Oracle LEFT JOIN, right connection, full outer join, and (+) sign usage

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 (+):

1. The (+) operator can only appear in the WHERE clause and cannot be used in conjunction with the outer join syntax.
2. 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.
3. The (+) operator applies only to columns, not to expressions.
4. The (+) operator cannot be used with the OR and in operators.
5. 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