关于SQL中的EXISITS和NOTEXISTS详细理解

sql 子查询 exists 和 not exists

mysql exists 和 not exists 子查询语法如下:

select … from table where exists (subquery)

该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(true 或 false)来决定主查询的数据结果是否得以保留。

mysql exists 子查询实例

下面以实际的例子来理解 exists 子查询。下面是原始的数据表:

article 文章表:

aid title content uid
1 文章1 文章1正文内容… 1
2 文章2 文章2正文内容… 1
3 文章3 文章3正文内容… 2
4 文章4 文章4正文内容… 4

user 用户表:

uid title content
1 admin admin@5idev.com
2 小明 xiao@163.com
3 jack jack@gmail.com

我们要查出 article 表中的数据,但要求 uid 必须在 user 表中存在。sql 语句如下:

select * from article where exists (select * from user where article.uid = user.uid)

返回查询结果如下:

aid title content uid
1 文章1 文章1正文内容… 1
2 文章2 文章2正文内容… 1
3 文章3 文章3正文内容… 2

从语句执行结果可以看出,article 表中第 4 条记录没有被保留,原因就是该条记录的数据在子查询中返回的结果是 false 。

当上面的 sql 使用 not exists 时,查询的结果就是 article 表中 uid 不存在于 user 表中的数据记录。

下面来三张表的实例

我们先介绍下使用的3个数据表:

student数据表:

sno 学号 sname ssex sage
20161181 altair 20
20161182 desmond 18
20161183 ezio 22
20161184 christina 19

course数据表:

cno 课程编号 cname 课程名
1 c语言
2 数据结构
3 信号与
4 模拟电子技术
5 高数

sc数据表:

sno 学号 cno 课程编号 grade 成绩
20161181 1 99
20161182 2 98
20161181 2 97
20161181 3 95
20161184 3 92
20161181 4 90
20161181 5 88
20161183 5 58

exists

exists代表存在量词。带有exists谓词的子查询不返回任何数据,只产生逻辑真值“true”或者逻辑假值“false”。

一个例子1.1:

要求:查询选修了课程”信号与系统“的同学

select s.sname from student s
where exists  
(select * from sc, course c where sc.sno = s.sno and sc.cno = c.cno and c.cname = '信号与系统')

使用存在量词exists后,若内层查询结果为非空,则外层的where子句返回值为真,否则返回值为假。

在本例中,首先分析最内层的语句:

select * from sc, course c where sc.sno = s.sno and sc.cno = c.cno and c.cname = '信号与系统'

本例中的子查询的查询条件依赖于外层父查询的某个属性值(本例中的是student的sno值),这个相关子查询的处理过程是:

首先取外层查询中(student)表的第一个元组,根据它与内层查询相关的属性值(sno值)处理内层查询,若外层的where返回为真,则取外层查询中该元组的sname放入结果表;

然后再取(student)表的下一组,重复这一过程,直至外层(student)表全部检查完毕。

查询结果表:

sname
altair
christina

not exists

与exists谓词相对的是not exists谓词。使用存在量词not exists后,若对应查询结果为空,则外层的where子语句返回值为真值,否则返回假值。

例子2.1:

要求:查询没有选修课程”信号与系统“的同学

select s.sname from student s
where not exists  
(select * from sc, course c where sc.sno = s.sno and sc.cno = c.cno and c.cname = '信号与系统')

使用not exists之后,若内层查询结果为非空,则对应的not exists不成立,所以对应的where语句也不成立。

在例子1.1中李勇同学对应的记录符合内层的select语句的,所以返回该记录数据,但是对应的not exists不成立,where语句也不成立,表示这不是我们要查询的数据。

查询结果表:

sname
desmond
ezio

例子2.2(这是一个用not exists表示全称量词的例子):

要求:查询选修了全部课程的学生姓名。

sql语句:

select sname  
from student   
where not exists  
(select * from course where not exists  
     (select * from sc where sno=student.sno and cno=course.cno)  
);  

这个算是一个比较复杂的sql语句了,两个exists和三个where。

这个sql语句可以分为3层,最外层语句,最内层语句,中间层语句。

我们很关心最外层语句,因为结果表中的数据都是最外层的查询的表中的数据,我们更关心最内层的数据,因为最内层的数据包含了全部的判断语句,决定了student表中的那一条记录是我们查询的记录。

我们由内而外进行分析:

最外层的student表中的第一条记录是altair同学对应的记录,然后中间层的course表的第一条记录是对应的记录,然后对该数据进行判断(最内层的where语句),结果返回真,则内层的not exists为假,

然后继续对course表中的下一条记录进行判断,返现not exists的值也为假,直到遍历完course表中的所有的数据,内层的not exists的值一直都是假,所以中间层的where语句的值也一直都是假。

对应student的altair记录,course表中的所有的记录对应的中间层的返回值为假,所以最外层的not exists对应的值为真,最外层的where的值也为真,则altair对应的记录符合查询条件,装入结果表中。

然后继续对student表中的下一条记录进行判断,直达student表中的所有数据都遍历完毕。

下面是我自己对这段sql的解读:

先取一条student记录,进入中层,再取一条course的记录,进入内层,此时student的记录和course的记录,作为内层判断的条件,比如此时我取的第一条记录是altair,那么我里面的sql就可以写成

select * from course where not exists  
     (select * from sc where sno = '20161181' and cno=course.cno)  
)

此处 sno 20161181即altair的学号,这条sql的意思是选出没有被altair选择的课程,如果不存在,则返回false,再跟最外层的not exists关联,负负得正。每一条循环的意思就是指,筛选出的每一个学生都不存在没有被他选取的那门课,即选了所有课。

最终查询结果:

sname
altair

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐