in/not in exists/not exists null的理解
两个测试表
create table tmp01 as
with tmp as (
select '1' as id from dual
union all
select '2' as id from dual
union all
select '3' as id from dual
union all
select null as id from dual
)
select * from tmp;
create table tmp02 as
with tmp as (
select '1' as id from dual
union all
select '2' as id from dual
union all
select null as id from dual
)
select * from tmp;
select * from tmp01 t where t.id in (select * from tmp02);
ID
in可以理解为 t.id = 1 or t.id = 2 or t.id = null
select * from tmp01 t where t.id not in (select * from tmp02);
ID
no rows
not in 可以理解为 t.id <> 1 and t.id <> 2 and t.id <> null
由于t.id <> null,为unkown,始终返回false,所以查不出值来。
解决：
select * from tmp01 t where t.id not in (select * from tmp02 where id is not null) or t.id is null;
---------------------------------------------------------------------------------------------------------------------------------
exists实际上用的也是等值判断
select * from tmp01 t where exists (select 'X' from tmp02 d where d.id = t.id);
ID
子查询中，d.id = t.id 判断出来的只有 1 ，2，null = null不可判断，null只能使用 is null,is not null.
select * from tmp01 t where not exists (select 'X' from tmp02 d where d.id = t.id);
ID
此语句查出了null和3，子查询中查出的是1,2，不在这个集合中的有null和3
再说一下 in/exists的效率问题
两个表中一个数据量小，一个数据量大，则子查询表大的用exists，表小的用in
表tmp01（小表），表tmp02（大表）
select * from tmp01 where id in (select id from tmp02)
效率低，用到了tmp01 表上id 列的索引
select * from tmp01 where exists(select id from tmp02 where id= tmp01.id)
效率高，用到了tmp02 表上id 列的索引。
select * from tmp02 where exists(select id from tmp01 where id= tmp02.cc)
效率高，使用tmp02 表上id 列的索引
select * from tmp02 where exists(select id from tmp01 where id= tmp02.cc)
效率低，用到了tmp01表上id列的索引
not in/not exists
not in内外表进行全表扫描，不使用索引
not extsts 的子查询用到表上的索引。无论表大小，用not exists 都比not in 要快。