|
|
Oracle SQL语句中NOT IN子查询中的NULL值陷阱
今天在使用Oracle数据库写存储过程时，发现了一个NOT IN子查询的null值陷阱。看了点资料，大概记录如下。
1、问题记录
本来是要查出A表中col列值在B表col列中没有出现过的记录。实际数据库是有符合条件的记录的，但是，运行如下SQL：
Select *
From A
where A.col not in (Select B.col from B)
始终查不出结果。经查，原来是查询子句：
Select B.col from B
查出的结果集中有空值导致的。也就是说，当not in后面跟的结果集中有null值时，not in子句返回false。下面是一个例子：
select 'Val1' Col1
from dual
where 2 not in (1, NULL);
运行之后，查不出结果，如下图：
null compare directly comes false
将in语句理解为若干个等式的or条件组合，将not in语句理解为若干个不等式的and组合，而null和任何值的比较运算的结果都是false。这样，就不难理解了。
2、解决方法
大概有两种方法，一种是将null值过滤掉，另一种是用not exists子句。
2.1 not in子句中过滤掉空值
为了避免not in子查询中出现空值，影响查询结果，可以对这部分子查询的结果进行非空过滤。如下：
Select *
From A
where A.col not in (Select B.col from B where B.col is not null)
2.2 使用not exists子句
可以将not in转换为not exists子句：
Select *
From A
where not exists (Select 1 from B where B.col = A.col)
2.3 注意
要注意，上面的两种解决方案中，都不能将A表中col列值为null的记录查出。所以，如果需要用到这些记录，最后需要在查询条件中作补充。如下：
Select *
From A
where A.col not in (Select B.col from B where B.col is not null)
A.col is null
Select *
From A
where not exists (Select 1 from B where B.col = A.col)
A.col is null
Select *
From A
where A.col in (Select B.col from B)
如果想在最后的结果集中包含A.col列为空的记录，也需要通过OR条件控制，如下。
Select *
From A
where A.col in (Select B.col from B)
A.col is null
转://Oracle not in查不到应有的结果(NULL、IN、EXISTS详解)