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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29324876/viewspace-1308471/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29324876/viewspace-1308471/