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