1. 构建虚拟表
create table tmp01 as
with tmp as (
select 1 as id from dual union all
select 2 from dual union all
select 3 from dual union all
select null from dual
)
select * from tmp;
create table tmp02 as
with tmp as (
select 1 as id from dual union all
select 2 from dual union all
select null from dual
)
select * from tmp;
select id
from tmp01
where id not in ( select id from tmp02 );
2. 在使用not exitis常常碰到类似的查询not exists ( select 1 from tmp02 where tmp02.id=tmp01.id )或者not exists ( select null from tmp02 where tmp02.id=tmp01.id ),其实它们的结果是一样的,都是返回t1表与t2表的差集
SQL> select id,CASE WHEN ID IS NULL THEN 'id is null' ELSE 'id is not null' END "id null" from tmp01 where not exists ( select 1 from tmp02 where tmp02.id=tmp01.id );
ID id null
---------- --------------
id is null
3 id is not null
SQL> select id,CASE WHEN ID IS NULL THEN 'id is null' ELSE 'id is not null' END "id null" from tmp01 where not exists ( select NULL from tmp02 where tmp02.id=tmp01.id );
ID id null
---------- --------------
id is null
3 id is not null
SQL> select id,CASE WHEN ID IS NULL THEN 'id is null' ELSE 'id is not null' END "id null" from tmp01 where not exists ( select 'c' from tmp02 where tmp02.id=tmp01.id );
ID id null
---------- --------------
id is null
3 id is not null