not exists 与 select null,select 1的作用

最新推荐文章于 2025-05-19 21:22:02 发布

原创最新推荐文章于 2025-05-19 21:22:02 发布 · 5.1k 阅读

4 ·

CC 4.0 BY-SA版权

oracle_sql 专栏收录该内容

37 篇文章

订阅专栏

本文通过创建两个虚拟表tmp01和tmp02，演示了如何使用SQL语句中的NOT EXISTS来找出两表之间的差集，并比较了不同SELECT子句（如SELECT 1, SELECT NULL等）在NOT EXISTS条件下的效果一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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