数据库表模型

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

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

student表包含以下两条记录:

并且，student_grade表存储了学生获得的分数:

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

但是，应用程序感兴趣的是显示学生的全名，而不仅仅是标识符，所以我们还需要学生表中的信息。

为了过滤数学成绩为10的学生记录，我们可以使用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行被选中:

外部查询选择我们感兴趣的学生行列返回给客户端。然而，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记录被匹配:

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

我总是默认为不存在。

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

当两者都不是产品。ProductID或[订单详细信息]。该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 

如果[订单详情]。ProductID是可空的，然后查询变成

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，正确的语义是不返回任何结果。查看额外的反半连接和行计数线轴，以验证添加到计划中的这一点。

如果产品。ProductID也被更改为可空查询

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乘积。ProductId不应该在结果中返回，除非not in子查询根本不返回结果(即[Order Details]表为空)。在这种情况下，应该如此。在我的示例数据的计划中，这是通过添加另一个反半连接来实现的，如下所示。

Buckley已经链接了一篇博文，其中显示了这种影响。在这个例子中，逻辑读取的数量从400增加到500,000。

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

然而，对于可空列上的not IN，这并不是唯一可行的执行计划。本文展示了另一个针对AdventureWorks2008数据库的查询。

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

当列更改为可空时，NOT IN计划现在看起来像这样

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

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

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

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

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

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

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

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

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

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

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