一、用法

exists: （）内子查询语句返回结果不为空，说明where条件成立就会执行主sql语句。如果为空就表示where条件不成立，sql语句就不会执行。not exists 和 exists相反，子查询语句结果为空，则表示where条件成立，执行sql语句。否则不执行。

exists : 强调是否返回结果集，不要求知道返回什么，与in的区别就是，in只能返回一个字段值，exists允许返回多个字段。如：

select name from student where sex = 'm' and mark exists(select 1 from grade where ...) 

exists引导的子句有结果集返回，那么exists这个条件就算成立了,返回的字段始终为1，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

• exists （sql返回的结果为真）
• not exists (sql不返回的结果为真）

exists 效率一般优于 in ，使用EXISTS，Oracle会首先检查主查询，然后运行子查询知道它找到第一个匹配项，这就节省了时间。IN 子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。

exists 效率优于DISTINCT 当，提交一对多表信息的查询时，避免在SELECT 子句中使用DISTINCT

exists适合外表的结果集小的情况
in适合内外表都很大的情况

二、实例

1.exists简单用法

create table a(
  id int,
  name varchar(10)
);
insert into a values(1,'A1');
insert into a values(2,'A2');
insert into a values(3,'A3');

1.png

create table b(
  id int,
  aid int,
  name varchar(10)
);
insert into b values(1,1,'B1');
insert into b values(2,2,'B2');
insert into b values(3,2,'B3');

2.png

表A和表B示1对多的关系 A.ID => B.AID

SELECT ID,NAME FROM A WHERE 
EXISTS(SELECT * FROM B WHERE A.ID = B.AID)

执行结果：

3.png

分解

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1) 
--->SELECT * FROM B WHERE B.AID=1有值返回TRUE所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2) 
--->SELECT * FROM B WHERE B.AID=2有值返回TRUE所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3) 
--->SELECT * FROM B WHERE B.AID=3无值返回TRUE所以没有数据 

• NOT EXISTS 就是反过来

以上代码等价于：

SELECT id, name from A WHERE id in (
select aid from B)

SQL中IN, NOT IN, EXISTS, NOT EXISTS 的用法和差别：

• IN：确定给定的值是否与子查询或列表中的值相匹配。IN关键字选择与列表中的任意一个值匹配的行。IN 关键字之后的项目必须用逗号隔开，并且括在括号中。
• NOT IN : 通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。
• EXISTS 指定一个子查询，检测行的存在。相当于两个集合的交集。EXISTS 后面可是整句的查询语句，IN的后面只能是对单列。
• NOT EXISTS 相当于两个集合的差集。
• EXISTS 和 NOT EXISTS 返回的结果类型是 Boolean，如果子查询包含行，则返回TRUE/FALSE

二、EXISTS 替代DISTINCT

SELECT distinct dept_no, dept_name from dept D, EMP E 
WHERE D.dept_no = E.dept_no;

SELECT dept_no, dept_name from dept D 
WHERE EXISTS (
   SELECT 1 from emp E WHERE E.dept_no =D.dept_no
);

RDBMS 核心模块将在子查询的条件一旦满足后，立即返回结果，所以自带去重。

三、按A列去重，按B列取条件（如最大/小值等）

1. 在下表中查询出，同一ID内的B_DATE最近的记录。

   
   4.png

SELECT * from t t1 
   WHERE NOT EXISTS(
      select * from t where id = t1.id and b_date>t1.b_date)

结果：

5.png

分析：
子查询中，先看id = 1 的情形，只有当t1.b_date 取最大值时，没有返回结果，因为是NOT EXISTS关键字，所以Where条件成立，返回符合条件的查询结果。