“NOT_IN”与“NULL”的邂逅

今天处理了一个因“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