oracle in 空值替换,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);

运行之后，查不出结果，如下图：

not in null is false, actually.

这块儿具体的原因，可以从如下角度来理解。

首先，要知道，对null值的判断必须采用is null 或者is not null。如下：

null judge should use is null or is not null

除此之外，NULL和其他的值进行比较或者算术运算(、=、!=、+、-、*、/)，结果仍是NULL，也就是false。

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)

OR

A.col is null

或者

Select *

From A

where not exists (Select 1 from B where B.col = A.col)

OR

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)

OR

A.col is null

参考资料