【问题处理】
个人分类:故障处理与分析
今天处理了一个因“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.小结
这里描述的的案例可以用“陷阱”来形容,不过对于这个“陷阱”我们是有诸多解决方案进行规避的
(1,null); select * from t1 where c2 not in (select c2 from t2); no rows found select * from t1 where not exists (select 1 from t2 where...
这里主要分析组合列 NOT IN 非相关子查询中 NULL 的问题。对于单列,如果 NOT IN 非相关 子查询中有 NULL,则没有结果,可以使用 NOT IN 相关子查询、NOT ...
(1,null); select * from t1 where c2 not in (select c2 from t2); no rows found select * from t1 where not exists (select 1 from t2 where...
-- 1. EXISTS谓词 exists/not exists/in/not in 存在量词 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果...
Oracle 中 in 和 notin 的巨大区别在很多软件系统中,系统的性能很大程度上由数据库的性能决定。以前也曾 经做过很多次关于性能方面的各种测试,特别是关于 oracle...
连接代替not in_计算机软件及应用_IT/计算机_专业资料。用连接代替not in ...(+)=B.mobile and A.mobile is null 等价于 select B.mobile from A ...
SQL 中 IN,NOT IN,EXISTS,NOT EXISTS 的用法和差别: IN:确定给定的值是否与子查询或列表中的值相匹配。 IN 关键字使您得以选择与列表中的任意一个值匹配的...
SQL 里的 EXISTS与in、not exists与not in_日语学习_外语学习_教育专区。SQL...select * from t1 where exists ( select null from t2 where y = x ) ...
Dietary Calcium Decreases Plasma Cholesterol Level only in Female but not in Male Hamster Fed a_专业资料。329 Bie nio comdEvrnSi, 21;54:9—9 022()...
NOT IN ( select deptno from emp where deptno is not null) and deptno is not null; Explained SQL> select * from table(dbms_xplan.display); PLAN_...
热门文档