External connection
When you use an outer join for multiple-table queries, the returned query result set contains only the rows for the query criteria and join criteria. In addition to eliminating any unmatched rows in the other table, the outer join extends the result set of the inner join, and returns some or all of the unmatched rows, in addition to returning all matching rows, depending on the type of external connection.
For external connections, Oracle can use (+) to represent, or use the left and full outer join keywords.
Outer joins can be divided into the following three categories:
Left OUTER join: (outer join or Ieft join)
Right outer join: (outer join or Starboard join)
Full outer joins: (outer join or complete join)
Using an outer join, lists the rows that match the join criteria, and lists all data rows matching the search criteria in the left table (left outer join), right table (right table connection), or two tables (full outer joins).
Examples are as follows:
We operate with Oracle's own EMP and Salgrade tables.
The Salgrade table represents the salary level, with the highest and lowest values for each grade. EMP table for employee's basic information
Sql> select * from Salgrade;
GRADE Losal Hisal
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Sql> Select Empno,ename,sal,grade
2 from EMP e inner JOIN Salgrade s
3 on e.sal between S.losal and S.hisal;
EMPNO ename SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 800 1
7876 ADAMS 1100 1
7900 JAMES 950 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
14 rows have been selected.
sql> INSERT INTO EMP values (7937, ' Candy ', null,null,null,500,null,null);//The SAL salary in the inserted data is 500 not within the employee's salary range
1 rows have been created.
sql> INSERT into Salgrade values (6,10000,20000)--the level of insertion and the salary are no longer within range
1 rows have been created.
Sql> Select E.empno,e.ename,e.sal,d.grade
2 from EMP e ieft outer join Salgrade D--use left Join
3 on e.sal between D.losal and D.hisal;
EMPNO ename SAL GRADE
---------- ---------- ---------- ----------
7839 KING 5000 5
7902 FORD 3000 4
7788 SCOTT 3000 4
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7499 ALLEN 1600 3
7844 TURNER 1500 3
7934 MILLER 1300 2
7521 WARD 1250 2
7654 MARTIN 1250 2
7876 ADAMS 1100 1
7900 JAMES 950 1
7369 SMITH 800 1
7937 Candy 500
15 rows have been selected.
As you can see from the data above, we have also investigated data that is not in range except for all eligible data.
7937 Candy 500
Right outer connection
A right outer join is a result in which all rows in the right-hand table of the join that satisfy the search criteria are displayed in addition to the rows that meet the criteria.
SELECT DISTINCT E.deptno,d.deptno from EMP e right outer joins dept D on E.deptno=d.deptno;
If you implement right outer joins using Howe, the above statement is equivalent to the following statement:
SELECT DISTINCT E.deptno,d.deptno from emp e,dept D where E.deptno (+) =d.deptno;
The results are as follows:
Sql> SELECT DISTINCT E.deptno,d.deptno from emp e,dept D where E.deptno (+) =d.deptno;
DEPTNO DEPTNO
---------- ----------
30 30
20 20
10 10
40
Sql> SELECT DISTINCT E.deptno,d.deptno from EMP e right outer joins dept D on E.deptno=d.deptno;
DEPTNO DEPTNO
---------- ----------
30 30
20 20
10 10
40
Sql> Select E.empno,e.ename,e.sal,d.grade
2 from EMP e right outer join Salgrade D
3 on e.sal between D.losal and D.hisal;
EMPNO ename SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 800 1
7876 ADAMS 1100 1
7900 JAMES 950 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
6
There is no data with a salary level of 6, but using the right connection, the mismatched data in the Salgrade table is displayed
15 rows have been selected.
Sql> SELECT DISTINCT E.deptno,d.deptno
2 from EMP e right outer JOIN Dept D
3 on E.deptno=d.deptno;
DEPTNO DEPTNO
---------- ----------
30 30
20 20
10 10
40
Sql> SELECT DISTINCT E.deptno,d.deptno
2 from EMP e,dept D
3 where E.deptno (+) =d.deptno; Same as the above results
DEPTNO DEPTNO
---------- ----------
30 30
20 20
10 10
40
Left and right connections for SQL
