LEFT JOIN後AND與WHERE的異同

1、AND 過濾之後再串連

2、WHERE 串連之後再過濾

下面以具體例子來說明：

(1)、建表及插入測試資料

 

[c-sharp]view plaincopyprint?

1. --建測試表  
2. create table FACT_TAB  
3. ( ID     INTEGER,  
4.   STATUS VARCHAR2(8)  
5. );  
6. create table DIM_STATUS  
7. ( STSTUS_CLASS VARCHAR2(8),  
8.   STATUS_CODE  VARCHAR2(8),  
9.   STATUS_DESC  VARCHAR2(8)  
10. );  
11. --插入測試資料  
12. insert into FACT_TAB (ID, STATUS)values (1, '1');  
13. insert into FACT_TAB (ID, STATUS)values (2, '1');  
14. insert into FACT_TAB (ID, STATUS)values (3, '2');  
15. insert into FACT_TAB (ID, STATUS)values (4, '1');  
16. insert into FACT_TAB (ID, STATUS)values (5, '2');  
17. insert into FACT_TAB (ID, STATUS)values (6, '3');  
18. insert into FACT_TAB (ID, STATUS)values (7, '1');  
19. insert into FACT_TAB (ID, STATUS)values (8, '2');  
20. insert into FACT_TAB (ID, STATUS)values (9, '3');  
21. insert into FACT_TAB (ID, STATUS)values (10, '3');  
22. insert into FACT_TAB (ID, STATUS)values (11, '2');  
23. insert into FACT_TAB (ID, STATUS)values (12, '1');  
24.   
25. insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('1', '1', '正常');  
26. insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('1', '2', '登出');  
27. insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '1', '正常');  
28. insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '2', '登出');  
29. insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '3', '遺失');  
30. insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '4', '未知');  
31.   
32. commit;  

(2)、on後面and 條件表示先過濾之後，再串連

以下兩種寫法，所得的結果相同：

 

[c-sharp]view plaincopyprint?

1. SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC  
2.   FROM FACT_TAB A  
3.   LEFT JOIN DIM_STATUS B  
4.     ON A.STATUS = B.STATUS_CODE  
5.    AND B.STSTUS_CLASS = '2'  
6.    AND B.STATUS_CODE = '1'  
7.  ORDER BY A.ID;  
8.   
9. SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC  
10.   FROM FACT_TAB A  
11.   LEFT JOIN (SELECT * FROM DIM_STATUS WHERE STATUS_CODE = '1') B  
12.     ON A.STATUS = B.STATUS_CODE  
13.    AND B.STSTUS_CLASS = '2'  
14. --AND B.STATUS_CODE = '1'  
15.  ORDER BY A.ID;  

ID	STATUS_CODE	STATUS_DESC
1	1	正常
2	1	正常
3	-1
4	1	正常
5	-1
6	-1
7	1	正常
8	-1
9	-1
10	-1
11	-1
12	1	正常

(3)、on後面where條件表示先關聯之後，再過濾

 

[c-sharp]view plaincopyprint?

1. SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC  
2.   FROM FACT_TAB A  
3.   LEFT JOIN DIM_STATUS B  
4.     ON A.STATUS = B.STATUS_CODE  
5.    AND B.STSTUS_CLASS = '2'  
6.  WHERE B.STATUS_CODE = '1'  
7.  ORDER BY A.ID;  

ID	STATUS_CODE	STATUS_DESC
1	1	正常
2	1	正常
4	1	正常
7	1	正常
12	1	正常

(4)、全值的情況

 

[c-sharp]view plaincopyprint?

1. --全值的情況  
2. SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC  
3.   FROM FACT_TAB A  
4.   LEFT JOIN DIM_STATUS B  
5.     ON A.STATUS = B.STATUS_CODE  
6.    AND B.STSTUS_CLASS = '2'  
7.  ORDER BY A.ID;  

ID	STATUS_CODE	STATUS_DESC
1	1	正常
2	1	正常
3	2	登出
4	1	正常
5	2	登出
6	3	遺失
7	1	正常
8	2	登出
9	3	遺失
10	3	遺失
11	2	登出
12	1	正常

 

(5)、說明：

用到此類串連的情況，多為事實表為主表，維表為次表的代碼關聯的串連；JOIN後AND與WHERE的區別，其實就是主次表過濾與聯結的先後問題，這一點能認識到，所有得出的結果，都不難理解了；另外，如果主表或次表的聯結關鍵字，有多個重複記錄，則聯結的結果會催生出多條重複記錄，這就要求聯結的關鍵字根據需要須是事實上的主鍵。

附：上面討論的是對輔表限制的情況，下面的是對事實表限制用on和where的情況

[c-sharp]view plaincopyprint?

1. 1、對事實表(主表)用on  
2. 將在結果集中過濾不滿足主表條件的輔表資訊，但因為是左串連主表的所有記錄都會顯示出來。  
3. SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC  
4.   FROM FACT_TAB A  
5.   LEFT JOIN DIM_STATUS B  
6.     ON A.STATUS = B.STATUS_CODE  
7.    AND B.STSTUS_CLASS = '2'  
8.       --AND B.STATUS_CODE = '1'  
9.    AND A.ID IN ('1', '2', '3')  
10.  ORDER BY A.ID;  
11. 2、對事實表(主表)用where  
12. 如果對主表的限制放在where裡，則不滿足主表條件的所有主表輔表資訊記錄將都不會體現在結果集中。  
13. SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC  
14.   FROM FACT_TAB A  
15.   LEFT JOIN DIM_STATUS B  
16.     ON A.STATUS = B.STATUS_CODE  
17.    AND B.STSTUS_CLASS = '2'  
18. --AND B.STATUS_CODE = '1'  
19.  WHERE A.ID IN ('1', '2', '3')  
20.  ORDER BY A.ID;  
21. 註：因為對主表用ON限制沒什麼意義，所以基本上用不到。