Nemo

关注TA

路漫漫其修远兮，吾将上下而求索。

普罗旺斯
负责帅就完事了
写了1,496,113字

该文章投稿至Nemo社区数据库板块复制链接

SQL not in 中包含null值是个悲剧

发布于 2017/03/22 20:14 1,664浏览 0回复 2,775字

如果使用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-------------------

点了个评