“NOT_IN”与“NULL”的邂逅

来源:网络 责任编辑:栏目编辑 发表时间:2013-07-02 00:02 点击:

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

    相关新闻>>

      发表评论
      请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
      用户名: 验证码:点击我更换图片
      最新评论 更多>>

      推荐热点

      • Request.ServerVariables 参数大全
      • 执行全文索引时出现权限不足的解决方法
      • 导入excel文件处理流程节点的解决方案
      • 查看sql修改痕迹(SQL Change Tracking on Table)
      • MongoDB安装为Windows服务方法与注意事项
      • App数据层设计及云存储使用指南
      • PostgreSQL启动过程中的那些事三:加载GUC参数
      • 写给MongoDB开发者的50条建议Tip1
      • Percolator与分布式事务思考(二)
      网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
      Copyright © 2008-2015 计算机技术学习交流网. 版权所有

      豫ICP备11007008号-1