not in 中包含null值是個悲劇

如果使用not in 那麼子查詢中一定不能有null值，如果子查詢中有null值那麼查詢無結果返回。

1.測試 not in 子查詢中包含null值

測試表test_t

SQL> select * from test_t ;   

         ID NAME

---------- --------------------

          1 a

          2 b

          3 c

測試表test_t2

SQL> select * from test_t2 ;

         ID NAME

---------- --------------------

       100 z

test_t表插入一條空資料

SQL> insert into test_t values (4,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_t;

         ID NAME

---------- --------------------

          1 a

          2 b

          3 c
          4

not in 子查詢中包含null 查詢無結果

SQL> select * from test_t2 where name not in (select name from test_t);

no rows selected

刪除空資料

SQL> delete test_t where id=4;

1 row deleted.

SQL> select * from test_t;

         ID NAME

---------- --------------------

          1 a

          2 b

          3 c

not in 子查詢中不包含null 查詢結果正常

SQL> select * from test_t2 where name not in (select name from test_t);

         ID NAME

---------- --------------------

       100 z

總結：當not in 子查詢有null值時，沒有結果返回。

 

2.測試 not exists 子查詢包含null值

not exists 子查詢沒有null值結果正常

SQL> select * from test_t2 t2 where not exists (select 1 from test_t t where t.name=t2.name);

         ID NAME

---------- --------------------

       100 z

test_t表中插入null值

SQL> insert into test_t values(4,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_t;

         ID NAME

---------- --------------------

          1 a

          2 b

          3 c

          4

not exists子查詢有null值時結果也正常

SQL> select * from test_t2 t2 where not exists (select 1 from test_t t where t.name=t2.name);

         ID NAME

---------- --------------------

       100 z

總結：not exists子查詢中是否包含null值結果集都正常

3.測試 in和exists 子查詢包含null值

SQL> insert into test_t2 values (99,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_t2;

         ID NAME

---------- --------------------

       100 z

         99 a

SQL> select * from test_t;

         ID NAME

---------- --------------------

          1 a

          2 b

          3 c

          4

SQL> select * from test_t2 where name in (select name from test_t);

         ID NAME

---------- --------------------

         99 a

SQL> select * from test_t2 t2 where exists (select 1 from test_t t where t.name=t2.name);

         ID NAME

---------- --------------------

         99 a

總結：in和exists子查詢中是否有null值結果集都正常


------------------end-------------------

            DBA_建瑾
            2014.10.23