不存在与不存在

sql sql-server notin

这些查询中哪个更快？

不存在：

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

我想我会坚持不存在。

Community

我始终默认为 NOT EXISTS。

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

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

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

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

https://i.stack.imgur.com/lCTsG.png

一个相当普遍的误解似乎是，与连接相比，相关子查询总是“坏”的。当他们强制执行嵌套循环计划（逐行评估子查询）时，它们当然可以，但该计划包括反半连接逻辑运算符。反半连接不限于嵌套循环，也可以使用散列或合并（如本例所示）连接。

/*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，则正确的语义是不返回任何结果。请参阅额外的反半联接和行计数假脱机以验证已添加到计划中。

https://i.stack.imgur.com/mPYhd.png

如果 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是空的）。在这种情况下应该。在我的示例数据计划中，这是通过添加另一个反半连接来实现的，如下所示。

https://i.stack.imgur.com/8XAh1.png

其效果如 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 列上的 NOT IN 唯一可能的执行计划。 This article shows another one 用于针对 AdventureWorks2008 数据库的查询。

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

https://i.stack.imgur.com/nahUD.png

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

https://i.stack.imgur.com/8o9PQ.png

它在计划中添加了一个额外的内部连接运算符。这个装置是explained here。它可以将之前在 Sales.SalesOrderDetail.ProductID = <correlated_product_id> 上的单个相关索引查找转换为每个外部行两次查找。另一个在 WHERE Sales.SalesOrderDetail.ProductID IS NULL 上。

由于这是一个反半连接，如果该连接返回任何行，则不会发生第二次搜索。但是，如果 Sales.SalesOrderDetail 不包含任何 NULL ProductID，则所需的查找操作数会加倍。

Baldrick

另请注意，当涉及到 null 时，NOT IN 不等于 NOT EXISTS。

这个帖子解释的很好

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

当子查询返回一个空值时，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，并且不会返回任何记录

John Millikin

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

执行计划时间可能相同，但执行结果可能不同，因此存在差异。如果您的数据集中有 NULL，NOT IN 会产生意想不到的结果（请参阅巴克利的回答）。最好使用 NOT EXISTS 作为默认值。

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 JOIN 将所有内容都过滤掉，然后将 NULL 过滤掉，这样效率会低得多。 Example of that at bottom of this article

刚刚登录以支持您的回答先生。正在寻找同样的问题，我的查询从使用子选择的 4 分钟到使用完全外连接和 IS NULL 的 1 秒 where

Monic

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

NOT IN 大约需要 10 分钟，NOT EXISTS 需要 4 秒。

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

ravish.hacker

我正在使用

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。

Monic

在您的具体示例中，它们是相同的，因为优化器已经确定您要尝试做的事情在两个示例中都是相同的。但有可能在非平凡的例子中优化器可能不会这样做，在这种情况下，有时有理由选择一个而不是另一个。

如果您在外部选择中测试多行，则应该首选 NOT IN。可以在执行开始时评估 NOT IN 语句中的子查询，并且可以根据外部选择中的每个值检查临时表，而不是像 {3 所要求的那样每次都重新运行子查询} 陈述。

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

Paul-Sebastian

数据库表模型

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

https://i.stack.imgur.com/uD3tb.png

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

student table 包含以下两条记录：

id first_name last_name admission_score 1 Alice Smith 8.95 2 Bob Johnson 8.75

而且，student_grade 表存储学生获得的成绩：

id class_name 年级 student_id 1 数学 10 1 2 数学 9.5 1 3 数学 9.75 1 4 科学 9.5 1 5 科学 9 1 6 科学 9.25 1 7 数学 8.5 2 8 数学 9.5 2 9 数学 9 2 10 科学 10 2 11 科学 9.4 2

SQL 存在

假设我们想让所有在数学课上获得 10 分的学生。

如果我们只对学生标识符感兴趣，那么我们可以运行如下查询：

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

但是，应用程序有兴趣显示 student 的全名，而不仅仅是标识符，因此我们还需要 student 表中的信息。

为了过滤在数学中有 10 级的 student 记录，我们可以使用 EXISTS SQL 运算符，如下所示：

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

运行上面的查询时，我们可以看到只选择了 Alice 行：

id first_name last_name 1 爱丽丝·史密斯

外部查询选择我们有兴趣返回给客户端的 student 行列。但是，WHERE 子句将 EXISTS 运算符与关联的内部子查询一起使用。

如果子查询返回至少一条记录，则 EXISTS 运算符返回 true，如果未选择任何行，则返回 false。数据库引擎不必完全运行子查询。如果匹配单个记录，则 EXISTS 运算符返回 true，并选择关联的其他查询行。

内部子查询是相关的，因为 student_grade 表的 student_id 列与外部 student 表的 id 列匹配。

SQL 不存在

假设我们要选择所有成绩不低于 9 的学生。为此，我们可以使用 NOT EXISTS，它否定 EXISTS 运算符的逻辑。

因此，如果底层子查询没有返回记录，NOT EXISTS 运算符将返回 true。但是，如果单个记录被内部子查询匹配，NOT EXISTS 运算符将返回 false，并且可以停止子查询执行。

要将所有没有关联 student_grade 的学生记录与小于 9 的值匹配，我们可以运行以下 SQL 查询：

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

运行上面的查询时，我们可以看到只有 Alice 记录匹配：

id first_name last_name 1 爱丽丝·史密斯

因此，使用 SQL EXISTS 和 NOT EXISTS 运算符的优点是，只要找到匹配的记录，就可以停止内部子查询的执行。

Onga Leo-Yoda Vellem

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

在效率方面，我发现 left join is null 语句更有效（当要选择大量行时）

onedaywhen

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

oglester

这取决于..

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

不会相对较慢，限制查询检查的大小以查看它们是否在其中。 EXISTS 在这种情况下会更可取。

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

作为 EXISTS 何时更好的示例

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

IN 和 EXISTS get the same plan in SQL Server。无论如何，问题是关于 NOT IN 与 NOT EXISTS。

不存在与不存在

关注公众号

想领先一步获取最新的外包任务吗？

相似问题

平台

支持

联系我们