Read Catalogue
- 1. Preparatory work
- 2. Left outer connection (OUTER Join/left join)
- 3. Right outer connection (OUTER join/right join)
- 4. Full external connection (OUTER Join/full join)
1. Preparatory work
Oracle outer joins (OUTER join) include the following:
- 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 TABLE t_a (id number,name VARCHAR2 (10)); CREATE TABLE t_b (id number,name VARCHAR2), insert into t_a values (1, ' A '); insert into t_a values (2, ' B '); I Nsert into t_a values (3, ' C '), insert into t_a values (4, ' D '), insert into t_a values (5, ' E '), insert into t_b values (1, ' AA '); SERT into T_b values (1, ' BB '), insert into t_b values (2, ' CC '), insert into t_b values (1, ' DD ');
Back to directory 2, 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 T_a a LEFT join T_b b on a.id = b.id; or select * from t_a A left outer join t_b B on a.id = 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 T_a a,t_b B where a.id=b.id (+);
Back to directory 3, right outer connection (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 T_a a right joins T_b b on a.id = b.id; or select * from t_a A right outer join t_b B on a.id = 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 T_a a,t_b B where a.id (+) =b.id;
Back to catalog 4, full 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 T_a a full join T_b b on a.id = b.id; or select * from t_a A full outer join t_b B on a.id = b.id;
Add
SELECT * from t_a a,t_b b where a.id = B.id;select * from t_a A joins T_b B on a.id = b.id;
SELECT * from T_a a where a.id in (select b.ID from T_b b), select * from T_a A where exists (select 1 from t_b b where a.i d = b.id);
Oracle LEFT JOIN, right connection, full outer join, and (+) sign usage