今天處理了一個因“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;sec@ora10g> select * from t1;sec@ora10g> select * from t2;sec@ora10g> select * from t1 where x not in (select x from t2);sec@ora10g> select * from t1 where x in (select x from t2);sec@ora10g> select * from t1 where x not in (select x from t2 where x is not null);sec@ora10g> select * from t1 where not exists (select * from t2 where t1.x=t2.x);sec@ora10g> select t1.* from t1, t2 where t1.x = t2.x(+) and t2.x is null;
2.確認T1表和T2表的數據內容
X
----------
1
2
3
X
----------
1
3
3.再現問題
嘗試使用“NOT IN”方法獲得不在T2表中出現的T1表數據。
no rows selected
問題已重現，明明T1中的數據“2”在T2表中沒有，為什麼沒有返回結果呢？
原因：當子查詢返回含有“NULL”記錄時，使用NOT IN時將不會有返回記錄。
解析：可以這麼理解這個現象：Oracle中的NULL是一個不確定的狀態。以T1表中的記錄“2”為例，在與T2表中的NULL進行比較時，NULL既可以是“2”，也可以不是“2”，因為判斷不了他們的關系，所以只能返回空記錄，此乃無奈之舉。
4.為什麼使用“IN”可以返回“正確”的結果？
X
----------
1
3
這是顯然的，因為T2表中確確實實的存在著記錄“1”和記錄“3”，因此“1”和“3”被返回。不過，注意，NULL依然是一個不確定的狀態，因此在T1表中的“2”與NULL比較之後仍然是個不確定的結果，因此“2”是不會被返回的。這裡給我們的一個錯覺：T1表中的“2”與T2表中NULL不同（他們其實也可能相同）。
5.諸多解決方案
1）排除“NOT IN”子查詢中存在的NULL值
X
----------
2
2）使用“NOTEXISTS”改寫
X
----------
2
3）使用“外連接”改寫
X
----------
2
道理是相通的，想想為什麼使用“NOT EXISTS”和“外連接”方法改寫後就可以成功？（給大家一個機會，這裡不贅述。）
6.小結
這裡描述的的案例可以用“陷阱”來形容，不過對於這個“陷阱”我們是有諸多解決方案進行規避的。
遇到問題在沉著、冷靜+淡定之後，終有柳暗花明之時。希望您也有思考之後豁然開朗之悅。
Good luck.
secooler
10.02.09
-- The End --
=========================================
IN & NOT IN & EXISTS & NOT EXISTS & TABLE JION
當子查詢有返回null值時，not in不會有結果返回，如：
SQL> select ename from emp where deptno not in(10,20,null)
2 /
no rows selected
原因是：
deptno not in(10, 20, null) ===>
not (deptno in(10, 20, null)) ===>
not (deptno=10 or deptno=20 or deptno=null) ===>
deptno<>10 and deptno<>20 and deptno<>null ===>
deptno<>10 and deptno<>20 and unkown ===>
deptno<>10 and deptno<>20 and false ===>false
下面是兩個在NOT IN中使用子查詢的例子，先看沒有null的情況：
SQL> select ename, deptno from emp;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> select dname from dept where deptno not in(select deptno from emp);
DNAME
--------------
OperaTIONS
再看子查詢包含null值的情況：
SQL> insert into emp(empno, ename,deptno)
2 values(9999,'LAW',null)
3 /
1 row created.
SQL> select ename, deptno from emp;
ENAME DEPTNO
---------- ----------
LAW
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
15 rows selected.
SQL> select dname from dept where deptno not in(select deptno from emp);
no rows selected
SQL> select dname from dept
2 where deptno not in(select deptno from emp where deptno is not null)
3 /
DNAME
--------------
OperaTIONS
而子查詢包含null時，用in卻不會有問題：
SQL> select ename,deptno from emp where deptno in(10,20,null)
2 /
ENAME DEPTNO
---------- ----------
SMITH 20
JONES 20
CLARK 10
SCOTT 20
KING 10
ADAMS 20
FORD 20
MILLER 10
8 rows selected.
IN一般可以改為EXISTS：
SQL> select dname from dept
2 where deptno in(select deptno from emp)
3 /
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
SQL> select dname from dept
2 where exists(select * from emp where emp.deptno=dept.deptno)
3 /
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
而NOT IN一般可以改為NOT EXISTS：
SQL> select dname from dept
2 where deptno not in(select deptno from emp where deptno is not null)
3 /
DNAME
--------------
OperaTIONS
SQL> select dname from dept
2 where not exists(select * from emp where dept.deptno=emp.deptno)
3 /
DNAME
--------------
OperaTIONS
使用NOT EXISTS，即使子查詢中包含NULL值，也會得到正確結果。原因是：
select * from emp where dept.deptno=null不會有返回值，這樣，EXISTS(select * from emp where dept.deptno=null)返回的布爾值為false，而 NOT(false)顯然為TRUE，其他條件與之作and後，依然得到true。
而在NOT IN子句中，是NOT(deptno=null)，即NOT(unkown)，結果依然為unkown，而unkown被當作false，其他條件與之作and後，最後得到false。
NOT EXISTS(select * from emp where dept.deptno=null) ===>
NOT (false) ===>TRUE
所以子查詢中的null不會影響其他的查詢結果。
EXISTS可以改為使用表連接語法：
SQL> select dname from dept
2 where exists(select * from emp where emp.deptno=dept.deptno)
3 /
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
SQL> select distinct dname from dept,emp
2 where dept.deptno=emp.deptno
3 /
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
修改為表連接語法後，性能會有所提高，而且可以在select子句中查詢另外一個表中的列。
EXISTS還可以改為使用count(*)，因為這時對於每個dept表中的deptno要遍歷整個emp表，很明星性能會差：
SQL> select dname from dept
2 where (select count(*) from emp where emp.deptno=dept.deptno)>0
3 /
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
NOT EXISTS可以修改為outer join：
SQL> select dname from dept
2 where not exists(select * from emp where emp.deptno=dept.deptno)
3 /
DNAME
--------------
OperaTIONS
SQL> select dname from dept
2 left outer join emp on dept.deptno=emp.deptno
3 where emp.deptno is null
4 /
DNAME
--------------
OperaTIONS
where emp.deptno is null是左外連接的結果中的emp.deptno為null，而不是指原來的emp表。
EXISTS與NOT EXISTS改為表連接語法後，性能一般都會提高。
下一步再研究這幾種語法在執行計劃上的差別：
IN與EXISTS的執行計劃相同，與table join稍有區別。
NOT EXISTS與左外連接的執行計劃相同，與NOT IN稍有區別。
有待於進一步研究其效率上的差異。
