Oracle outer JOIN (OUTER join)
- Left OUTER join (the table on the left is unrestricted)
- Right outer join (the table on the right is unrestricted)
- All-out connection (both the left and right tables are unrestricted)
Corresponds to Sql:left/right/full OUTER JOIN. Usually omit the outer keyword, written as: Left/right/full JOIN.
Both the left and right connections use a table as the base table, and the contents of the table are all displayed, followed by a and B tables that match the contents. If the data in table A is not recorded in table B. Then the columns in the associated result set row are displayed as null values (NULL).
For outer joins, you can also use "(+)" to represent them. Some things to note about using (+):
- The (+) operator can only appear in the WHERE clause and cannot be used in conjunction with the outer join syntax.
- When an outer join is performed using the (+) operator, if there are multiple conditions in the WHERE clause, the (+) operator must be included in all conditions.
- The (+) operator applies only to columns, not to expressions.
- The (+) operator cannot be used with the OR and in operators.
- The (+) operator can only be used to implement left outer and right outer joins, not for full outer joins.
Create two tables, insert data for Learning tests:
CREATE TABLEt_a (ID Number, nameVARCHAR2(Ten));CREATE TABLET_b (ID Number, nameVARCHAR2(Ten));INSERT intoT_aVALUES(1,'A');INSERT intoT_aVALUES(2,'B');INSERT intoT_aVALUES(3,'C');INSERT intoT_aVALUES(4,'D');INSERT intoT_aVALUES(5,'E');INSERT intoT_bVALUES(1,'AA');INSERT intoT_bVALUES(1,'BB');INSERT intoT_bVALUES(2,'CC');INSERT intoT_bVALUES(1,'DD');
Left outer connection (OUTER Join/left join)
The left join is based on the left table record, and the example t_a can be seen as an open table, T_b can be seen as the right table, and its result set is all the data in the T_a table, plus the data after the T_a table and T_b table match. In other words, the records for the left table (T_A) will all be represented, and the right table (T_b) will only display records that match the search criteria. The T_b table has insufficient records where it is null.
Select * from Left Join on = b.id, or Select * from Left outer Join on = b.id;
With (+) to achieve, the + number can be understood as follows: + to supplement, that is, which table has a plus, this table is a matching table. If the plus sign is written in the right table, the left table is all displayed, so it is left connected.
Select * from where a.id=b.id (+);
Right outer join (OUTER join/right join)
The result of the left join is exactly the opposite, based on the right table (T_b). Its result set is all records of the T_b table, plus the data after matching t_a and T_b. The T_a table has insufficient records where it is null.
Select * from Right Join on = b.id, or Select * from Right outer Join on = b.id;
With (+) to achieve, the + number can be understood as follows: + to supplement, that is, which table has a plus, this table is a matching table. If the plus sign is written in the left table, the right table is all displayed, so it is the right connection.
Select * from where a.id (+)=b.id;
Full outer join (OUTER join/full JOIN)
Neither the left table nor the right table is restricted, and all records show that both tables are null. The full outer join does not support (+) the notation.
Select * from Full Join on = b.id, or Select * from Full outer Join on = b.id;
Add
Select * from where = b.id; Select * from Join on = b.id;
Select * from where inch (Select from t_b B); Select * from where exists (Select1fromwhere= b.id);
Oracle LEFT JOIN, right connection, full outer join, and (+) sign usage