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

共计 1991 个字符，预计需要花费 5 分钟才能阅读完成。

我们在写 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