今天處理了一個因“NOT IN”與“NULL”邂逅導致的問題，值得思考和總結，記錄在此，供參考。（感謝John丟擲的問題）
我們以實驗的形式先再現一下這個問題，然後對其分析，最後給出幾種解決方案。

1.建立實驗表T，並分別初始化三條資料，注意T2表中包含一條空（NULL）資料。
sec@ora10g> insert into t1 values (1);
sec@ora10g> insert into t1 values (2);
sec@ora10g> insert into t1 values (3);

sec@ora10g> insert into t2 values (1);
sec@ora10g> insert into t2 values (null);
sec@ora10g> insert into t2 values (3);

sec@ora10g> commit;

2.確認T1表和T2表的資料內容
sec@ora10g> select * from t1;

         X
----------
         1
         2
         3

sec@ora10g> select * from t2;

         X
----------
         1

         3

3.再現問題
嘗試使用“NOT IN”方法獲得不在T2表中出現的T1表資料。
sec@ora10g> select * from t1 where x not in (select x from t2);

no rows selected

問題已重現，明明T1中的資料“2”在T2表中沒有，為什麼沒有返回結果呢？
原因：當子查詢返回含有“NULL”記錄時，使用NOT IN時將不會有返回記錄。
解析：可以這麼理解這個現象：Oracle中的NULL是一個不確定的狀態。以T1表中的記錄“2”為例，在與T2表中的NULL進行比較時，NULL既可以是“2”，也可以不是“2”，因為判斷不了他們的關係，所以只能返回空記錄，此乃無奈之舉。

4.為什麼使用“IN”可以返回“正確”的結果？
sec@ora10g> select * from t1 where x in (select x from t2);

         X
----------
         1
         3

這是顯然的，因為T2表中確確實實的存在著記錄“1”和記錄“3”，因此“1”和“3”被返回。不過，注意，NULL依然是一個不確定的狀態，因此在T1表中的“2”與NULL比較之後仍然是個不確定的結果，因此“2”是不會被返回的。這裡給我們的一個錯覺：T1表中的“2”與T2表中NULL不同（他們其實也可能相同）。

5.諸多解決方案
1）排除“NOT IN”子查詢中存在的NULL值
sec@ora10g> select * from t1 where x not in (select x from t2 where x is not null);

         X
----------
         2

2）使用“NOT EXISTS”改寫
sec@ora10g> select * from t1 where not exists (select * from t2 where t1.x=t2.x);

         X
----------
         2

3）使用“外連線”改寫
sec@ora10g> select t1.* from t1, t2 where t1.x = t2.x(+) and t2.x is null;

         X
----------
         2

道理是相通的，想想為什麼使用“NOT EXISTS”和“外連線”方法改寫後就可以成功？（給大家一個機會，這裡不贅述。）

6.小結
這裡描述的的案例可以用“陷阱”來形容，不過對於這個“陷阱”我們是有諸多解決方案進行規避的。
遇到問題在沉著、冷靜+淡定之後，終有柳暗花明之時。希望您也有思考之後豁然開朗之悅。

Good luck.

secooler
10.02.09

-- The End --

【問題處理】“NOT IN”與“NULL”的邂逅

相關文章