嵌套查询

嵌套查询概述

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

例:选修了2号课程的学生名字

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

FROM Student

WHERE Sno IN

(

SELECT Sno /*内层查询/子查询*/

FROM SC

WHERE Cno= '2'

）

子查询限制

不能使用ORDER BY子句，有些嵌套语句可以使用连接运算替代

几种嵌套查询

带有IN谓词的子查询(这是在集合中进行的一个查询，如果查询的是一个具体的值，那么可以在外面用比较运算符)

带有比较运算符的子查询

带有ANY（SOME）或ALL谓词的子查询(不可单独使用，因为和返回值相关)

带有EXISTS谓词的子查询

带有IN谓词的子查询

例 查询与“刘晨”在同一个系学习的学生，分步来完成

① 确定“刘晨”所在系名

SELECT Sdept

FROM Student

WHERE Sname= ' 刘晨 '；

结果为：CS

② 查找所有在CS系学习的学生

SELECT Sno，Sname，Sdept

FROM Student

WHERE Sdept= ' CS '；

将第一步查询嵌入到第二步查询的条件中

SELECT Sno，Sname，Sdept

FROM Student

WHERE Sdept IN

(

SELECT Sdept

FROM Student

WHERE Sname= ‘ 刘晨 ’

)

此查询为不相关子查询，DBMS求解该查询时也 是分步去做的

用自身连接完成例中查询要求

SELECT S1.Sno,S1.Sname,S1.Sdept

FROM Student S1,Student S2

WHERE S1.Sdept = S2.Sdept

AND S2.Sname = '刘晨'

例 查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname ③ 最后在Student关系中 取出Sno和Sname

FROM Student

WHERE Sno IN ② 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号

(

SELECT Sno

WHERE Cno IN

(

SELECT Cno ① 首先在Course关系中找出“信息系统”的课程号为3号

FROM Course

WHERE Cname= ‘信息系统’

)

)

用连接查询实现

SELECT Sno,Sname

FROM Student,SC,Course

WHERE Student.Sno=SC.Sno

AND SC.Cno=Course.Cno

AND Course.Cname=‘信息系统’

带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运 算符（>，<，=，>=，<=，!=或< >）

与ANY或ALL谓词配合使用

例 假设一个学生只可能在一个系学习,并且必须属于一个系,则在可以用 = 代替IN

SELECT Sno，Sname，Sdept

FROM Student

WHERE Sdept =

(

SELECT Sdept

FROM Student

WHERE Sname= ‘刘晨’

)

注意：子查询一定要跟在比较符之后

例 找出每个学生超过他选修课程平均成绩的课程号

SELECT Sno,Cno

FROM SC x

WHERE Grade >=

(

SELECT AVG(Grade)

FROM SC y

WHERE y.Sno=x.Sno

)

可能的执行过程：

1. 从外层查询中取出SC的一个元组x，将元组x的Sno值（200215121）传送给内层查询

SELECT AVG(Grade)

FROM SC y

WHERE y.Sno='200215121'

2. 执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询

SELECT Sno， Cno

FROM SC x

WHERE Grade >=88；

3. 执行这个查询，得到

（200215121，1）

（200215121，3）

4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。结果为:

（200215121，1）

（200215121，3）

（200215122，2）

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

谓词语义

ANY：任意一个值(有的用SOME)

ALL : 所有值

需要配合使用比较运算符

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

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

< ANY 小于子查询结果中的某个值

< ALL 小于子查询结果中的所有值

>= ANY 大于等于子查询结果中的某个值

>= ALL 大于等于子查询结果中的所有值

<= ANY 小于等于子查询结果中的某个值

<= ALL 小于等于子查询结果中的所有值

= ANY 等于子查询结果中的某个值

=ALL 等于子查询结果中的所有值（通常没有实际意义）

!=（或<>）ANY 不等于子查询结果中的某个值

!=（或<>）ALL 不等于子查询结果中的任何一个值

例 查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄

SELECT Sname，Sage

FROM Student

WHERE Sage < ANY

(

SELECT Sage

FROM Student

WHERE Sdept= ' CS '

)

AND Sdept <> ‘CS ' ; /*父查询块中的条件 */

执行过程

1.RDBMS执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20，19)

2. 处理父查询，找所有不是CS系且年龄小于20或19的学生

方法二：用聚集函数

SELECT Sname，Sage

FROM Student

WHERE Sage <

(

SELECT MIN(Sage)

FROM Student

WHERE Sdept= ' CS '

)

AND Sdept <>' CS ’

带有EXISTS谓词的子查询

1. EXISTS谓词

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

若内层查询结果非空，则外层的WHERE子句返回真值，若内层查询结果为空，则外层的WHERE

子句返回假值，由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只

返回真值或假值， 给出列名无实际意义

2. NOT EXISTS谓词

若内层查询结果非空，则外层的WHERE子句返回假值

若内层查询结果为空，则外层的WHERE子句返回真值

例 查询选修了1课程的学生姓名

SELECT Sname

FROM Student

WHERE EXISTS

(

SELECT *

FROM SC

WHERE Sno = Student.Sno

AND Cno ='1'

)

或

SELECT Sname

FROM Student

WHERE Sno IN

(

SELECT Sno

FROM SC

WHERE Cno ='1'

)

例 查询选修了全部课程的学生姓名

等价查询:查询这样的学生,没有一门课程是该生不选修的

SELECT Sname

FROM Student

WHERE NOT EXISTS ——逐一检查每门课程

(

SELECT *

FROM Course

WHERE NOT EXISTS ——检查该生是否修了该课程

(

SELECT *

FROM SC

WHERE Sno= Student.Sno

AND Cno= Course.Cno

）

)

例 查询至少选修了学生201215122选修的全部课程的学生号码

SELECT DISTINCT Sno

FROM SC SCX

WHERE NOT EXISTS

(

SELECT *

FROM SC SCY

WHERE SCY.Sno = ' 201215122 '

AND NOT EXISTS

(

SELECT *

FROM SC SCZ

WHERE SCZ.Sno=SCX.Sno

AND

SCZ.Cno=SCY.Cno

)

)