Outer Join, left Outer Join

Outer Join, left Outer Join

Definition: Used to query records that meet the connection conditions and do not meet the connection conditions.

Query emp table records

SELECT * FROM emp;
Empno ename job mgr hiredate sal comm deptno
1 7369 smith clerk 7902 800.00 20
2 7499 allen salesman 7698 1600.00 300.00 30
3 7521 ward salesman 7698 1250.00 500.00 30
4 7566 jones manager 7839 2975.00 20
5 7654 martin salesman 7698 1250.00 1400.00 30
6 7698 blake manager 7839 2850.00 30
7 7782 clark manager 7839 2450.00 10
8 7788 scott analyst 7566 0087/4/19 3000.00 20
9 7839 king president 1981/11/17 5000.00 10
10 7844 turner salesman 7698 1500.00 0.00 30
11 7876 adams clerk 7788 0087/5/23 1100.00 20
12 7900 james clerk 7698 950.00
13 7902 ford analyst 7566 3000.00 20
14 7934 miller clerk 7782 1982/1/23 1300.00 10

Query dept table records

SELECT * FROM dept;
Deptno dname loc
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON

Connection query statement

SELECT e. ename, d. dname FROM emp e left outer join dept d ON (e. deptno = d. deptno); -- LEFT OUTER JOIN
SELECT e. ename, d. dname FROM emp e right outer join dept d ON (e. deptno = d. deptno); -- RIGHT OUTER JOIN
SELECT e. ename, d. dname FROM emp e full outer join dept d ON (e. deptno = d. deptno); -- all external connections

Comparison of query results of three connections

LEFT OUTER RIGHT OUTER FULL OUTER
1 smith research 1 clark accounting 1 SMITH RESEARCH
2 allen sales 2 king accounting 2 ALLEN SALES
3 ward sales 3 miller accounting 3 WARD SALES
4 jones research 4 jones research 4 JONES RESEARCH
5 martin sales 5 ford research 5 MARTIN SALES
6 blake sales 6 adams research 6 BLAKE SALES
7 clark accounting 7 smith research 7 CLARK ACCOUNTING
8 scott research 8 scott research 8 SCOTT RESEARCH
9 king accounting 9 ward sales 9 KING ACCOUNTING
10 turner sales 10 turner sales 10 TURNER SALES
11 adams research 11 allen sales 11 ADAMS RESEARCH
12 james sales 12 james sales 12 JAMES SALES
13 ford research 13 blake sales 13 FORD RESEARCH
14 miller accounting 14 martin sales 14 MILLER ACCOUNTING
15 OPERATIONS 15 OPERATIONS

Query results from the preceding table:
Knowledge preparation:
First, you should understand what a driver table is. To put it bluntly, it is the position of the table (The position of the table on the LEFT or right of the "left outer join" keyword ).
Left outer join: the driving table is the table on the LEFT of the "left outer join" keyword.
OUTER right JOIN: the driving table is the table on the right of the "left outer join" keyword.
Full outer join: the driving table is the table on both sides of the "left outer join" keyword.
SQL statement execution sequence
Left outer join: first query the table on the LEFT of the "left outer join" keyword.
OUTER right JOIN: first query the table on the right of the "left outer join" keyword.
Full outer join: first query the table on the LEFT of the "left outer join" keyword. (if the corresponding field on the LEFT of the query is empty, query the corresponding field in the right table. If not empty, ).

Differentiate left Outer Join, right outer join, and full outer join
Left Outer Join:
First, query the corresponding fields in the LEFT table of the "left outer join" keyword. If it is null, it does not match the right table.
Outer right connection:
First, query the corresponding fields of the table on the right of the "left outer join" keyword. If it is null, it does not match the LEFT table.
All external connections
First, query the corresponding fields of the table on the LEFT of the "left outer join" keyword. If it is empty, query the corresponding fields of the table on the right of the "left outer join" keyword. If it is not empty on one side, the matching is performed.