not exists 与 select null,select 1的作用

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