轉自：http://space.itpub.net/11320622/viewspace-675486
今天處理了一個因“NOT IN”與“”導致的問題，值得思考和總結，記錄在此，供參考。（感謝John丟擲的問題）我們以的形式先再現一下這個問題，然後對其分析，最後給出幾種解決方案。
[@more@]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”改寫
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.小結這裡描述的的案例可以用“陷阱”來形容，不過對於這個“陷阱”我們是有諸多解決方案進行規避的。遇到問題在沉著、冷靜+淡定之後，終有柳暗花明之時。希望您也有思考之後豁然開朗之悅。

“NOT_IN”與“NULL”的邂逅

相關文章