不存在而不是存在

哪个查询更快？

不存在:

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.

1> Martin Smith..：

---

我总是默认NOT EXISTS.

该执行计划可以是相同的时刻,但如果任一列在未来改变,以允许NULLS上的NOT IN版本需要做更多的工作(即使没有NULLs为实际存在的数据)和语义NOT IN如果NULL小号都存在无论如何都不太可能是你想要的.

如果没有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) 

这样做的原因是,如果[Order Details]包含任何NULL ProductIds 的正确语义是不返回任何结果.请参阅额外的反半连接和行计数假脱机以验证添加到计划中的此情况.

如果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数据中没有行,执行计划的其余部分可能会更糟糕,如果这只是更大查询的一部分,不恰当的嵌套循环导致重复执行昂贵的子例如树.

但是,这不是NOT INon NULL-able列的唯一可能的执行计划.本文展示了另一个针对AdventureWorks2008数据库的查询.

对于NOT IN上一NOT NULL列或NOT EXISTS反对任何一个可以为空或非空列它提供了以下方案.

当列变为NULL-able时,NOT IN计划现在看起来像

它为计划添加了额外的内连接运算符.这里解释了这个装置.将所有先前的单个相关索引搜索转换为Sales.SalesOrderDetail.ProductID = 每个外行的两个搜索.另外一个是开启的WHERE Sales.SalesOrderDetail.ProductID IS NULL.

因为这是一个反半连接,如果那个返回任何行,第二次搜索将不会发生.但是,如果Sales.SalesOrderDetail不包含任何NULL ProductIDs,它将使所需的查找操作数量翻倍.

---

@xis这些是在SQL Sentry计划资源管理器中打开的执行计划.您还可以在SSMS中以图形方式查看执行计划.

---

请问您如何获得如图所示的分析图？

---

@Mayur在SQL Server中不需要这个.http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists/6140367#6140367

2> buckley..：

---

还要注意,当涉及到null时,NOT IN不等同于NOT EXISTS.

这篇文章很好地解释了它

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

当子查询返回一个null时,NOT IN将不匹配任何行.

通过查看NOT IN操作实际含义的细节可以找到原因.

让我们说,为了说明的目的,表中有4行叫做t,有一个名为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.因此,!=比较返回UNKNOWN.AND的逻辑真值表表明UNKNOWN和TRUE是UNKNOWN,UNKNOWN和FALSE是FALSE.没有值可以与UNKNOWN进行AND运算以产生结果TRUE

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

3> John Milliki..：

---

如果执行计划员说他们是相同的,那么他们就是一样的.使用任何一个会使你的意图更明显 - 在这种情况下,第二个.

---

执行计划程序时间可能相同,但执行结果可能不同,因此存在差异.如果数据集中包含NULL,则NOT IN将产生意外结果(请参阅buckley的答案).最好使用NOT EXISTS作为默认值.

4> James Curran..：

---

实际上,我相信这将是最快的:

SELECT ProductID, ProductName 
    FROM Northwind..Products p  
          outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null

---

@HLGEM不同意.根据我的经验,LOJ的最佳情况是它们是相同的,SQL Server将LOJ转换为反半连接.在最糟糕的情况下,SQL Server LEFT加入所有内容并过滤掉NULL,之后效率会低得多.[本文底部的示例](http://bradsruminations.blogspot.co.uk/2011/10/t-sql-tuesday-023-flip-side-of-join.html)

---

当优化器正在完成它的工作时,可能不是最快的,但当它没有时肯定会更快.

5> 小智..：

---

我有一个表有大约120,000条记录,需要在其他四个表中选择那些不存在(与varchar列匹配)的表,行数约为1500,4000,40000,200.所有涉及的表都有唯一索引关于Varchar专栏.

NOT IN花了大约10分钟,NOT EXISTS花了4秒钟.

我有一个递归查询,可能有一些未调整的部分,可能有10分钟的贡献,但另一个选项花了4秒解释,至少对我来说,这NOT EXISTS是更好或至少那个IN,EXISTS并不完全相同,总是值得一个在进行代码之前检查.

6> Jeffrey L Wh..：

---

在您的具体示例中它们是相同的,因为优化器已经弄清楚您尝试做的是两个示例中相同的内容.但有可能的是,在非平凡的例子中,优化器可能不会这样做,并且在这种情况下,有理由有时会优先选择其中一个.

NOT IN如果您在外部选择中测试多行,则应该首选.NOT IN可以在执行开始时评估语句中的子查询,并且可以针对外部选择中的每个值检查临时表,而不是每次重新运行子NOT EXISTS语句,如语句所需.

如果子查询必须与外部选择相关联,则NOT EXISTS可能是优选的,因为优化器可能发现一种简化,该简化阻止创建任何临时表以执行相同的功能.

7> ravish.hacke..：

---

我在用

SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)

并发现它给出了错误的结果（错误是指没有结果）。由于TABLE2.Col1中为NULL。

将查询更改为

SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)

给了我正确的结果。

从那时起，我开始在每个地方都使用NOT EXISTS。