in和exists和null，not in和not exists和null的研究

创建测试表：
create table gw1(id int,name varchar2(1));
create table gw2(id int,name varchar2(1));
create table gw3(id int,name varchar2(1));
insert into gw1 values(1,'a');
insert into gw1 values(2,'b');
insert into gw1 values(3,'c');
insert into gw1 values(4,'d');
insert into gw1 values(null,'e');
insert into gw2 values(1,'a');
insert into gw2 values(2,'b');
insert into gw3 values(1,'a');
insert into gw3 values(2,'b');
insert into gw3 values(null,'c');
commit;
SQL> select * from gw1;


        ID N
---------- -
         1 a
         2 b
         3 c
         4 d
           e


5 rows selected.


SQL> select * from gw2;


        ID N
---------- -
         1 a
         2 b


2 rows selected.


SQL> select * from gw3;


        ID N
---------- -
         1 a
         2 b
           c


3 rows selected.








1.null的比较操作符只能是is、is not，不能=，并且null也不是空串


SQL> select 1 from dual where null='';     


no rows selected


SQL> select 1 from dual where null=null;


no rows selected


SQL> select 1 from dual where null is null;


         1
----------
         1


1 row selected.








2.首先看in和exists和null


（1）主查询有null，但是子查询没null。结果：结果完全一样


SQL> select * from gw1 where id in (select id from gw2);                


        ID N
---------- -
         1 a
         2 b


2 rows selected.




SQL> select * from gw1 where exists (select 1 from gw2 where id=gw1.id);


        ID N
---------- -
         1 a
         2 b


2 rows selected.






（2）主查询和子查询都有null，跟上面完全一样，也就是说null in null条件是假的，但是in中有其他值不受影响。比如id in (1,2,null)这里只会出来1，2的值，null的不满足。


SQL> select * from gw1 where id in (select id from gw3);                


        ID N
---------- -
         1 a
         2 b


2 rows selected.




SQL> select * from gw1 where exists (select 1 from gw3 where id=gw1.id); 


        ID N
---------- -
         1 a
         2 b


2 rows selected.








3.下面看not in，not exists，null


（1）主查询有null，子查询没有null。 结果：不一样，not in的没有出来主查询的id为null的行，而not exists出来了id为null的行


SQL> select * from gw1 where id not in (select id from gw2); 


        ID N
---------- -
         4 d
         3 c


2 rows selected.




SQL> select * from gw1 where not exists (select 1 from gw2 where id=gw1.id);


        ID N
---------- -
           e
         4 d
         3 c


3 rows selected.


（2）主查询有null，子查询也有null。 结果：not in没有任何结果，证明not in后面有null的话没有结果。而not exists出来了


SQL> select * from gw1 where id not in (select id from gw3);


no rows selected


SQL> select * from gw1 where not exists (select 1 from gw3 where id=gw1.id);


        ID N
---------- -
           e
         4 d
         3 c


3 rows selected.












总结：


1.null只能用is null来比较


2.in和exists是一样的


3.    in (1,2,null) 可以出来1，2
  not in (1,2,null) 都出不来也就是not in后面不能有null值。


4.not in和not exists不是完全一样的，如果后面的表达式有null值，则not in不能用，可以用not exists




上面只涉及到有null值的情况，对于in exists not in not exist的执行计划与驱动表的选择，表连接方式的选择看下一篇文章。