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