Oracle的where条件in/not in中包含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>
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值为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> 从执行计划中看到优化器对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> 从执行计划中查看优化器对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
页:
[1]