Oracle LEFT JOIN中ON条件与WHERE条件的区别

1、创建测试数据：

SQL> create table t1 as select * from scott.emp;

Table created.

SQL> create table t2 as select * from scott.dept;

Table created.

SQL> 

2、以下为使用where的查询结果与执行计划

SQL> select t1.*,t2.dname from t1 left join t2 on t1.deptno=t2.deptno where t1.job='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO DNAME

---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- --------------

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10 ACCOUNTING

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20 RESEARCH

      7369 SMITH      CLERK           7902 17-12月-80            800                    20 RESEARCH

      7900 JAMES      CLERK           7698 03-12月-81            950                    30 SALES

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 1823443478

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |   436 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |   436 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| T1   |     4 |   348 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |     4 |    88 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+))

   2 - filter("T1"."JOB"='CLERK')

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

          8  recursive calls

          0  db block gets

         15  consistent gets

          0  physical reads

          0  redo size

       1223  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          4  rows processed

SQL>

3、以下为使用on条件的查询结果与执行计划

SQL> select t1.*,t2.dname from t1 left join t2 on t1.deptno=t2.deptno and t1.job='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO DNAME

---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- --------------

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10 ACCOUNTING

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20 RESEARCH

      7369 SMITH      CLERK           7902 17-12月-80            800                    20 RESEARCH

      7900 JAMES      CLERK           7698 03-12月-81            950                    30 SALES

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO DNAME

---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- --------------

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

      7839 KING       PRESIDENT            17-11月-81           5000                    10

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 1823443478

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    19 |  2071 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |    19 |  2071 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1   |    14 |  1218 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |     4 |    88 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+) AND "T1"."JOB"=CASE  WHEN

              ("T2"."DEPTNO"(+) IS NOT NULL) THEN 'CLERK' ELSE 'CLERK' END )

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

       1590  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

SQL> 

oracle 对谓词and t1.job='CLERK'（on 后面的）,where t1.job='CLERK'的解析是不一样的。

使用where t1.job='CLERK':

1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+))

2 - filter("T1"."JOB"='CLERK')

Oracle 先根据"T1"."JOB"='CLERK'对T1表进行过滤，然后与T2表进行左外连接

Oracle解析的谓词and t1.job='CLERK'（on 后面的）为：

1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+) AND "T1"."JOB"=CASE  WHEN

           ("T2"."DEPTNO"(+) IS NOT NULL) THEN 'CLERK' ELSE 'CLERK' END )

Oracle 对t1,t2进行全表扫描，之后进行左外连接（也可能是在扫描过程中进行连接），而and t1.job='CLERK'对连接之后的记录总数没有影响，只是对不符合and t1.job='CLERK'的记录中的部门名称置为空

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23009281/viewspace-763320/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23009281/viewspace-763320/