not in和not exists的区别

先创建测试数据：

 

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);