在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所示。

image

圖1.Null值與任何值進行對比結果都為Null

    SQL Server提供了“IS”操作符與Null值做對比，用于衡量某個值是否為Null。

    那麼Not In 的問題在哪呢，如圖2所示。

image   

圖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等價形式，将會減少很多麻煩。

本文轉自CareySon部落格園部落格，原文連結：http://www.cnblogs.com/CareySon/p/4955123.html如需轉載請自行聯系原作者