Oracle中EXISTS & NOT EXISTS的使用

目录

1.IN与EXISTS

EXISTS用法总结 

2.NOT IN与NOT EXISTS 

3.not in 中 null的用法

4.EXISTS和IN的区别 (面试常问)

---

1.IN与EXISTS

示例：在 DEPT 表中找出在 EMP 表中存在的部门编号；

方法一：使用in

select DEPTNO
from DEPT
where DEPTNO in (select distinct DEPTNO from EMP);

方法二：使用exists

select DEPTNO
from DEPT d
where exists(select 1 from EMP e where d.DEPTNO = e.DEPTNO);

上面两种方法结果都是一样的： 

IN和EXISTS的区别：IN是将子查询的内容拿到select里面，EXISTS是将值带入子查询

exists要求返回的是一个布尔类型true/false

SELECT deptno FROM emp e WHERE e.deptno=40  --false
SELECT deptno FROM emp e WHERE e.deptno=30  --true
SELECT deptno FROM emp e WHERE e.deptno=20  --true
SELECT deptno FROM emp e WHERE e.deptno=10  --true

1=1 --true
1=2 --false

EXISTS用法总结 

1. EXISTS基本都是放在 WHERE 后面作为过滤条件使用；
2. EXISTS括号里面 SELECT 对应的值无意义，SELECT 任何值都可以；
3. EXISTS后面可以接其它条件； 

2.NOT IN与NOT EXISTS 

示例：在 DEPT 表中找出在 EMP 表中不存在的部门编号 

-- not in
select DEPTNO
from DEPT
where DEPTNO not in (select DEPTNO from EMP group by DEPTNO);

-- not exists
select DEPTNO
from DEPT d
where not exists(select 1 from EMP e where d.DEPTNO = e.DEPTNO);

比较下面两个表： 

CREATE TABLE t2(ID NUMBER);
CREATE TABLE t1(ID NUMBER);

INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(NULL);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(NULL);

SELECT * FROM t1;
SELECT * FROM t2;

                 

--从t1找到t2有的值 in
select ID from t1 where ID in (select ID from t2);
-- 1

--从t1找到t2有的值 exists
select ID from t1 where exists (select ID from t2 where t2.ID = t1.ID);
-- 1

--从t1找到t2没有的值 not in
select * from T1 where ID not in (select id from T2);
-- 没有返回值

--从t1找到t2没有的值 not exists
select ID from T1 where not exists(select ID from t2 where t2.ID = t1.ID);
-- null
-- 2

为什么上面使用not in的时候没有结果，而使用not exists的时候有结果呢？

3.not in 中 null的用法

        NULL在SQL中被视为一种特殊值，它既不等于任何值，也不不等于任何值，包括它自己。

        这意味着，当比较任何值与NULL时，结果都不是TRUE，这样的比较总是未知（NULL）

结论：

        NOT IN 子查询返回NULL，那么整个NOT IN表达式的结果将是NULL

        NOT EXISTS 不会因为子查询返回NULL而影响结果，它只关心子查询是否有结果返回。

        当子查询返回的结果是null时，表示false，前面再加上not exists，双重否定表示肯定，就是true，因此null被成功返回。

场景	EXISTS结果	NOT EXISTS结果
子查询返回至少一行	TRUE	FALSE
子查询返回空集	FALSE	TRUE

--从t1找到t2有的值 exists
select ID from t1 where exists (select ID from t2 where t2.ID = t1.ID);
-- 1

• 对于t1.ID = 1，子查询找到t2.ID = 1，返回1，因此t1的第一行被选中。
• 对于t1.ID = 2，子查询未找到匹配，返回空，因此t1的第二行被过滤。
• 对于t1.ID = NULL，子查询中的t2.ID = NULL永远为UNKNOWN，子查询返回空，因此t1的第三行被过滤。并且返回null也表示false，exists不会返回，exists只会返回子查询结果为true的

4.EXISTS和IN的区别 (面试常问)

IN 是内表驱动外表，适合 内表 比 外表 数据量小的情况

EXISTS 是外表驱动内表， 适合 内表 比 外表 数据量大的情况