SQL Server ON条件和WHERE条件
SQL Server中ON条件和WHER条件的区别:
一、ON条件是在生成临时表时使用的条件,它不管ON中的条件是否为真,都会返回左边表中的记录
以 LEFT JOIN 为例,新建表Item和UOM,并插入测试数据:
1 CREATE TABLE [dbo].[Item] 2 ( 3 [ID] [INT] NULL, 4 [Item] [INT] NULL, 5 [GroupNo] [INT] NULL, 6 [UOM] [INT] NULL, 7 [ParentUOM] [INT] NULL 8 ) 9 10 INSERT INTO Item 11 SELECT 1,1,10,1,0 12 UNION 13 SELECT 2,1,10,2,1 14 UNION 15 SELECT 3,1,10,4,2 16 UNION 17 SELECT 4,2,10,1,0 18 UNION 19 SELECT 5,2,10,2,1 20 UNION 21 SELECT 6,2,10,4,2 22 UNION 23 SELECT 7,1,20,1,0 24 UNION 25 SELECT 8,1,20,2,1 26 UNION 27 SELECT 9,1,20,4,2 28 UNION 29 SELECT 10,1,30,1,0 30 UNION 31 SELECT 11,1,30,2,1 32 UNION 33 SELECT 12,1,30,4,2 34 35 CREATE TABLE [UOM] 36 ( 37 [ID] [INT] NULL, 38 [ParentUOM] [INT] NULL 39 ) 40 41 INSERT INTO UOM 42 SELECT 1,0 43 UNION 44 SELECT 2,1 45 UNION 46 SELECT 3,1 47 UNION 48 SELECT 4,2 49 UNION 50 SELECT 5,2 51 UNION 52 SELECT 6,3
1、当 Item LEFT JOIN UOM ON xxx 时,结果返回所有Item记录,ON xxx是表关联时的条件,根据条件Item表记录与UOM表记录关联,最终只有满足关联条件的UOM表记录会保留
1 SELECT A.*,B.* 2 FROM Item A 3 LEFT JOIN UOM B ON A.ParentUOM = B.ID
查询结果:
2、ON条件不能过滤Item表记录,即使ON条件中是Item.xxx。Item.xxx只是限定了Item表中哪些记录参与到关联过程,以便过滤UOM表记录
1 SELECT A.*,B.* 2 FROM Item A 3 LEFT JOIN UOM B ON A.ParentUOM = B.ID AND A.GroupNo = 10
与1中产寻脚本不同是ON条件中增加 A.GroupNo = 10
查询结果:
比较两次查询结果:2中的查询结果,结果7-12因为GroupNo != 10,所以结果中UOM记录全部为NULL
3、总结
LEFT JOIN 如果是想要在最终结果集过滤左表中的记录,不能通过ON条件,需要使用WHERE条件
二、WHERE条件是在临时表生成后,在对临时表进行过滤时使用的条件
三、如果左表很大,而且查询的结果相同时,使用ON条件可以减小中间临时表的大小,使用ON条件效率比WHERE条件高