【MySQL】详解嵌套查询（相关、非相关子查询）与集合查询（交集、并集、差集）【附源码】

一、嵌套查询

在SQL语言中，一个select…from…where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where字句或having短语的条件中的查询块称为嵌套查询（nested query）。
例如：

select sno,sname,sdept from student where sdept in (
select sdept from student where sname="刘晨");

上层的查询块称为外层查询或父查询，下层的查询块称为内层查询或子查询。
注意：

子查询的注意事项：
① SQL允许多层子查询，即一个子查询还可以嵌套其他子查询；
② 子查询的select子句不能使用order by子句，order by子句只对最终结果进行排序；
③ 子查询根据子查询的查询条件是否依赖于父查询分为相关子查询和不相关子查询。

1.带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所以使用谓词in来取值。
例1：查询与“刘晨”在同一个系学习的学生
解法1：使用IN谓词的子查询

select sno,sname,sdept from student where sdept in (
select sdept from student where sname="刘晨");

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询；如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询语句。
子查询中先执行子查询，子查询的结果用于建立父查询的查找条件，由内向外。

解法2：使用自连接查询

select s1.sno,s1.sname,s1.sdept from student s1,student s2 where 
s1.sdept=s2.sdept and s2.sname="刘晨";

例2：查询选修了课程名为“信息系统”的学生学号和姓名
解法1：使用in谓词的子查询

select sno,sname from student where sno in (
select sno from sc where cno in (
select cno from course where cname="信息系统"));

解法2：使用连接查询

select s.sno,sname from student s,course c,sc x where 
s.sno=x.sno and c.cno=x.cno and c.cname="信息系统";

2.带有比较运算符的子查询

带有比较运算符的子查询是指父查询和子查询之间使用比较运算符进行连接。
（只适用于子查询返回单个值时使用，可以使用>、<、=、>=、<=、!=或<>等比较运算符；当明确知道子查询的返回结果只有一个时，可以使用比较运算符替换in谓词使用）
例1：查询与“刘晨”在同一个系学习的学生
解法3：使用比较运算符

select sno,sname,sdept from student where sdept = (
select sdept from student where sname="刘晨");

例2：查询每个学生超过他自己选修课程平均成绩的课程号

select sno,cno from sc x where x.grade >= (
select avg(grade) from sc y where x.sno = y.sno);

在本例中，子查询的值与父查询是相关的，是相关子查询。

3.带有ANY（SOME）或ALL谓词的子查询

子查询返回单值是可以使用比较运算符，但返回多值时要用ANY（SOME）或ALL谓词修饰符，使用ANY（SOME）或ALL谓词时必须同时使用比较运算符。

>  ANY 	大于子查询结果中的某个值
>  ALL  大于子查询结果中的所有值
<  ANY 	小于子查询结果中的某个值
<  ALL 	小于子查询结果中的所有值
>= ANY  大于等于子查询结果中的某个值
>= ALL  大于等于子查询结果中的所有值
<= ANY 	小于等于子查询结果中的某个值
<= ALL 	小于等于子查询结果中的所有值
=  ANY 	等于子查询结果中的某个值
=  ALL 	等于子查询结果中的所有值（通常没有实际意义）
!=(或<>) ANY 不等于子查询结果中的某个值
!=(或<>) ALL 不等于子查询结果中的任何一个值

例1：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生的姓名和年龄

select sname,sage from student where sage< any (
select sage from student where sdept="CS") and sdept<>"CS";

例2：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄

select sname,sage from student where sage < all (
select sage from student where sdept="CS") and sdept<>"CS";

4.带有EXISTS谓词的子查询

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑值“true”或逻辑值“false”。
（可以使用EXISTS来判断x属于S、S包含于R、S=R、S和R相交是否为空等成立）
例1：查询所有选修了2号课程的学生姓名

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

例2：查询与“刘晨”在同一个系学习的学生
解法4：使用exists谓词

select sno,sname,sdept from student x where exists (
select * from student y where sname = "刘晨" and x.sdept=y.sdept);

与EXISTS相对的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的where字句返回真值，否则返回假值。
例1：查询没有选修1号课程的学生姓名

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

例2：查询选修了全部课程的学生姓名
（问题==>没有一门课程是他不选修的）

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

例3：查询至少选修了学生201215122选修的全部课程的的学生学号
（问题==>不存在这样的课程y，学生201215122选修了y，而学生没有选）

select distinct sno from sc x where not exists (
select * from sc y where x.sno=201215122 and not exists (
select * from sc z where z.sno=x.sno and z.cno=y.cno));

二、集合查询

select 语句的查询结果是元组的集合，所以多个select语句的结果可以进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

1.UNION并集

UNION参与集合运算时会自动去重，若想输出所有的查询结果，则使用union all.
例1：查询计算机科学系的学生以及年龄小于19岁的学生

select * from student where sdept="CS" union select * from student where sage<19;

例2：查询选修了1号或2号课程的学生
解法1：使用union

select sno from sc where cno=1 union all select sno from sc where cno=2;

解法2：使用in谓词

select distinct sno from sc where cno in (1,2);

解法3：使用or

select distinct sno from sc where cno=1 or cno=2;

2.INTERSECT 交集

（mysql不支持交集和差集，oracle是支持的）
例1：查询计算机科学系中年龄不大于19岁的学生
解法1：使用交集（了解格式即可，mysql不支持，oracle支持）

select * from student where sage<=19 intersect select * from student where sdept="CS";

解法2：使用条件查询

select * from student where sdept="CS" and sage<=19;

例2：查询既选修了1号，又选修了2号课程的学生
解法1：使用交集（了解即可，mysql不支持，oracle支持）

select sno from sc where cno=1 intersect select sno from sc where cno=2;

解法2：使用子查询

select sno from sc where cno=1 and sno in (select sno from sc where cno=2);

3.EXCEPT 差集

（mysql不支持交集和差集，oracle是支持的）
例：查询计算机科学系中年龄大于19岁的学生
解法1：使用差集（了解即可，mysql不支持，oracle支持）

select * from student where sdept="CS" except select * from student where sage<=9;

解法2：使用and

select * from student where sage>19 and sdept="CS";

三、使用的数据表

1.学生表

sno（学号）、sname（姓名）、ssex（性别）、sdept（所在系）

mysql> select * from student;
+-----------+--------+------+------+-------+
| sno       | sname  | ssex | sage | sdept |
+-----------+--------+------+------+-------+
| 201215121 | 李勇   | 男   |   20 | CS    |
| 201215122 | 刘晨   | 女   |   19 | CS    |
| 201215123 | 王敏   | 女   |   18 | MA    |
| 201215125 | 张立   | 男   |   19 | IS    |
+-----------+--------+------+------+-------+

2.课程表

cno（课程号）、cname（课程名）、cpno（先修课）、ccredit（学分）

mysql> select * from course;
+-----+--------------+------+---------+
| cno | cname        | cpno | ccredit |
+-----+--------------+------+---------+
|   1 | 数据库       |    5 |       4 |
|   2 | 数学         | NULL |       2 |
|   3 | 信息系统     |    1 |       4 |
|   4 | 操作系统     |    6 |       3 |
|   5 | 数据结构     |    7 |       4 |
|   6 | 数据处理     | NULL |       2 |
|   7 | pascal语言   |    6 |       4 |
+-----+--------------+------+---------+

3.学生选课表

sno（学号）、cno（课程号）、grade（成绩）

mysql> select * from sc;
+-----------+-----+-------+
| sno       | cno | grade |
+-----------+-----+-------+
| 201215121 |   1 |    92 |
| 201215121 |   2 |    85 |
| 201215121 |   3 |    88 |
| 201215122 |   2 |    90 |
| 201215122 |   3 |    80 |
+-----------+-----+-------+

四、创建测试表文件

drop table if exists student;
#创建student
create table student(
sno bigint primary key not null auto_increment,
sname varchar(15),
ssex char(5),
sage int,adept char(10)
);
#向student中添加数据
insert into student value(201215121,"李勇","男",20,"CS");
insert into student value(201215122,"刘晨","女",19,"CS");
insert into student value(201215123,"王敏","女",18,"MA");
insert into student value(201215125,"张立","男",19,"IS");

drop table if exists course;
#创建课程表
create table course(
cno int primary key not null auto_increment,
cname varchar(20),
cpno int,
ccredit int
);
#向课程表中添加数据
insert into course value(1,"数据库",5,4);
insert into course value (2,"数学",null,2);
insert into course value(3,"信息系统",1,4);
insert into course value(4,"操作系统",6,3);
insert into course value(5,"数据结构",7,4);
insert into course value(6,"数据处理",null,2);
insert into course value(7,"pascal语言",6,4);

drop table if exists sc;
#创建选课表
create table sc(
sno bigint not null, 
cno int not null,
grade  int,
primary key(sno,cno)
);
#向选课表中添加数据
insert into sc value(201215121,1,92);
insert into sc value(201215121,2,85);
insert into sc value(201215121,3,88);
insert into sc value(201215122,2,90);
insert into sc value(201215122,3,80);