不存在vs不存在

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数据库。

Answer 1

我始终默认为NOT EXISTS。

目前执行计划可能是相同的，但是如果将来更改任一列以允许NULLs，则该NOT IN版本将需要做更多的工作（即使NULL数据中实际上没有s），并且NOT INifNULL的语义也存在。无论如何都不太可能成为您想要的。

如果没有Products.ProductID或[Order Details].ProductID允许NULLS中的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) 

原因是正确的语义if如果[Order Details]包含any 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 Products.ProductId不应该在返回的结果只是如果NOT IN子查询是在所有返回任何结果（即[Order Details]表是空的）。在这种情况下应该。在我的样本数据计划中，这是通过添加另一个反半联接来实现的，如下所示。

在Buckley已经链接的博客中可以看到这样的效果。在该示例中，逻辑读取的数量从大约400增加到500,000。

另外，单个NULL可以将行计数减少到零的事实使基数估计非常困难。如果SQL Server假定会发生这种情况，但实际上NULL数据中没有行，则执行计划的其余部分可能会灾难性地恶化，如果这只是较大查询的一部分，并且嵌套循环不当会导致重复执行昂贵的子程序例如树。

但是，这不是-able列NOT IN上唯一可行的执行计划NULL。本文显示了另一个针对AdventureWorks2008数据库的查询。

对于NOT INonNOT NULL列或NOT EXISTS针对null列或non nullable列的，给出以下计划。

当列更改为NULL-able时，该NOT IN计划现在看起来像

它为计划添加了一个额外的内部联接运算符。这里说明该装置。只需要将先前的单个相关索引搜索转换为Sales.SalesOrderDetail.ProductID = <correlated_product_id>每个外行两个搜索。另一个打开WHERE Sales.SalesOrderDetail.ProductID IS NULL。

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