KB-詭異的NOT IN查詢，原來是NULL搞鬼

今天被一個詭異的SQL查詢結果搞到昏頭，查兩個TABLE

SELECT * FROM A WHERE ID='123' –> 有資料 
SELECT * FROM B WHERE ID='123' –> 沒資料

由以上結果，推測

SELECT * FROM A WHERE ID NOT IN (SELECT ID FROM B)

該至少有一筆資料才對吧?

答案是不一定!!

若Table B中有部分資料的ID欄位為NULL，則第三個NOT IN查詢的結果將會是空值。

NULL被用來比較時，其結果是很特別的。例如: 以下有五個測試

SELECT 'YES' WHERE 1=null
SELECT 'YES' WHERE 1<>null
SELECT 'YES' WHERE null=null
SELECT 'YES' WHERE null<>null
SELECT 'YES' WHERE null is null

以上五個查詢條件，只有最後一項會為true，這解釋了NOT IN失效的原因。

2016-03-04 補充：有個SET ANSI_NULLS OFF做法可以改變null = null及null <> null的比對結果，不過依據官方文件，此一選項未來會被拿掉，加上會混淆大家對NULL比對的認知，應避免使用。（感謝網友Billy Cheung回饋）