创建测试表:
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的执行计划与驱动表的选择,表连接方式的选择看下一篇文章。
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的执行计划与驱动表的选择,表连接方式的选择看下一篇文章。