在SQL Server中为什么不建议使用Not In子查询

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

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

 

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

 

结果不准确问题

    在SQL Server中，Null值并不是一个值，而是表示特定含义，其所表示的含义是“Unknow”，可以理解为未定义或者未知，因此任何与Null值进行比对的二元操作符结果一定为Null，包括Null值本身。而在SQL Server中，Null值的含义转换为Bool类型的结果为False。让我们来看一个简单的例子，如图1所示。

图1.Null值与任何值进行对比结果都为Null

    SQL Server提供了“IS”操作符与Null值做对比，用于衡量某个值是否为Null。

 

    那么Not In 的问题在哪呢，如图2所示。

   

图2.Not In产生不准确的值

 

     在图2中，条件3不属于Not In后面列表的任意一个，该查询却不返回任何值，与预期的结果不同，那么具体原因就是Not In子句对于Null值的处理，在SQL Server中，图2中所示的Not In子句其实可以等价转换为如图3所示的查询。

图3.对于Not In子句来说，可以进行等价转换

 

    在图3中可以看到Not In可以转换为条件对于每个值进行不等比对，并用逻辑与连接起来，而前面提到过Null值与任意其他值做比较时，结果永远为Null，在Where条件中也就是False，因此3<>null就会导致不返回任何行，导致Not In子句产生的结果在意料之外。

    因此，Not In子句如果来自于某个表或者列表很长，其中大量值中即使存在一个Null值，也会导致最终结果不会返回任何数据。

解决办法？

    解决办法就是不使用Not In，而使用Not Exists作为替代。Exists的操作符不会返回Null，只会根据子查询中的每一行决定返回True或者False，当遇到Null值时，只会返回False，而不会由某个Null值导致整个子查询表达式为Null。对于图2中所示的查询，我们可以改写为子查询，如图4所示。

图4.Not Exists可以正确返回结果

 

Not In导致的查询性能低下

    前面我们可以看出，Not In的主要问题是由于对Null值的处理问题所导致，那么对Null值的处理究竟为什么会导致性能问题？让我们来看图5的示例。图5中，我们使用了Adventurework示例数据库，并为了演示目的将SalesOrderDetail表的ProductId的定义由Not Null改为Null，此时我们进行一个简单的Not In查询。如图5所示。

图5.Not In的执行计划

 

    在图5中，我们看到一个Row Count Spool操作符，该操作符用于确认ProductId列中是否有Null值（过程是对比总行数和非Null行数，不想等则为有Null值，虽然我们知道该列中没有Null值，但由于列定义是允许Null的，因此SQL Server必须进行额外的确认），而该操作符占用了接近一半的查询成本。因此我们对比Not Exists，如图6所示。

图6.Not In Vs Not Exists

 

    由图6可以看出，Not In的执行成本几乎是Not Exists的3倍，仅仅是由于SQL Server需要确认允许Null列中是否存在Null。根据图3中Not In的等价形式，我们完全可以将Not In转换为等价的Not Exist形式，如图7所示。

图7.Not In转换为Not Exists

    我们来对比图7和其等价Not In查询的成本，如图8所示。

图8.成本上完全等价

 

    因此我们可以看到Not In需要额外的步骤处理Null值，上述情况是仅仅在SalesOrderDetail表中的ProductId列定义为允许Null，如果我们将SalesOrderHeader的SalesOrderID列也定义为允许Null时，会发现SQL Server还需要额外的成本确认该列上是否有Null值。如图9所示。

图9.SQL Server通过加入Left Anti Semi Join操作符解决列允许Null的问题

 

此时Not In对应的等价Not Exist形式变为如代码清单1所示。

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

代码清单1.当连接列两列定义都允许Null时，Not In等价的Not Exists形式

 

    此时我们简单对比Not In和Not Exists的IO情况，如图10所示。

图10.Not In吃掉很高的IO

 

小结

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