5.3.4 嵌套查询

在SQL中，一个SELECT…FROM…WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE条件中的查询称为嵌套查询。处于内层的查询称为子查询。执行嵌套查询语句时，每个子查询在上一级查询处理之前求解，也就是从里向外查询，先由子查询得到一组值的集合，外查询再从这个集合中得到新的查询条件的结果集。嵌套查询的语句格式为：

SELECT sname /*外层查询/父查询*/

FROM student

WHERE sno IN

（SELECT sno /*内层查询/子查询*/

FROM enroll

WHERE cno= 'c2'）

SQL允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要注意的是，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果进行排序。嵌套查询分为相关子查询和不相关子查询。相关子查询是指子查询的查询条件依赖于父查询，其操作过程为：先取外层查询中表的第一个元组，再根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表，然后再取外层查询中表的下一个元组。重复这一过程，直至外层表全部检查完为止。不相关子查询是指子查询的查询条件不依赖于父查询，其操作过程为：由里向外逐层处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

1. 带有IN谓词的子查询

这种子查询属于不相关子查询。子查询的结果作为外查询的条件，它是嵌套查询中用得最多的谓词。

【例5-40】查询与“胡峰”在同一个系学习的学生，使用IN谓词写出相关语句。

解：SELECT sno, sname,department

FROM student

WHERE department IN

(SELECT department FROM student WHERE sname='胡峰')

【例5-41】查询成绩在80分以下的学生名单，写出相关语句。

解：SELECT * FROM student WHERE sno

IN(SELECT sno FROM enroll WHERE grade＜80)

结果如图5-39所示。

【例5-42】查询选修了课程名为“数据库”的学生学号和姓名，写出相关语句。

解：SELECT sno,sname /*最后根据学生学号在student表中找出对应的学号和姓名*/

FROM student

WHERE sno IN

(SELECT sno /*然后在enroll表中找出c1号课程所对应的学生学号*/

FROM enroll

WHERE cno IN

(SELECT cno /*首先在course表中找出“数据库”所对应的课程号c1*/

FROM course

WHERE cname='数据库'))

结果如图5-40所示。

以上用IN谓词连接的嵌套查询，也可以用连接查询代替。但是要注意的是，有些嵌套查询可以用连接查询代替，有些是不能代替的。在进行查询时，究竟何时用连接查询，何时用嵌套查询，并不是绝对的，可以根据需要确定。

2. 带有比较运算符的子查询（＜,＞,=…）

当能确切知道内层查询返回单值时，可用比较运算符（＞、＜、=、＞=、＜=、!=或＜ ＞）连接父查询和子查询，并且可以与ANY或ALL谓词配合使用。

【例5-43】查询与“胡峰”在同一个系学习的学生，用比较运算符写出相关语句。

解：假设一个学生只可能在一个系学习，并且必须属于一个系，则可以用“=” 代替IN。

SELECT sno, sname,department

FROM student

WHERE department =

(SELECT department

FROM student

WHERE sname='胡峰')

需要注意的是，子查询必须跟在比较运算符之后。

【例5-44】找出每个学生超过他选修课程平均成绩的课程号，写出相关语句。

解：SELECT sno, cno

FROM enroll x

WHERE grade ＞=(SELECT AVG(grade)

FROM enroll y

WHERE y.sno=x.sno)

结果如图5-41所示。

该例可能的执行过程如下：

(1) 从外层查询中取出enroll的一个元组x，将元组x的sno值（95001）传送给内层查询，语句如下：

SELECT AVG(grade)

FROM enroll y

WHERE y.sno='95001'

(2) 执行内层查询，得到值88.8（近似值），用该值代替内层查询，得到外层查询，语句如下：

SELECT sno, cno

FROM enroll x

WHERE grade ＞=88.8

(3) 执行这个查询，得到如图5-42所示的结果。

(4) 外层查询取出下一个元组重复上述(1) 至(3) 步，直到外层的enroll元组全部处理完毕。

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

ANY（SOME）或ALL谓词必须和比较运算符配合使用。其一般格式为

＜标量表达式＞＜比较运算符＞ ANY︱ALL＜子查询＞

其中，ANY表示任意值，ALL表示所有值。所以，

＞ ANY 大于子查询结果中的某个值

＞ ALL 大于子查询结果中的所有值

此外，“= ANY”等价于IN，而“＜＞ANY”等价于NOT IN。早期的SQL版本中无SOME，而是ANY。

【例5-45】查询其他系中比计算机系某一学生年龄小的学生姓名和年龄，并按照年龄降序进行排列，写出相关语句。

解：SELECT sname, age

FROM student

WHERE age ＜ ANY

(SELECT age

FROM student

WHERE department= '计算机')

AND department ＜＞ '计算机'

ORDER BY age DESC

结果如图5-43所示。

此语句执行过程如下：

(1) RDBMS执行此查询时，先处理子查询，再找出计算机系中所有学生的年龄，构成一个集合(20，22)。

(2) 处理父查询，找出所有不是计算机系且年龄小于20岁或22岁的学生。

例5-45也可以用集合函数实现，实现语句如下：

SELECT sname, age

FROM student

WHERE age ＜

(SELECT MAX(age)

FROM student

WHERE department= '计算机')

AND department ＜＞ '计算机'

ORDER BY age DESC

4. 带有EXISTS谓词的子查询

EXISTS谓词表示存在量词（∃）。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果为非空，则外层的WHERE子句返回真值；若内层查询结果为空，则外层的WHERE子句返回假值。当内层查询返回真值时，取外层查询的该元组值作为结果值；反之，该元组值不可以作为结果值；由EXISTS谓词引出的子查询，其目标列表达式通常用*，因为带EXISTS谓词的子查询只返回真值或假值，给出列名无实际意义。

带有NOT EXISTS谓词的子查询，若内层查询结果为非空，则外层的WHERE子句返回假值；若内层查询结果为空，则外层的WHERE子句返回真值。

【例5-46】查询所有选修了c1号课程的学生姓名，写出相关语句。

解：SELECT sname

FROM student

WHERE EXISTS

(SELECT *

FROM enroll

WHERE sno=student.sno AND cno= 'c1')

结果如图5-44所示。

本查询涉及student和enroll关系，在student中依次取每个元组的sno值，用此值检查enroll关系，若enroll中存在这样的元组，其sno值等于此student.sno值，并且其cno=‘c1’，则取此student.sname送入结果关系。

若查询没有选修c1号课程的学生姓名，则可以用NOT EXISTS表示，查询语句如下：

SELECT sname

FROM student

WHERE NOT EXISTS

(SELECT *

FROM enroll

WHERE sno=student.sno AND cno= 'c1')

SQL中没有全称量词，可以把带有全称量词的谓词转换为等价的带有存在量词的谓词。

【例5-47】查询选修了全部课程的学生姓名，写出相关语句。

解：SELECT sname /*该生一定选修了所有的课程*/

FROM student

WHERE NOT EXISTS /*不存在该生没有选的课程*/

( SELECT * /*所有的课程该生都没有选*/

FROM course

WHERE NOT EXISTS /*不存在某学生选某课*/

(SELECT * /*某学生选某课*/

FROM enroll

WHERE sno=student.sno AND

cno=course.cno))

结果如图5-45所示。

因为SQL中没有全称量词，所以该题通过找这样的学生，没有一门课程是他不选的，来找到这个学生选修了所有的课程。

需要注意的是，一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换。而所有带IN谓词、比较运算符、ANY和ALL谓词的子查询，都能用带EXISTS谓词的子查询等价替换。