NOT IN vs NOT EXISTS

Question

这些查询中哪一个更快？

不存在：

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Northwind..[Order Details] od 
    WHERE p.ProductId = od.ProductId)

或不在：

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

查询执行计划表明它们做的是相同的事情。如果是这样的话，推荐使用哪种形式？

这是基于NorthWind数据库的。

编辑

我找到了这篇有用的文章：http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

我想我会坚持使用NOT EXISTS。

Answer 1

我总是默认使用NOT EXISTS。

现在的执行计划可能是相同的，但是如果在将来修改任一列以允许NULLs，NOT IN版本将需要做更多的工作(即使数据中实际上没有NULLs )，并且如果NULLs存在，NOT IN的语义也不太可能是您想要的。

当Products.ProductID或[Order Details].ProductID都不允许NULL%s时，NOT IN将被视为与以下查询相同。

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId) 

确切的计划可能会有所不同，但对于我的示例数据，我得到了以下内容。

​

​

一个相当常见的误解似乎是，与连接相比，相关子查询总是“糟糕”的。当它们强制嵌套循环计划(子查询逐行求值)时，它们当然可以，但该计划包括一个反半连接逻辑运算符。反半连接不限于嵌套循环，但也可以使用散列或合并(如本例所示)连接。

/*Not valid syntax but better reflects the plan*/ 
SELECT p.ProductID,
       p.ProductName
FROM   Products p
       LEFT ANTI SEMI JOIN [Order Details] od
         ON p.ProductId = od.ProductId 

如果[Order Details].ProductID为NULL-able，则查询将变为

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL) 

这样做的原因是，如果[Order Details]包含任何NULL ProductId，正确的语义是不返回任何结果。请参阅额外的反半联接和行数假脱机，以验证添加到计划中的这一点。

​

​

如果还将Products.ProductID更改为NULL-able，则查询将变为

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL)
       AND NOT EXISTS (SELECT *
                       FROM   (SELECT TOP 1 *
                               FROM   [Order Details]) S
                       WHERE  p.ProductID IS NULL) 

出现这种情况的原因是，如果NULL子查询根本不返回任何结果(即[Order Details]表为空)，则不应在结果Products.ProductId中返回[Order Details]表，但除外。在这种情况下，它应该。在我的样本数据计划中，这是通过添加另一个反半连接来实现的，如下所示。

​

​

the blog post already linked by Buckley中显示了这一点的效果。在该示例中，逻辑读取的数量从大约400增加到500,000。

此外，单个NULL可以将行计数减少到零，这使得基数估计非常困难。如果SQL Server假设这种情况会发生，但实际上数据中没有NULL行，那么如果这只是一个更大的查询with inappropriate nested loops causing repeated execution of an expensive sub tree for example的一部分，则执行计划的其余部分可能会灾难性地变得更糟。

但是，这并不是NULL-able列上NOT IN的唯一可能的执行计划。针对AdventureWorks2008数据库的查询的This article shows another one。

对于NOT NULL列上的NOT IN或可以为空或不可为空的列的NOT EXISTS，它提供了以下计划。

​

​

当列更改为NULL-able时，NOT IN计划现在如下所示

​

​

它向计划中添加了一个额外的内部联接操作符。这台仪器是explained here。这一切都是为了将Sales.SalesOrderDetail.ProductID = <correlated_product_id>上以前的单个相关索引查找转换为每个外部行的两个查找。另外一个在WHERE Sales.SalesOrderDetail.ProductID IS NULL上。

由于这是在反半连接下进行的，如果该连接返回任何行，则不会进行第二次查找。但是，如果Sales.SalesOrderDetail不包含任何NULL ProductID，它将使所需的查找操作数增加一倍。

Answer 2

还要注意，NOT IN并不等同于not EXISTS当它为null时。

这篇文章解释得很好。

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

当子查询返回一个null时，NOT IN将不与任何行匹配。

其原因可以通过查看NOT IN操作的实际含义的详细信息来找到。

为了便于说明，假设在名为t的表中有4行，有一个名为ID的列，其值为1..4

WHERE SomeValue NOT IN (SELECT AVal FROM t)

等同于

WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1) AND SomeValue != (SELECT AVal FROM t WHERE ID=2) AND SomeValue != (SELECT AVal FROM t WHERE ID=3) AND SomeValue != (SELECT AVal FROM t WHERE ID=4)

进一步假设AVal为NULL，其中ID = 4。因此，!=比较返回未知。AND的逻辑真值表表明，未知和真是未知的，未知和假是假的。没有可以与UNKNOWN进行AND运算的值来生成TRUE结果

因此，如果该子查询的任何行返回NULL，则整个NOT IN运算符将计算结果为FALSE或NULL，并且不会返回任何记录

Answer 3

如果执行计划者说它们是相同的，那么它们就是相同的。使用任何一种会让你的意图更明显的--在这种情况下，第二种。