EXISTS、IN与JOIN性能分析

在论坛上看到这样一篇帖子，转来备注EXISTS、IN与JOIN性能分析

EXISTS、IN与JOIN，都可以用来实现形如“查询A表中在（或不在）B表中的记录”的查询逻辑。

在论坛上看到很多人对此有所误解（如 关于in的疑惑 、 用 外连接 和 Is Null 代替 not in 两帖），特做一简单测试。

测试结果：

测试代码较长，附于本帖最后。
图表中百分数表示同一组3个查询的执行时间比例。红色表示3个语句中最慢，绿色表示3个语句中最快的，并列则没加颜色。
其中索引只测试了聚集索引，当表中字段较多且查询字段是非聚集索引时，选择执行计划的条件比较复杂，没有测试。并且当表中数量变化后，执行计划可能也有差异。图表反映了3种查询方式的解析机制的不同，基本结论是类似的，但具体情况还要视执行计划而定。

分析结论：
通常情况下，3种查询方式的执行时间：
EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时，NOT IN的方式最慢：
NOT EXISTS <= LEFT JOIN <= NOT IN

综上：
IN的好处是逻辑直观简单（通常是独立子查询）；缺点是只能判断单字段，并且当NOT IN时效率较低，而且NULL会导致不想要的结果。
EXISTS的好处是效率高，可以判断单字段和组合字段，并不受NULL的影响；缺点是逻辑稍微复杂（通常是相关子查询）。
JOIN用在这种场合，往往是吃力不讨好。JOIN的用途是联接两个表，而不是判断一个表的记录是否在另一个表。

编程建议：
（以下三条建议中EXISTS和IN同时代指肯定式逻辑和加NOT后的否定式逻辑）
如果查询条件是单字段主键（有索引且不允许NULL），则EXISTS和IN的性能基本一样，IN的查询通常写法简单、逻辑直观。
如果查询条件涉及多个字段，则最好选择EXISTS，千万不要用字段拼接再IN的方式（索引会失效）。
如果条件不确定，选用EXISTS是最保险的办法，性能最好，不受三值逻辑影响（EXISTS只会返回True/False不会返回Unknown），但代码逻辑稍稍复杂，思路要理清楚，而且相关字段最好采用“表（别）名.字段名”的形式。

附一：IN/NOT IN容易出现的两个问题
参看如下代码：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT      EmployeeID = n,      EmployeeName =  'E'  +  RIGHT ( '000'  +  CAST (n  AS  varchar (10)),3) INTO  #Employees FROM  dbo.Nums  WHERE  n <= 10;   SELECT  EmployeeID INTO  #Badboys FROM  ( SELECT  TOP (4) EmployeeID = n  FROM  dbo.Nums  WHERE  n <= 10  ORDER  BY  NEWID()) tmp UNION SELECT  NULL ;   --问题1： SELECT  *  FROM  #Employees  WHERE  EmployeeID  IN  ( SELECT  EmployeeID  FROM  #Badboys); SELECT  *  FROM  #Employees  WHERE  EmployeeID  NOT  IN  ( SELECT  EmployeeID  FROM  #Badboys); --问题2： SELECT  *  FROM  #Employees  WHERE  EmployeeName  IN  ( SELECT  EmployeeName  FROM  #Badboys); SELECT  *  FROM  #Employees  WHERE  EmployeeName  NOT  IN  ( SELECT  EmployeeName  FROM  #Badboys);

其中：
问题1是三值逻辑的问题，说明了在NOT IN遇到NULL时要特别小心（参看 关于 not in的疑问 一帖）。这也是为什么建议“如果可能，尽量让所有字段都声明为NOT NULL”的原因之一。
问题2是SQL Server子查询处理时命名空间解析的漏洞，说明了在多表查询中采用“表（别）名.字段名”的形式的好处，否则就要对字段名的拼写非常小心。

附二：EXISTS、IN与JOIN性能分析测试代码：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103

--表中字段不允许NULL   --TestCase1: 无重复数据，无索引 CREATE  TABLE  T1(n  int  NOT  NULL ); CREATE  TABLE  T2(n  int  NOT  NULL ); INSERT  INTO  T1 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100; INSERT  INTO  T2 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100  AND  n % 2 = 0;   --TestCase2: 无重复数据，有索引 CREATE  UNIQUE  CLUSTERED  INDEX  IX_T1  ON  T1(n); CREATE  UNIQUE  CLUSTERED  INDEX  IX_T2  ON  T2(n);   --TestCase3: 有重复数据，无索引 DROP  TABLE  T1; DROP  TABLE  T2; CREATE  TABLE  T1(n  int  NOT  NULL ); CREATE  TABLE  T2(n  int  NOT  NULL ); INSERT  INTO  T1 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100; INSERT  INTO  T2 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100  AND  n % 2 = 0 UNION  ALL SELECT  n  FROM  dbo.Nums  WHERE  n <= 100  AND  n % 3 = 0;   --TestCase4: 有重复数据，有索引 CREATE  CLUSTERED  INDEX  IX_T1  ON  T1(n); CREATE  CLUSTERED  INDEX  IX_T2  ON  T2(n);     --表中字段允许NULL   --TestCase5: 无重复数据，无索引 DROP  TABLE  T1; DROP  TABLE  T2; CREATE  TABLE  T1(n  int  NULL ); CREATE  TABLE  T2(n  int  NULL ); INSERT  INTO  T1 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100; INSERT  INTO  T2 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100  AND  n % 2 = 0;   --TestCase6: 无重复数据，有索引 CREATE  UNIQUE  CLUSTERED  INDEX  IX_T1  ON  T1(n); CREATE  UNIQUE  CLUSTERED  INDEX  IX_T2  ON  T2(n);   --TestCase7: 有重复数据，无索引 DROP  TABLE  T1; DROP  TABLE  T2; CREATE  TABLE  T1(n  int  NULL ); CREATE  TABLE  T2(n  int  NULL ); INSERT  INTO  T1 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100; INSERT  INTO  T2 SELECT  n  FROM  dbo.Nums  WHERE  n <= 100  AND  n % 2 = 0 UNION  ALL SELECT  n  FROM  dbo.Nums  WHERE  n <= 100  AND  n % 3 = 0;   --TestCase8: 有重复数据，有索引 CREATE  CLUSTERED  INDEX  IX_T1  ON  T1(n); CREATE  CLUSTERED  INDEX  IX_T2  ON  T2(n);     --Foreach TestCase above，分别执行以下两组语句并观察执行计划：   --肯定式逻辑   SELECT  T1.* FROM  T1 WHERE  EXISTS ( SELECT  *  FROM  T2  WHERE  T2.n = T1.n);   SELECT  T1.* FROM  T1 WHERE  T1.n  IN  ( SELECT  T2.n  FROM  T2);   SELECT  DISTINCT  T1.*   --不加DISTINCT可能会引起重复 FROM  T1 INNER  JOIN  T2 ON  T1.n = T2.n;   --否定式逻辑   SELECT  T1.* FROM  T1 WHERE  NOT  EXISTS ( SELECT  *  FROM  T2  WHERE  T2.n = T1.n);   SELECT  T1.* FROM  T1 WHERE  T1.n  NOT  IN  ( SELECT  T2.n  FROM  T2);   SELECT  T1.* FROM  T1 LEFT  JOIN  T2 ON  T1.n = T2.n WHERE  T2.n  IS  NULL ;   --End Foreach     --清场 DROP  TABLE  T1; DROP  TABLE  T2;