带有(NOT) EXISTS谓词的子查询
1. 基本知识介绍
1. EXISTS代表存在量词
-
带有EXISTS谓词的子查询不反回任何数据,值产生逻辑真值**“true”**或逻辑假值 “false”
使用EXISTS查询后
- 若内存查询结果非空,则外层的WHERE子句返回真值,否则返回假值
- 由EXISTS引出的子查询,其目标列表达式通常都用* ,因为带EXISTS的子查询只返回真值或假值,给出列名没有意义
-
例题
//查询所有选修了 1号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS (SELECT*
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
2. NOT EXISTS
- 与EXIST谓词对应的是NOT EXISTS谓词,使用存在量词NOT EXISTS后,若内层查询结果为空,则外层的WHERE子句返回真值,否则返回假值
- 例题
//查询没有选修1号课程的学生姓名
SELECT Sname
FROM Student
WHER NOT EXISTS(SELECT*
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
2. 进阶
-
一些带有EXISTS或者NOT EXISTS谓词的子查询,不可以被其他形式的子查询等价代替,但是所有带有IN谓词、比较运算符、ANY或ALL谓词的子查询都可以用带有EXISTS谓词的子查询等价替换;
-
例题
//查询和“刘成”在同一系学习的学生
//法1 用IN
SELECT Sname ,Sno,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname = '刘成');
//法二 用EXISTS
SELECT Sname,Sno,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Snmae='刘成');
3. 查询选修了所有课程的学生姓名 重点例题详解
//查询选修了所有课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT*
FROM Course
WHERE NOT EXISTS
(SELECT*
FROM SC
WHERE Sno = Student.Sno AND Cno = Course.Cno));
/*解释:
我们来先讨论以下 SELECT * FROM Table WHERE ...语句的实现
1. 选择FROM后面的表格Table,轮流Table中的每条数据,相当于指针的指向
2. 指针牵着第一条数据进入WHERE的语句中,进行筛选,如果满足条件,这个数据就被选了出来
3. 第一条筛选结束,指针向下移,指向第二条数据;指针再牵着第二条数据进入WHERE中筛选,如果满足条件,这个数据就被选了出来
4. 一直这样操作,直至指针牵完Table中的数据
我们再来解释一下这个题,它涉及三个表Student,SC,Course
以下是其操作流程
1. 小1指针指向Student表中的第一条数据
小2指针指向Course表中的第一条数据
小3指针指向SC表中的第一条数据
小指针们准备就绪...
2. 假如有个人叫张三,他将Couse表中的所有课都选了
小1指针正指着Student表中这个张三的数据
小2指针依旧指向Couse表中的第一个数据
小3指针指向SC表中的第一条数据
小3指针牵着第一条数据,依照 Sno = Student.Sno AND Cno = Course.Cno 这个筛选条件,进行筛选;
将SC表遍历完之后,肯定会有一个非空的值,返回到最内层的SELECT表中,因为肯定会在SC表中找到小2所指的课程,且这个人叫张三,但是在返回的时候遇到 NOT EXISTS谓词,因为非空,则 NOT EXISTS一定返回 false,即淘汰小2指针所指的这个课程
小2再指向下一个课,小3再遍历,重复以上,直至小2将表中的课全遍历完毕....
经过以上的循环,最终经过第二个 NOT EXISTS ,第二句中的 SELECT 并没有选出一个数据,因为所有的课程都被淘汰了,所以为空
则第二层的*/
SELECT*
FROM Course
WHERE NOT EXISTS
//等价于
SELECT*
FROM Course
WHERE false
/*则啥都不选,返回一个空表,而在出最外层括号时,又见 NOT EXISTS 返回 true;所以就成功筛选小1指针所指的张三
因为张三全选了Course中的课,所以肯定回建立一个非空的表,返回到最内层的SELECT表中
3. 再假如有个人叫做李四,他只选了Course表中的两门课,总共有5门课程,他选了前两门
有了张三的基础,我们很容易得出:
1. 李四选的两门课被最里面的 NOT EXISTS给淘汰掉了,
2. 而其它三门,由于SC表中没有记录,当再出最里边的 NOT EXISTS 时候,SELECT返回的是空,则最里边的 NOT EXISTS返回 true,即李四没有选的课被中间的 SELECT 语句给选了出来,
3. 遇到最外层的 NOT EXISTS时,由于中间的 SELECT选出来了3个记录,即为非空表,则外边的 NOT EXISTS返回 false,淘汰指针1所指的李四*/