sql中exists用法

exists关键字介绍

exists强调的是 是否返回结果集，不要求知道返回什么，比如：

SELECT * FROM AM_USER WHERE EXISTS (SELECT 1 FROM AM_ROLE WHERE NAME = 'admin')

只要exists引导的子句有结果集返回，那么exist的条件就成立。SELECT 1 FROM AM_ROLE 返回的数字没有意义，exists子句不在乎返回什么，而是在乎有没有结果返回。

而exists与in最大的区别在于in引导的子句只能返回一个字段，比如：

SELECT * FROM AM_USER WHERE USERNAME IN (SELECT 1,2,3 from AM_ROLE WHERE NAME = 'admin') 

in子句返回了三个字段，这是不正确的，但exists允许。而in只允许有一个字段返回，在1,2,3中随意取出两个即可。

而not exists和not in分别是exists和in的对立面

exists返回结果集为真
not exists 不返回结果集为真

举个例子，现在有两个表

表A

ID	NAME
1	A1
2	A2
3	A3

 

表B

ID	AID	NAME
1	1	B1
2	2	B2
3	2	B3

 

表A和表B是一对多的关系A.ID => B.AID

执行语句

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

执行结果为

ID	NAME
1	A1
2	A2

 

原因如下分析

原因可以按照如下分析

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

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

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

如果是not exists则反过来

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

执行结果为

ID	NAME
3	A3

 

有时候会遇到要选出某一列不重复，某一列作为选择条件的情况，比如表food:

ID	NAME	CLASS	DATE
1	苹果	水果	2020-10-14
1	橘子	水果	2020-10-15
1	香蕉	水果	2020-10-16
2	白菜	蔬菜	2020-10-14
2	青菜	蔬菜	2020-10-15

如果想要得到如下结果：（ID唯一，DATE选最近的一次）

ID	NAME	CLASS	DATE
1	香蕉	水果	2020-10-16
2	青菜	蔬菜	2020-10-15

这里不能使用distinct，因为如下使用sql语句，会作用于所有字段，会查出来所有数据

SELECT DISTINCT ID, NAME, CLASS, DATE FROM food

正确的sql语句是：

SELECT
ID,NAME,CLASS,DATE 
FROM
food f
WHERE 
NOT EXISTS ( SELECT ID, NAME, CLASS, DATE FROM food WHERE ID = f.ID AND DATE > f.DATE )