共计 2028 个字符，预计需要花费 6 分钟才能阅读完成。
我们在写 SQL 时经常会用到 in 条件，如果 in 包含的值都是非 NULL 值，那么没有特殊的，但是如果 in 中的值包含 null 值 (比如 in 后面跟一个子查询，子查询返回的结果有 NULL 值)，Oracle 又会怎么处理呢？
创建一个测试表 t_in
linuxidc@linuxidc>create table t_in(id number);
Table created.
linuxidc@linuxidc>insert into t_in values(1);
1 row created.
linuxidc@linuxidc>insert into t_in values(2);
1 row created.
linuxidc@linuxidc>insert into t_in values(3);
1 row created.
linuxidc@linuxidc>insert into t_in values(null);
1 row created.
linuxidc@linuxidc>insert into t_in values(4);
1 row created.
linuxidc@linuxidc>commit;
Commit complete.
linuxidc@linuxidc>select * from t_in;
ID
———-
1
2
3
4
现在 t_in 表中有 5 条记录
1、in 条件中不包含 NULL 的情况
linuxidc@linuxidc>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@linuxidc>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@linuxidc>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@linuxidc>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@linuxidc>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-03/141698.htm
