SQL Server Not In子查詢所返回的錯誤結果

SQL Server中使用了Not In 子查詢時，若包含null值，則會使查詢結果不如預期。

舉例如下：

首先建立一個主要的Table (MainTable)並給予值a.g.h







接著建立一個Temp Table並塞入a.b.c.d.e及null值



接著透過Not In來濾掉MainTable中不存在Temp的值，

預期應該是可以得到g.h的結果，

但執行後卻發現得到的結果是空的，與預期結果不符



發生上述狀況，推斷是Not In出了些問題。

經過了解，Not In在此例子中，使用時可以等價轉換成以下SQL



問題就出在Line43－－與NULL值做比對

當 SET ANSI_NULLS 是 ON 時，比較一個或多個 Null 運算式不會產生 TRUE 或 FALSE 的結果，而會產生 UNKNOWN。這是因為未知的值無法與任何值進行邏輯比較。如果運算式與常值 NULL 比較，或是兩個運算式互相比較但其中有一個是 NULL 值，就會發生此狀況。(Technet)

在沒特別設置的情況下，ANSI_NULLS 皆是預設為ON，

因此透過以上敘述，與NULL值的比對皆會回傳UNKNOWN，

而UNKNOWN在boolean的比對中意義則是false，

故才會有如上的結果。

總之在正常情況下，NULL與任何值比對的結果皆會是false，舉例如下：



解決辦法： 使用Not Exists語法



透過Not Exists的寫法，針對NULL的比對只會在單一結果中回傳false，

而不會如Not In般影響全部的結果。

因此在使用上盡可能避免使用Not In語法，

除結果的不準確性外，尚有其效能問題，這部分有機會再來談談。

 
補充：ANSI_NULLS設定 (Microsoft Docs)

為了使NULL比對遵循ISO標準，故有此項設定，也預設為ON，

使得使用「=」或「<>」比對NULL值時必定回傳false之結果，

因此若設定為OFF，則可以正常使用：



要注意的是，若更動此設定值，僅會對單一connection有效，

(全域更動：ALTER DATABASE [DB_NAME] SET ANSI_NULLS OFF WITH NO_WAIT)

然而官方文件也提醒在未來的SQL Server版本中，

將不再支援更改此設定值了，

也因此大家還是乖乖的遵循ISO標準來思考如何撰寫SQL吧！

 
(本文也發表於Yohey66's Corner)