1、测试数据：
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
2、左连接测试
SQL> select
e.empno, e.ename, e.deptno, d.deptno, d.dname
3 from scott.emp e left join scott.dept d on e.deptno = d.deptno and e.deptno != 20 and d.deptno != 30;
EMPNO ENAME DEPTNO DEPTNO DNAME
---------- ------------------------------ ---------- ---------- ------------------------------------------
7934 MILLER 10 10 ACCOUNTING
7839 KING 10 10 ACCOUNTING
7782 CLARK 10 10 ACCOUNTING
7900 JAMES 30
7844 TURNER 30
7698 BLAKE 30
7654 MARTIN 30
7521 WARD 30
7499 ALLEN 30
7902 FORD 20
7876 ADAMS 20
7788 SCOTT 20
7566 JONES 20
7369 SMITH 20
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
filter("E"."DEPTNO"<>CASE WHEN ("D"."DEPTNO"(+) IS NOT NULL)
THEN 20 ELSE 20 END )
3 - filter("D"."DEPTNO"(+)<>30)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1082 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
-- 结论：left join 仅有on子句
-- 过滤条件对左表无效；
-- on子句过滤条件仅对右表生效；
-- 对于右表，过滤条件在连接之前生效（即先过滤，后连接）；
-- 对于左表，过滤条件在连接之后生效（即先连接，后过滤）。
SQL> select
e.empno, e.ename, e.deptno, d.deptno, d.dname
3 from scott.emp e left join scott.dept d on e.deptno = d.deptno where e.deptno != 20 and d.deptno != 30;
EMPNO ENAME DEPTNO DEPTNO DNAME
---------- ------------------------------ ---------- ---------- ------------------------------------------
7782 CLARK 10 10 ACCOUNTING
7934 MILLER 10 10 ACCOUNTING
7839 KING 10 10 ACCOUNTING
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 9 | 234 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 39 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | PK_DEPT | 3 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 9 | 117 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 9 | 117 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPTNO"<>30 AND "D"."DEPTNO"<>20)
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."DEPTNO"<>20 AND "E"."DEPTNO"<>30)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
916 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
-- 结论：where子句过滤
-- 过滤条件对于左右表都有效；
-- 对于右表，过滤条件在连接之前生效（即先过滤，后连接）；
-- 对于左表，过滤条件在连接之前生效（即先过滤，后连接）；
-- 对于左连接，右表过滤字段出现在连接条件中，左连解会变成内连接。
3、以下SQL自行测试，可验证以上结论：
select
e.empno, e.ename, e.deptno, d.deptno, d.dname
from scott.dept d join scott.emp e on e.deptno = d.deptno where e.deptno != 20 and d.deptno != 30;
select
e.empno, e.ename, e.deptno, d.deptno, d.dname
from scott.dept d join scott.emp e on e.deptno = d.deptno where d.deptno != 30;
select
e.empno, e.ename, e.deptno, d.deptno, d.dname
from scott.dept d join scott.emp e on e.deptno = d.deptno where e.deptno != 20;
select
e.empno, e.ename, e.deptno, d.deptno, d.dname
from scott.dept d left join scott.emp e on e.deptno = d.deptno where e.deptno != 20 and d.deptno != 30;
select
e.empno, e.ename, e.deptno, d.deptno, d.dname
from scott.dept d left join scott.emp e on e.deptno = d.deptno where d.deptno != 30;
select
e.empno, e.ename, e.deptno, d.deptno, d.dname
from scott.dept d left join scott.emp e on e.deptno = d.deptno where e.deptno != 20;