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

创建一个测试表t_inlinuxidc@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条记录１、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，我们来看 http://www.cppentry.com  编程开发 程序员入门下图当有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