（點(diǎn)擊上方藍(lán)字，快速關(guān)注我們)

在SQL Server中，子查詢可以分為相關(guān)子查詢和無(wú)關(guān)子查詢，對(duì)于無(wú)關(guān)子查詢來(lái)說(shuō)，Not In子句比較常見(jiàn)，但Not In潛在會(huì)帶來(lái)下面兩種問(wèn)題：

• 結(jié)果不準(zhǔn)確

• 查詢性能低下

下面我們來(lái)看一下為什么盡量不使用Not In子句。

結(jié)果不準(zhǔn)確問(wèn)題

在SQL Server中，Null值并不是一個(gè)值，而是表示特定含義，其所表示的含義是“Unknow”，可以理解為未定義或者未知，因此任何與Null值進(jìn)行比對(duì)的二元操作符結(jié)果一定為Null，包括Null值本身。而在SQL Server中，Null值的含義轉(zhuǎn)換為Bool類型的結(jié)果為False。讓我們來(lái)看一個(gè)簡(jiǎn)單的例子，如圖1所示。

圖1.Null值與任何值進(jìn)行對(duì)比結(jié)果都為Null

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

那么Not In 的問(wèn)題在哪呢，如圖2所示。

圖2.Not In產(chǎn)生不準(zhǔn)確的值

在圖2中，條件3不屬于Not In后面列表的任意一個(gè)，該查詢卻不返回任何值，與預(yù)期的結(jié)果不同，那么具體原因就是Not In子句對(duì)于Null值的處理，在SQL Server中，圖2中所示的Not In子句其實(shí)可以等價(jià)轉(zhuǎn)換為如圖3所示的查詢。

圖3.對(duì)于Not In子句來(lái)說(shuō)，可以進(jìn)行等價(jià)轉(zhuǎn)換

在圖3中可以看到Not In可以轉(zhuǎn)換為條件對(duì)于每個(gè)值進(jìn)行不等比對(duì)，并用邏輯與連接起來(lái)，而前面提到過(guò)Null值與任意其他值做比較時(shí)，結(jié)果永遠(yuǎn)為Null，在Where條件中也就是False，因此3<>null就會(huì)導(dǎo)致不返回任何行，導(dǎo)致Not In子句產(chǎn)生的結(jié)果在意料之外。

因此，Not In子句如果來(lái)自于某個(gè)表或者列表很長(zhǎng)，其中大量值中即使存在一個(gè)Null值，也會(huì)導(dǎo)致最終結(jié)果不會(huì)返回任何數(shù)據(jù)。

解決辦法？

解決辦法就是不使用Not In，而使用Not Exists作為替代。Exists的操作符不會(huì)返回Null，只會(huì)根據(jù)子查詢中的每一行決定返回True或者False，當(dāng)遇到Null值時(shí)，只會(huì)返回False，而不會(huì)由某個(gè)Null值導(dǎo)致整個(gè)子查詢表達(dá)式為Null。對(duì)于圖2中所示的查詢，我們可以改寫(xiě)為子查詢，如圖4所示。

圖4.Not Exists可以正確返回結(jié)果

Not In導(dǎo)致的查詢性能低下

前面我們可以看出，Not In的主要問(wèn)題是由于對(duì)Null值的處理問(wèn)題所導(dǎo)致，那么對(duì)Null值的處理究竟為什么會(huì)導(dǎo)致性能問(wèn)題？讓我們來(lái)看圖5的示例。圖5中，我們使用了Adventurework示例數(shù)據(jù)庫(kù)，并為了演示目的將SalesOrderDetail表的ProductId的定義由Not Null改為Null，此時(shí)我們進(jìn)行一個(gè)簡(jiǎn)單的Not In查詢。如圖5所示。

圖5.Not In的執(zhí)行計(jì)劃

在圖5中，我們看到一個(gè)Row Count Spool操作符，該操作符用于確認(rèn)ProductId列中是否有Null值（過(guò)程是對(duì)比總行數(shù)和非Null行數(shù)，不想等則為有Null值，雖然我們知道該列中沒(méi)有Null值，但由于列定義是允許Null的，因此SQL Server必須進(jìn)行額外的確認(rèn)），而該操作符占用了接近一半的查詢成本。因此我們對(duì)比Not Exists，如圖6所示。

圖6.Not In Vs Not Exists

由圖6可以看出，Not In的執(zhí)行成本幾乎是Not Exists的3倍，僅僅是由于SQL Server需要確認(rèn)允許Null列中是否存在Null。根據(jù)圖3中Not In的等價(jià)形式，我們完全可以將Not In轉(zhuǎn)換為等價(jià)的Not Exist形式，如圖7所示。

圖7.Not In轉(zhuǎn)換為Not Exists

我們來(lái)對(duì)比圖7和其等價(jià)Not In查詢的成本，如圖8所示。

圖8.成本上完全等價(jià)

因此我們可以看到Not In需要額外的步驟處理Null值，上述情況是僅僅在SalesOrderDetail表中的ProductId列定義為允許Null，如果我們將SalesOrderHeader的SalesOrderID列也定義為允許Null時(shí)，會(huì)發(fā)現(xiàn)SQL Server還需要額外的成本確認(rèn)該列上是否有Null值。如圖9所示。

圖9.SQL Server通過(guò)加入Left Anti Semi Join操作符解決列允許Null的問(wèn)題

此時(shí)Not In對(duì)應(yīng)的等價(jià)Not Exist形式變?yōu)槿绱a清單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.當(dāng)連接列兩列定義都允許Null時(shí)，Not In等價(jià)的Not Exists形式

小結(jié)

本文闡述了Not In 的實(shí)現(xiàn)原理以及所帶來(lái)的數(shù)據(jù)不一致和性能問(wèn)題，在寫(xiě)查詢時(shí)，盡量避免使用Not In，而轉(zhuǎn)換為本文提供的Not Exists等價(jià)形式，將會(huì)減少很多麻煩。