先创建测试数据:
create table test1(id number);
create table test2(id number);
insert into test1 values(1);
insert into test1 values(3);
insert into test1 values(4);
insert into test1 values(null);
insert into test1 values(null);
insert into test1 values(1);
insert into test1 values(3);
insert into test1 values(5);
insert into test1 values(null);
insert into test1 values(null);
看如下SQL执行结果:
select * from test1 t1 where t1.id not in (select id from test2 t2);
select * from test1 t1 where not exists (select 1 from test2 t2 where t2.id = t1.id);
第一个结果为空,第二个结果为
null
null
6
两个结果不同,原因是not in里的值如果有null值会出现意想不到的结果。
看如下SQL:
select * from test 1 where id not in (1,null);
这个SQL相当于这样:
select * from test 1 where id <>1 and id <>null;
我们知道null和所有值比较都为false,所以id <> null一直为false,导致结果为null。
但是in里条件有null不受影响,因为看如下SQL:
select * from test 1 where id in (1,null);
这个SQL相当于这样:
select * from test 1 where id i= 1 or id = null;
由于id = null一直为false,所以上面的SQL变成这样。
select * from test 1 where id i= 1
即in中有null无null,效果一样
再看not exists,SQL如下:
select * from test1 t1 where not exists (select 1 from test2 t2 where t2.id = t1.id);
相当于把test1表的每条记录带入后面的表达式,看是否有记录返回。
1 select 1 from test2 t2 where t2.id = 1 true
3 select 1 from test2 t2 where t2.id = 1 true
4 select 1 from test2 t2 where t2.id = 1 false
null select 1 from test2 t2 where t2.id = null false
null select 1 from test2 t2 where t2.id = null false
由于是not exists,加了个not ,就变成了false false true true true
所以最后的结果为
4
null
null
所以not exists包含了结果为null的记录。
可知:
not in 中包含null时,结果会为空。
not in 外面包含null时,null会被剔除。
not exists外面包含null时,为null的结果也会变显示。
上面的两条SQL,要写成一样应该这样:
select t1.id from test1 t1 where not exists (select 1 from test2 t2 where t2.id = t1.id) and t1.id is not null;
select t1.id from test1 t1 where t1.id not in (select t2.id from test2 t2 where t2.id is not null);