NOT IN和不存在

如果执行计划说它们是一样的，它们就是一样的。使用任何一个能让你的意图更明显的方法——在这种情况下，使用第二个。

这取决于. .

SELECT x.col
FROM big_table x
WHERE x.key IN( SELECT key FROM really_big_table );

不会相对较慢，也没有太多限制查询检查的大小，看看他们的键是否在。在这种情况下，存在是可取的。

但是，根据DBMS的优化器，这可能没有什么不同。

作为一个例子，当存在更好

SELECT x.col
FROM big_table x
WHERE EXISTS( SELECT key FROM really_big_table WHERE key = x.key);
AND id = very_limiting_criteria

事实上，我相信这是最快的:

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

在您的特定示例中，它们是相同的，因为优化器已经发现您正在尝试做的事情在两个示例中是相同的。但在非平凡的例子中，优化器可能不会这样做，在这种情况下，有时有理由更喜欢其中一个。

如果在外层选择中测试多行，NOT IN应该是首选。NOT IN语句中的子查询可以在执行开始时求值，并且可以根据外部选择中的每个值检查临时表，而不是像使用NOT EXISTS语句那样每次都需要重新运行子选择。

如果子查询必须与外部选择相关，那么NOT EXISTS可能更可取，因为优化器可能会发现一个简化，防止创建任何临时表来执行相同的功能。

如果优化器说它们是相同的，那么就考虑人为因素。我更喜欢看NOT EXISTS:)

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

这篇文章解释得很好

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/ < a href = " http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/ " > < / >

当子查询返回一个null时，NOT IN将不匹配任何null 行。< / p > 这其中的原因可以通过查看的细节来找到

. 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进行AND运算以产生结果TRUE

的值 因此，如果子查询中的任何一行返回NULL，则整个NOT IN 运算符将计算为FALSE或NULL，没有记录 返回< / p >

我总是默认为NOT EXISTS。

目前的执行计划可能是相同的，但如果将来任何一列被更改为允许# eyz0，那么NOT IN版本将需要做更多的工作(即使数据中实际上没有# eyz0)，而NOT IN(如果数据中存在# eyz0 是)的语义不太可能是您想要的。

当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)

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

它的效果显示在巴克利已经链接了博客文章中。在这个例子中，逻辑读取的数量从400增加到500,000。

此外，单个NULL可以将行数减少到零，这使得基数估计非常困难。如果SQL Server假设会发生这种情况，但实际上数据中没有NULL行，那么如果这只是一个更大的查询例如，不适当的嵌套循环导致昂贵的子树重复执行的一部分，那么执行计划的其余部分可能会灾难性地更糟。

然而，这并不是在NULL-able列上执行NOT IN的唯一可能的执行计划。这篇文章展示了另一种用于对AdventureWorks2008数据库的查询。

对于NOT NULL列上的NOT IN或NOT EXISTS列上的可空或不可空列，它给出以下计划。

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

它向计划中添加了一个额外的内部连接运算符。这个仪器是这里介绍的。这一切都是为了将之前在Sales.SalesOrderDetail.ProductID = <correlated_product_id>上的单个相关索引查找转换为每个外部行两个查找。另外一个在WHERE Sales.SalesOrderDetail.ProductID IS NULL上。

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

我在用

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。

我有一个大约有120,000条记录的表，需要只选择那些不存在的(匹配一个varchar列)，在其他四个表中，行数约为1500,4000,40000,200。所有相关的表在相关的Varchar列上都有唯一的索引。

NOT IN花了大约10分钟，NOT EXISTS花了4秒。

我有一个递归查询，它可能有一些未调优的部分，这可能会贡献10分钟，但另一个选项需要4秒解释，至少对我来说，NOT EXISTS要好得多，或者至少IN和EXISTS并不完全相同，在继续代码之前总是值得检查。

它们非常相似，但并不完全相同。

在效率方面，我发现左连接为空语句更有效率(当要选择大量行时)

数据库表模型

让我们假设我们的数据库中有以下两个表，它们形成了一对多表关系。

student表是父表，student_grade表是子表，因为它有一个student_id外键列引用学生表中的id主键列。

student table包含以下两条记录:

并且，student_grade表存储学生获得的成绩:

SELECT
student_grade.student_id
FROM
student_grade
WHERE
student_grade.grade = 10 AND
student_grade.class_name = 'Math'
ORDER BY
student_grade.student_id

SELECT
id, first_name, last_name
FROM
student
WHERE EXISTS (
SELECT 1
FROM
student_grade
WHERE
student_grade.student_id = student.id AND
student_grade.grade = 10 AND
student_grade.class_name = 'Math'
)
ORDER BY id

SELECT
id, first_name, last_name
FROM
student
WHERE NOT EXISTS (
SELECT 1
FROM
student_grade
WHERE
student_grade.student_id = student.id AND
student_grade.grade < 9
)
ORDER BY id