Oracle的where条件in/notin中包含NULL时的处理-创新互联

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

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：国际域名空间、网站空间、营销软件、网站建设、贵定网站维护、网站推广。

创建一个测试表t_in

zx@TEST>create table t_in(id number); Table created. zx@TEST>insert into t_in values(1); 1 row created. zx@TEST>insert into t_in values(2); 1 row created. zx@TEST>insert into t_in values(3); 1 row created. zx@TEST>insert into t_in values(null); 1 row created. zx@TEST>insert into t_in values(4); 1 row created. zx@TEST>commit; Commit complete. zx@TEST>select * from t_in; ID ----------  1  2  3  4

现在t_in表中有5条记录

１、in条件中不包含NULL的情况

zx@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的情况

zx@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值的情况

zx@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值的情况

zx@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值。

zx@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

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享标题：Oracle的where条件in/notin中包含NULL时的处理-创新互联
本文来源：http://6mz.cn/article/ddeops.html