[原]not in/not exists 的 null 陷阱

以前遇到了 not in 子查詢的一個 null 陷阱，有經驗的朋友可能知道怎么回事了，用代碼來說就是：

-- 創建兩張測試表：
create table tmp01 as 
with tmp as (
  select 1 as id from dual  union all
  select 2       from dual  union all
  select 3       from dual  union all
  select null    from dual
)
select * from tmp;

create table tmp02 as 
with tmp as (
  select 1 as id from dual  union all
  select 2       from dual  union all
  select null    from dual
)
select * from tmp;

我現在想知道表tmp01有哪些id值不在tmp02中，于是我隨手就寫了一條語句：

select id
from tmp01 
where id not in ( select id from tmp02 )

我期望的結果是：

         ID
----------
         3

但實際結果卻是：

no rows selected

近日讀到了dinjun123的大作《符合列NULL問題的研究》，終于靜下心來想想這個問題。

通常使用 not in / not exists 的場景是希望得到兩個集合的“差集”，與真正的差集又略有不同，后文將會提到，一般的寫法有兩種：

select id from tmp01 where id not in ( select id from tmp02 )
select id from tmp01 where not exists ( select 1 from tmp02 where tmp02.id=tmp01.id )

正如上文提到的例子，第一條語句沒有可返回的行（no rows selected），第二條語句返回了結果是：

         ID
----------
    (null)
         3

為什么第一個沒有結果呢？

我們可以將第一條語句重寫為：

select id from tmp01 where id<>1 and id<>2 and 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，我們再逐個值來跟蹤一下，這里我用笛卡爾乘積來獲得結果：

set pagesize 6;
select 
  tmp01.id "tmp01.id" , 
  tmp02.id "tmp02.id" , 
(select case when count(*)>0 
             then '   Yes         ' 
             else '   No          ' 
             end  from dual where tmp01.id=tmp02.id) "Result Exists?" 
from tmp01,tmp02
order by 1,2

結果如下：

  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改名：

rename tmp01 to tmp01_with_null;
rename tmp02 to tmp02_with_null;

我們看看測試用例：

 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語句改寫為：

-- not in 求差集
with tmp01 as (
  select id from tmp01_with_null --where id is not null
),
tmp02 as (
  select id from tmp02_with_null --where id is not null 
)
-- start here
select id from tmp01 
where id not in ( select id from tmp02 where id is not null )
-- 以下是新加的，應付 test case 2
union all
select null from dual 
where exists ( select 1 from tmp01 where id is null )
and not exists ( select 1 from tmp02 where id is null )

-- not exists 求差集
with tmp01 as (
  select id from tmp01_with_null --where id is not null
),
tmp02 as (
  select id from tmp02_with_null --where id is not null 
)
-- start here
select id from tmp01 
where not exists ( 
  select 1 from tmp02 
  where (tmp02.id=tmp01.id) 
  -- 這行是新加的，應付 test case 1
  or (tmp02.id is null and tmp01.id is null )  
)

寫了這么多，有人會提議使用minus操作符：

with tmp01 as (
  select id from tmp01_with_null --where id is not null
),
tmp02 as (
  select id from tmp02_with_null --where id is not null 
)
-- start here 
select id from tmp01
minus
select id from tmp02 

貌似語句很簡單，但是結果確不一樣，請看下面這條語句：

with tmp01 as (
  select id from tmp01_with_null --where id is not null
  union all                      -- 注意這里，現在tmp01已經有重復行了
  select id from tmp01_with_null -- 注意這里，現在tmp01已經有重復行了
),
tmp02 as (
  select id from tmp02_with_null --where id is not null 
)
-- start here 
select 'minus ' as sql_op,id from tmp01
minus
select 'minus ',id from tmp02 
union all
-- not in
select 'not in',id from tmp01 
where id not in ( select id from tmp02 where id is not null )
union all
select 'not in',null from dual 
where exists ( select 1 from tmp01 where id is null )
and not exists ( select 1 from tmp02 where id is null )
union all
-- not exists
select 'not exists',id from tmp01 
where not exists ( 
  select 1 from tmp02 
  where (tmp02.id=tmp01.id) 
  -- 這行是新加的，應付 test case 1
  or (tmp02.id is null and tmp01.id is null )  
);

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 的問題,不知道大家見到過嗎？