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)

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *