not in/not exists 的 null 陷阱

来源：互联网发布：药学专业网络教育编辑：程序博客网时间：2024/06/05 19:48

SQL之not in关键字2007-03-26 17:28注意：如果not in后的结果集中有null值，则该条件恒为不成立

实例：

select SEQ_STAFF.NEXTVAL, r.RYBM, r.RYXM, r.ID, -1 from RYZK1 r
where r.ID not in (select distinct INNER_ID from OFFICESTAFF where INNER_ID is not null);

以前遇到了 not in 子查询的一个 null 陷阱，有经验的朋友可能知道怎么回事了，用代码来说就是：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建两张测试表：
create table tmp01as
with tmpas (
  select 1as idfrom dual union all
  select 2      from dual union all
  select 3      from dual union all
  select null    from dual
)
select *from tmp;
 
create table tmp02as
with tmpas (
  select 1as idfrom dual union all
  select 2      from dual union all
  select null    from dual
)
select *from tmp;

我现在想知道表tmp01有哪些id值不在tmp02中，于是我随手就写了一条语句：

1
2
3
select id
from tmp01
where idnot in (select idfrom tmp02 )

我期望的结果是：

1
2
3
         ID
----------
         3

但实际结果却是：

1
no rows selected

近日读到了dinjun123的大作《符合列NULL问题的研究》，终于静下心来想想这个问题。

通常使用 not in / not exists 的场景是希望得到两个集合的“差集”，与真正的差集又略有不同，后文将会提到，一般的写法有两种：

1
2
select idfrom tmp01where idnot in (select idfrom tmp02 )
select idfrom tmp01where not exists ( select 1from tmp02where tmp02.id=tmp01.id )

正如上文提到的例子，第一条语句没有可返回的行（no rows selected），第二条语句返回了结果是：

1
2
3
4
         ID
----------
    (null)
         3

为什么第一个没有结果呢？

我们可以将第一条语句重写为：

1
select idfrom tmp01where id<>1and id<>2and id<>null

id=1或者2的时候很好理解，当id=3的时候，id<>null 的判断结果是UNKNOW，注意不是false，where子句只认true，其他都不认，所以tmp01中没有一个值经过 id<>1 and id<>2 and id<>null 这个长长的条件判断后能获得true，也就不会有结果集返回了。

那第二条语句为什么返回的结果是两条呢？3容易理解，null为什么也在结果集中呢？明明tmp02中有null值的啊，我们仔细看一下子查询的where 子句 tmp02.id=tmp01.id，我们再逐个值来跟踪一下，这里我用笛卡尔乘积来获得结果：

1
2
3
4
5
6
7
8
9
10
set pagesize 6;
select
  tmp01.id"tmp01.id" ,
  tmp02.id"tmp02.id" ,
(select case when count(*)>0
             then '   Yes         '
             else '   No          '
             end  from dualwhere tmp01.id=tmp02.id)"Result Exists?"
from tmp01,tmp02
order by 1,2

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  tmp01.id   tmp02.id Result Exists?
---------- ---------- ---------------
         1          1    Yes
         1          2    No
         1      (null)   No
 
  tmp01.id   tmp02.id Result Exists?
---------- ---------- ---------------
         2          1    No
         2          2    Yes
         2     (null)    No
 
  tmp01.id   tmp02.id Result Exists?
---------- ---------- ---------------
         3          1    No
         3          2    No
         3     (null)    No
 
  tmp01.id   tmp02.id Result Exists?
---------- ---------- ---------------
    (null)          1    No
    (null)          2    No
    (null)     (null)    No

从结果来看有这么一个规律：只要 null 参与了比较，Result Exists? 就一定为NO（因为结果是UNKNOW），这个也是关于 null 的基本知识，这就解析了为什么第二条语句的输出是两行。

从上面的分析，我们可以“窥视”出 in/not in 的结果是依赖于“=”等值判断的结果；exists/not exists 虽然是判断集合是否为空，但通常里面的子查询做的是值判断。

知道了造成结果集出乎意料的原因，我们就可以修改我们的SQL了，为了测试方便，将原来的表tmp01和tmp02改名：

1
2
rename tmp01 to tmp01_with_null;
rename tmp02 to tmp02_with_null;

我们看看测试用例：

1
2
3
4
5
6
test case id   tmp01 has null   tmp01 has null  result has null
------------- ---------------- ---------------- ----------------
            1             true             true            false
            2             true            false             true
            3            false             true            false
            4            false            false            false

其中test case 4 就是打酱油的，只要SQL没有写错，一般不会出问题。

最终，SQL语句改写为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- not in 求差集
with tmp01as (
  select idfrom tmp01_with_null--where id is not null
),
tmp02as (
  select idfrom tmp02_with_null--where id is not null 
)
-- start here
select idfrom tmp01
where idnot in (select idfrom tmp02where idis not null )
-- 以下是新加的，应付 test case 2
union all
select null from dual
where exists ( select 1from tmp01where idis null )
and not exists ( select 1from tmp02where idis null )
 
-- not exists 求差集
with tmp01as (
  select idfrom tmp01_with_null--where id is not null
),
tmp02as (
  select idfrom tmp02_with_null--where id is not null 
)
-- start here
select idfrom tmp01
where not exists ( 
  select 1from tmp02
  where (tmp02.id=tmp01.id)
  -- 这行是新加的，应付 test case 1
  or (tmp02.idis null and tmp01.idis null ) 
)

写了这么多，有人会提议使用minus操作符：

1
2
3
4
5
6
7
8
9
10
with tmp01as (
  select idfrom tmp01_with_null--where id is not null
),
tmp02as (
  select idfrom tmp02_with_null--where id is not null 
)
-- start here 
select idfrom tmp01
minus
select idfrom tmp02

貌似语句很简单，但是结果确不一样，请看下面这条语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
with tmp01as (
  select idfrom tmp01_with_null--where id is not null
  union all                      -- 注意这里，现在tmp01已经有重复行了
  select idfrom tmp01_with_null-- 注意这里，现在tmp01已经有重复行了
),
tmp02as (
  select idfrom tmp02_with_null--where id is not null 
)
-- start here 
select 'minus ' as sql_op,idfrom tmp01
minus
select 'minus ',idfrom tmp02
union all
-- not in
select 'not in',idfrom tmp01
where idnot in (select idfrom tmp02where idis not null )
union all
select 'not in',null from dual
where exists ( select 1from tmp01where idis null )
and not exists ( select 1from tmp02where idis null )
union all
-- not exists
select 'not exists',idfrom tmp01
where not exists ( 
  select 1from tmp02
  where (tmp02.id=tmp01.id)
  -- 这行是新加的，应付 test case 1
  or (tmp02.idis null and tmp01.idis null ) 
);

1
2
3
4
5
6
7
SQL_OP             ID
---------- ----------
minus               3
not in              3
not in              3
not exists          3
not exists          3

minus消灭了重复行！这就是前文所说的 not in 和 not exists 并非真正意义上的差集。

刚在博问中发现有位朋友遇到了这个陷阱一个sql 语句in not in 的问题,不知道大家见到过吗？

0 0