你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle的where条件in/not in中包含NULL时的处理

[日期:2017-11-20] 来源:Linux社区  作者:hbxztc [字体: ]

我们在写SQL时经常会用到in条件,如果in包含的值都是非NULL值,那么没有特殊的,但是如果in中的值包含null值(比如in后面跟一个子查询,子查询返回的结果有NULL值),Oracle又会怎么处理呢?

创建一个测试表t_in
linuxidc@TEST>create table t_in(id number);
 
Table created. 
 
linuxidc@TEST>insert into t_in values(1);
 
1 row created. 
 
linuxidc@TEST>insert into t_in values(2);
 
1 row created. 
 
linuxidc@TEST>insert into t_in values(3);
 
1 row created. 
 
linuxidc@TEST>insert into t_in values(null);
 
1 row created. 
 
linuxidc@TEST>insert into t_in values(4);
 
1 row created. 
 
linuxidc@TEST>commit;
 
Commit complete. 
 
linuxidc@TEST>select * from t_in;
 
    ID
----------
    1
    2
    3
 
    4

现在t_in表中有5条记录
1、in条件中不包含NULL的情况
12345678 linuxidc@TEST>select * from t_in where id in (1,3);
 
    ID
----------
    1
    3
 
2 rows selected.

上面的条件等价于id =1 or id = 3得到的结果正好是2;查看执行计划中可以看到 2 - filter("ID"=1 OR "ID"=3)说明我们前面的猜测是正确的

2、in条件包含NULL的情况
linuxidc@TEST>select * from t_in where id in (1,3,null);
 
    ID
----------
    1
    3
 
2 rows selected.

上面的条件等价于id = 1 or id = 3 or id = null,我们来看下图当有id = null条件时Oracle如何处理

从上图可以看出当不管id值为NULL值或非NULL值,id = NULL的结果都是UNKNOWN,也相当于FALSE。所以上面的查结果只查出了1和3两条记录。
查看执行计划看到优化器对IN的改写

3、not in条件中不包含NULL值的情况
linuxidc@TEST>select * from t_in where id not in (1,3);
 
    ID
----------
    2
    4
 
2 rows selected.

上面查询的where条件等价于id != 1 and id !=3,另外t_in表中有一行为null,它虽然满足!=1和!=3但根据上面的规则,NULL与其他值做=或!=比较结果都是UNKNOWN,所以也只查出了2和4。
从执行计划中看到优化器对IN的改写

4、not in条件中包含NULL值的情况
linuxidc@TEST>select * from t_in where id not in (1,3,null);
 
no rows selected

上面查询的where条件等价于id!=1 and id!=3 and id!=null,根据上面的规则,NULL与其他值做=或!=比较结果都是UNKNOWN,所以整个条件就相当于FALSE的,最终没有查出数据。
从执行计划中查看优化器对IN的改写

总结一下,使用in做条件时时始终查不到目标列包含NULL值的行,如果not in条件中包含null值,则不会返回任何结果,包含in中含有子查询。所以在实际的工作中一定要注意not in里包含的子查询是否包含null值。
linuxidc@TEST>select * from t_in where id not in (select id from t_in where id = 1 or id is null);
 
no rows selected

官方文档:http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements005.htm#SQLRF51096
http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions013.htm#SQLRF52169
http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions004.htm#SQLRF52116

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-11/148750.htm

linux
相关资讯       Oracle NUll  Oracle的where条件 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款