EXISTS与IN, NOT EXISTS与NOT IN比较

EXISTS
返回TRUE或者 FALSE,取决于子查询返回的结果集是否至少包含一行。不同于其它谓词（如IN）和逻辑表达式，EXISTS不能返回UNKNOWN。子查询或者返回行集，或者不返回。假如子查询对某一特殊行的筛选导致结果返回 UNKNOWN，则该行不会被返回到子查询的结果集中。 因为在筛选过程中，UNKNOWN是被作为FALSE来对待的。 换句话说，在存在筛选条件的子查询中，EXISTS返回TRUE的前提条件是至少有一行针对筛选值为TRUE。
先看一个exists实例。下边查询返回表Customers中所有国家为Spain而且 在表Orders里边下过订单 的客户。
SELECT CustomerID, CompanyName
FROM dbo.Customers AS C
WHERE Country = N'Spain'
AND EXISTS
(SELECT * FROM Orders AS O
WHERE O.CustomerID = C.CustomerID);
/*
CustomerID Companyname
---------- ----------------------------------------
BOLID Bólido Comidas preparadas
GALED Galería del gastrónomo
GODOS Godos Cocina Típica
ROMEY Romero y tomillo

(4 行受影响)
*/
Tip： *用在这里的子查询非常安全，尽管说实际很少这么用。查询优化器会忽视子查询中SELECET返回列表，因为EXISTS只关注

行集的存在性，而不关注其他属性

由执行计划，先扫描Customers表，通过Spain条件进行过滤。对每一个符合条件的customer，执行计划通过对索引列Orders.CustomerID进行查找来检测是否 Orders表中包含该客户的CustomerID。在这里，CustomerID列上的索引对查询非常有帮助，因为通过它可以直接访问到Orders表中含有指定CustomerID的行（而无需进行全表扫描）

EXISTS VS. IN
很多人都想知道，是否一个含有EXISTS谓词的查询比一个含有IN谓词的逻辑等值查询效率要更高。比方说，上述查询可以通过用IN谓词和一个自包含的子查询来实现：
SELECT CustomerID, CompanyName
FROM dbo.Customers AS C
WHERE Country = N'Spain'
AND CustomerID IN(SELECT CustomerID FROM dbo.Orders);
在SQL Server2000以前的版本中，由执行计划可以看出两者是有差别的，而且很显然，EXISTS效率要高，这事由EXISTS固有的short-circuiting属性所决定的。然后，在SQL Server2000以及2005中，当两种查询方式确实逻辑等价、限定相同时，查询优化器通常会对它们生成相同的执行计划。
假如你总是考虑三价逻辑(TRUE,FALSE,UNKNOWN)的含意，你也许会认识到IN跟 EXISTS还是有区别的，区别于EXISTS，当输入列中含空值时IN能差生一个UNKNOWN的逻辑结果。比如说，a IN(b,c,NULL)结果为UNKNOWN。因为在筛选过程中，UNKOWN被当作FALSE来对待，因此查询时不论是包含IN谓词还是EXISTS谓词，结果都相同。查询优化器已经认识到这点，因此产生的执行计划相同。

NOT EXISTS VS. NOT IN
当谓词NOT IN的输入列中假如有空值，这种逻辑不同性跟上述IN与EXISTS一样，也存在于NOT EXISTS与NOT IN之间。
举个例子，你现在想返回所有来自 Spain的，而且又没下订单的客户。下边方法是通过NOT EXISTS谓词来实现：
SELECT CustomerID, CompanyName
FROM dbo.Customers AS C
WHERE Country = N'Spain'
AND NOT EXISTS
(SELECT * FROM Orders AS O
WHERE O.CustomerID = C.CustomerID);
/*
CustomerID CompanyName
---------- ----------------------------------------
FISSA FISSA Fabrica Inter. Salchichas S.A.

(1 行受影响)
*/
即使在Orders表中CustomerID存在空值，对我们的查询结果也没有影响。你可以获取到所有来自Spain的客户名单，而且SQL Server不能从Orders表中找到任何关于该客户的订单信息。

执行计划扫描 Customers表并且通过客户来自Spain来过滤，对每一符合条件的Customer，执行计划对索引列CustomerID执行索引一次查找。这里Top操作出现了，这是因为它只需要去判断是否这里至少有一个对应客户的订单。这就是EXISTS在过程中所体现出的short-circuiting 性能。在这里，当Orders.CustomerID列密度很高时（也就是说有很多重复项）效率会更高。对每个客户，查找只执行一次，而不关心客户在Orders表中的订单个数，页文件中只有一行（页级别为索引的最底层级别）会被扫描是否吻合，而不是针对所有符合条件的行。
下边的查询通过使用NOT IN谓词也返回相同输出结果。它看起来意义相同，但是之后我们会发现其实不然。
SELECT CustomerID, CompanyName
FROM dbo.Customers AS C
WHERE Country = N'Spain'
AND CustomerID NOT IN(SELECT CustomerID FROM dbo.Orders);
检查执行计划，发现它和NOT EXISTS查询所生成的执行计划不一样。

计划中起始时候包含一些额外的操作，相比之前（NOT EXISTS）的操作，它需要查找所有空CustomerID列。为什么它们的执行计划会不同？为何SQL Server会如此特殊关注Orders.CustomerID列中的空值列？
这两执行计划的差异性并未影响查询结果，原因是Orders表中CustomerID列并不存在空值。但是，由于CustmerID允许空，因此查询优化器必须要考虑到这种情况。现在我们在表Orders中假如CustomerID为空值（NULL）的情况。
INSERT INTO dbo.Orders DEFAULT VALUES;
重新运行上边两查询，发现NOT EXISTS返回相同结果，而NOT IN所在的查询返回空。
事实上，只要Orders.CustomerID列存在控制，NOT IN查询将永远返回空集。原因是因为谓词val IN(val1,val2,...,NULL)只会返回TRUE跟UNKNOWN。 因此, val NOT IN(val1,val2,...,NULL)也只会返回NOT TRUE跟NOT UNKNOWN,而两者没有一个为TRUE。
举个例子，假如在这个查询中customer列集为（a,b,NULL）,Customer a出在先列集中，因此谓词a IN(a,b,NULL)返回 TRUE，a NOT IN(a,b,NULL)返回 NOT TRUE,也就是FALSE，由此customer a在查询中也不会被返回。Customer C，从另一方面讲,并未出现在list(a,b,NULL)中，但由于有NULL值，逻辑结果c IN(a,b,NULL)为UNKNOWN。c NOT IN(a,b,NULL)为NOT UNKNOWN，即为UNKNOWN，由此customer c不会被外部查询返回，也没有出现在customer list中。
无论某一customer是否出现在customer列集中，只要集合中存在空，customer就不会被查询返回。因此需要认识一点，当空值潜在的存在于列集中时，NOT EXISTS 跟NOT IN逻辑不等价 。这也就能解释上述两执行计划的差异以及潜在的结果差异性。
假如想NOT IN查询逻辑等价于NOT EXISTS查询，则需显示声明列非空，或者加筛选条件去除空值 :
SELECT CustomerID, CompanyName
FROM dbo.Customers AS C
WHERE Country = N'Spain'
AND CustomerID NOT IN(SELECT CustomerID FROM dbo.Orders
WHERE CustomerID IS NOT NULL);
/*
CustomerID CompanyName
---------- ----------------------------------------
FISSA FISSA Fabrica Inter. Salchichas S.A.

(1 行受影响)
*/