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

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

JOIN中的ON条件与WHERE条件是一样的,而LEFT JOIN却不一样

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

 

表已创建。

 

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

 

表已创建。

 

SQL> delete t2 where deptno=30;

 

已删除 1行。

 

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

 

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

 

 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'的记录中的部门名称置为空

on中的条件限制

SQL> select * from tab1;

        ID      SIZE1
---------- ----------
         1         10
         2         20
         3         30

SQL> select * from  tab2;

     SIZE1 NAME
---------- --------------------
        10 AAA
        20 BBB
        20 CCC

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1) ;

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         1         10         10 AAA
         2         20         20 BBB
         2         20         20 CCC
         3         30

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab1.id=2);

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         1         10
         2         20         20 BBB
         2         20         20 CCC
         3         30

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='AAA');

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         1         10         10 AAA
         3         30
         2         20

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='BBB');

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         2         20         20 BBB
         3         30
         1         10

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='CCC');

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         2         20         20 CCC
         3         30
         1         10

SQL> update tab2 set name='DDD' where size1=20;

已更新2行。

提交完成。
SQL> commit
  2  ;

提交完成。

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='DDD');

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         2         20         20 DDD
         2         20         20 DDD
         3         30
         1         10

SQL> select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='xxx');

        ID      SIZE1      SIZE1 NAME
---------- ---------- ---------- --------------------
         3         30
         2         20
         1         10

当on中对左表的非连接字段限制时 与 对右表的非连接字段限制时 是两种不同的情况，请注意。

当on中对右表的非连接字段限制时(on (tab1.size1= tab2.size1 and tab2.name='AAA')) 相当于右表根据非连接字段限制获取结果，然后左表再与它关联。
select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='AAA');
相当于
select tab1.*,t.* from tab1 left join (select * from tab2 where tab2.name='AAA') t on (tab1.size1= t.size1);