Oracle LEFT JOIN中ON條件與WHERE條件的區別__Oracle

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         30SQL> select * from  tab2;     SIZE1 NAME---------- --------------------        10 AAA        20 BBB        20 CCCSQL> 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         30SQL> 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         30SQL> 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         20SQL> 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         10SQL> 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         10SQL> 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         10SQL> 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);