Join in Oracle

1. Overview

1.1. All join connections can be added with a condition similar to where a. ID = '000000' to achieve the same effect.

1.2 except for cross join, the on keyword must be added to all other join joins, And the where condition can be added later.

1.3 Although the where condition can be added, they only search for the where condition in the result set of the standard connection. For example, the result of the left Outer Join does not have three classes of class, so if you add where class. id = 'c003 'is included in the table but not in the left join result set. Therefore, no records are recorded after the query.

2. instance, standard join connection (without the where condition)

2.1. the table is as follows:

Student table

Class table, corresponding to the classid in the student table

2.2. Auto join: Join, inner join

1 -- auto-join: returns matching items for only two table connection columns.
2 -- the following three query results are the same.
3 select * from student s inner join class C on S. classid = C. ID;
4 select * from student s join class C on S. classid = C. ID;
5 select * from student s, Class C where S. classid = C. ID;

Self-connection result:

2.3. flute product: cross join

1 -- Cartesian Product join: The result set that reaches M * n without any conditions.
2 -- the following two types of query results are the same.
3 select * from student s cross join class C;
4 select * from student, class;

Cartesian results:

Note: If the where S. classid = C. ID condition is added to cross join, the result is the same as that of the self join:

1 -- add a condition to produce the same result as that of the self-Join Operation.
2 select * from student s cross join class C where S. classid = C. ID;

Cross join result of the Self-join result set

2.3 left Outer Join: left join 

1 -- left join: lists all the tables on the left and the tables on the right that meet the conditions. null values are used instead of non-conforming values.
2 -- in the (+) calculation, which carry (+), which must meet the conditions, and the other all. That is, left or right connections, and right or left connections.
3 -- the following result sets are the same.
4 select * from student s left join class C on S. classid = C. ID;
5 select * from student s, Class C where S. classid = C. ID (+ );

Left join result:

2.4 outer right join: Right join

1 -- right outer join: like the left join, it lists all the tables on the right and the tables on the left that meet the conditions but do not.
2 -- replace null values.
3 -- (+) is the same, and its position is opposite to the connection.
4 select * from student s right join class C on S. classid = C. ID;
5 select * from student s, Class C where S. classid (+) = C. ID;

Right join result

2.5. Full join: Full join

1 -- full join: generate a result set of m + n. list all the results of the two tables. If the two tables do not meet the conditions, replace them with null values.
2 select * from student s full join class C on S. classid = C. ID;

Full join result set