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:
CREATETABLET_a (IDNumber, nameVARCHAR2 (10));CREATETABLET_b (IDNumber, nameVARCHAR2 (10));INSERTInto T_aVALUES (1,‘A‘);INSERTInto T_aVALUES (2,‘B‘);INSERTInto T_aVALUES (3,‘C‘);INSERTInto T_aVALUES (4,‘D‘);INSERTInto T_aVALUES (5,‘E‘);INSERTInto T_bVALUES (1,‘Aa‘);INSERTinto t_b values (1, ' bb ); insert into t_b values ( 2, ' into t_b values (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.
= b.id; or = 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.
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.
= b.id; or = 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.
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.
= b.id;
Add
= b.id; = b.id;
In (from t_b B); exists (= b.id);
Oracle LEFT JOIN, right connection, full outer join, and (+) sign usage (RPM)