left join 加and 和 left join 加where

转自：http://blog.youkuaiyun.com/nsj820/article/details/6001718

1、AND 过滤之后再连接

2、WHERE 连接之后再过滤

下面以具体例子来说明：

(1)、建表及插入测试数据

 

[c-sharp] view plain copy print ?

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 plain copy print ?

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 plain copy print ?

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 plain copy print ?

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限制没什么意义，所以基本上用不到。