为什么不建议使用not in子查询

在SQL Server中，子查询可以分为相关子查询和无关子查询，对于无关子查询来说，not in子句比较常见，但not in潜在会带来下面两种问题：

• 结果不准确
• 查询性能低下

下面我们来看一下为什么尽量不使用not in子句。

问题1：结果不准确

在SQL Server中，null值并不是一个值，而是表示特定含义，其所表示的含义是“Unknow”，可以理解为未定义或者未知，因此任何与null值进行比对的二元操作符结果一定为null，包括null值本身。

IF (NULL=NULL) PRINT 1
IF (NULL=1) PRINT 1

• 结果：无返回值，null值与任何值进行对比结果都为null。
• 注意：SQL Server提供了“IS”操作符与null值做对比，用于衡量某个值是否为null。

那么 not in 的问题在哪呢?

SELECT '有值' WHERE 3 NOT IN (1,2,NULL)

• 结果：无返回值。难道不是返回有值吗？！
• 分析：条件“3”不属于not in后面列表的任意一个，该查询却不返回任何值。具体原因就是not in子句对于null值的处理，在SQLServer中，not in子句其实等价于如下子句(同样无返回值)：

SELECT '有值' WHERE 3 <> 1 AND 3 <> 2 AND 3 <> NULL

这里，not in转换为条件对于每个值进行不等<>比对，并用逻辑与（AND）连接起来，而前面提到过null值与任意其他值做比较时，结果永远为null，即3<>null为false，从而导致not in子句不返回任何行。

因此，如果not in子句的列表值中即使存在一个null值，就会导致结果不会返回任何数据。

解决办法：不使用not in，而用not exists代替。

exists的操作符不会返回null，只会根据子查询中的每一行决定返回true或者false，当遇到null值时，只会返回false，而不会由某个null值导致整个子查询表达式为null。对于上述查询，可以改为如下not exists子查询：

SELECT col1
FROM ( SELECT 3 AS col1 ) AS b
WHERE NOT EXISTS(
  SELECT *
  FROM ( SELECT 1 AS col2 UNION
         SELECT 2 UNION
         SELECT NULL ) AS a
  WHERE a.col2 = b.col1 )

结果：返回 3

问题2：not in导致查询性能低下

前面我们可以看出，not in的主要问题是由于对null值的处理问题所导致，那么对null值的处理究竟为什么会导致性能问题？让我们来看如下not in子查询：

SELECT * FROM SalesOrderHeader
WHERE SalesOrderID NOT IN ( SELECT ProductID FROM SalesOrderDetail )

这里，为了演示目的仅将SalesOrderDetail表的ProductID列的定义由not null改为null。查看执行计划可以看到一个Row Count Spool操作，该操作符用于确认ProductID列中是否有null值（过程是对比总行数和非null行数，不相等则为有null值，虽然我们知道该列中没有null值，但由于列定义是允许null的，因此SQL Server必须进行额外的确认），而该操作符占用了接近一半的查询成本。此时如果改用如下not exists查询：

SELECT * FROM SalesOrderHeader a
WHERE NOT EXISTS ( SELECT * FROM SalesOrderDetail b WHERE a.SalesOrderID = b.ProductID)

结果：查看执行计划发现，not in的执行成本几乎是not exists的N倍，仅仅是由于SQLServer需要确认允许null列中是否存在null。

根据例2中not in的等价形式，可以得出与例3中not in等价的not exist形式：

SELECT * FROM SalesOrderHeader a
WHERE NOT EXISTS ( SELECT * FROM SalesOrderDetail b WHERE a.SalesOrderID = b.ProductID)
  AND NOT EXISTS ( SELECT * FROM SalesOrderDetail b WHERE b.ProductID IS NULL)

因此,我们可以看到not in需要额外的步骤去处理null值。上述情况是仅仅在SalesOrderDetail表中的ProductId列定义为允许null，如果将SalesOrderHeader的SalesOrderID列也定义为允许null时，会发现SQL Server还需要额外的成本确认SalesOrderID列上是否有null值。此时not in对应的等价not exists形式变为如下所示：

--当连接两列定义都允许null时，not in等价的not exists形式
SELECT *
FROM SalesOrderHeadera
WHERE NOT EXISTS (
SELECT *
  FROM SalesOrderDetail b
  WHERE a.SalesOrderID = b.ProductID )
    AND NOT EXISTS (( SELECT *
                      FROM SalesOrderDetail b
                      WHERE b.ProductIDIS NULL
                      ) )
    AND NOT EXISTS ( SELECT 1
                    FROM ( SELECT *
                            FROM SalesOrderHeader
                          ) AS c
                    WHERE c.SalesOrderID IS NULL )

小结

本文阐述了not in的实现原理以及所带来的数据不一致和性能问题，在写查询时，尽量避免使用not in，而转换为本文提供的not exists等价形式，将会减少很多麻烦。