not exists 与 select null,select 1的作用

来源：互联网发布：影楼网络销售赚钱吗编辑：程序博客网时间：2024/06/04 01:01

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 idfrom 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 nullSQL> 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 nullSQL> 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

0 0