嵌套查询

三、嵌套查询

在 SQL 语言中, 一个select-from-where 语句称为一个查询块,将一个查询块嵌套在另一个和查询块的 where 子句或者 having 短语的条件中的查询称为 嵌套查询

例如:

select Sname              /*外层查询或父查询*/
from Student
where Sno in
		(select Sno       /*内层查询或子查询*/
		from SC
		where Cno='2');

本例中, 下层查询块 select Sno from SC where Cno=‘2’ 是嵌套在上层查询块 select Sname from Student where Sno in 的 where 条件中的
上层的查询块称为外层查询或父查询,下层查询块称为内层查询或子查询

SQL 语言允许多层嵌套查询, 即一个子查询还可以嵌套其他子查询,需要特别注意的是
子查询的 select 语句中不能使用 order by 子句, order by子句只能对最终查询结果排序

嵌套查询使用户可以用多个简单查询构成复杂的查询,从而增强 SQL 的查询能力,以层层嵌套的方式来构造程序正是 SQL 中 “结构化” 的含义所在

1. 带有 in 谓词的子查询

在嵌套查询中, 子查询的结果往往是一个集合,所以谓词 in 是嵌套查询中最经常使用的谓词

例1: 查询与 “李四” 在同一个系学习的学生

先分步来完成此查询, 然后再构造嵌套查询

1. 确定 “李四” 所在的系名

select Sdept
from Student 
where Sname='李四'

2. 查找所有再 CS 系学习的学生

select Sno,Sname,Sdept
from Student
where Sdept='CS'

结果为

    Sno      Sname      Sdept
--------------------------------
201215121     李四        CS
201215122     刘六        CS

将第一步查询嵌入到第二步查询的条件中, 构造嵌套查询如下:

select Sno,Sname,Sdept
from Student
where Sdept in
	(select Sdept
	 from Student
	 where Sname='李四');

本例中, 子查询的查询条件不依赖于父查询, 称为不相关子查询

一种求解方法是由里向外处理,即先执行子查询,子查询的结果用于建立其父查询的查找条件得到如下的语句:

select Sno,Sname,Sdept
from Student
where Sdept in('CS')

然后执行该语句

本例中的查询也可以用自身连接来完成:

select S1.Sno,S1.Sname,S1.Sdept
from Student S1, Student S2
where S1.Sdept=S2.Sdept and S2.Sname='李四'

还能用带 exists 谓词的子查询来完成（下方有介绍）

可见,实现一个查询请求有多种方法,当然不同的方法其执行效率可能会有差别,甚至会差别很大,这就是数据库编程人员应该掌握的数据库性能调优技术

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

本查询涉及学号,姓名和课程名三个属性,学号和姓名存放再 Student 表中,课程名存放在 Course 表中,但 Student 与 Course 两个表之间没有直接联系, 必须通过 SC 表建立它们二者直接按的联系,所以此查询实际上涉及三个关系

select Sno,Sname                        // 3. 最后在 Student 关系中取出 Sno 和 Sname
from Student
where Sno in
	 (select Sno                        // 2. 然后在 SC 关系中找出选修了3号课程的学生学号
	  from SC
	  where Cno in
	 	 (select Cno                    // 1. 首先在 Course 关系中找出'信息系统'的课程号,结果为3号
	 	  from Course
	 	  where Cname='信息系统'
	 	 )
	  );

本例可以用连接查询实现:

select Student.Sno,Sname
from Student,SC,Course
where Student.Sno=SC.Sno and
		SC.Cno=Course.Cno and
		Course.Cname='信息系统';

有些嵌套查询可以用连接运算替代, 有些是不能替代的
从上面两个 SQL 语句中可以看到, 查询涉及多个关系时,用嵌套查询局部求解层次清楚,易于构造,具有结构化程序设计的优点,
相比于连接运算,目前商用关系数据库管理系统对嵌套查询的优化做的还不够完善,所以在实际应用中,能用用连接运算表达的查询尽可能采用连接运算

以上两个 SQL 语句中的子查询的查询条件不依赖于父查询, 这类子查询称为不相关子查询, 不相关子查询时较简单的一类子查询
如果子查询的查询条件依赖于父查询,这类子查询称为相关子查询,整个查询语句称为相关嵌套查询语句

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

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接,当用户能确切知道内层查询返回的是单个值时,可以用 >, < , = , >= , <= , != , 或<> 等比较运算符

• 例1 : 查询与 '李四 ’ 在同一个系的学生

由于一个学生只能在一个系学习,也就是说内查询的结果是一个值,因此可以用 = 代替 in

select Sno,Sname,Sdept
from Student
where Sdept=
	(select Sdept
     from Student
     where Sname='李四');

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

select Sno,Cno
from SC x
where Grade>=(select avg(Grade)    //某学生的平均成绩
              from SC y
              where y.Sno=x.Sno);

x 是表 SC 的别名, 又称为元组变量,可以用来表示 SC 的一个元组,
内层查询时求一个学生所有选修课程平均成绩的, 至于是那个学生的平均成绩要看参数 x.Sno 的值, 而该值是和父查询相关的,因此这类查询被称为相关子查询

这个语句的一种可能的执行过程采用以下三个步骤:

// 1. 从外层查询中取出 SC 的一个元组 x, 将元组 x 的 Sno 值(201215121) 传送给内层查询
select avg(Grade)
form SC y
where y.Sno='201215121'

// 2. 执行内层查询,得到值 88(平均成绩的近似值), 用该值代替内层查询, 得到外层查询
select Sno,Cno
from SC x
where Grade>=88

// 3. 执行这个查询,得到
select Sno,Cno
from SC x
where Grade>=88;

然后从外层查询取出下一个元组重复上述 1 ~ 3 步骤的处理, 知道外层的 SC 元组全部处理完毕,结果为

// 学号 和 课程号
(201215121,1)
(201215121,3)
(201215122,2)

求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来,然后求解父查询, 内层查询由于与外层查询有关,因此必须 反复求值

3. 带有 any (some) 或 all 谓词的子查询

子查询返回单值时可以用比较运算符,但返回多值时要用 any (有的系统用some) 或 all 谓词修饰符
而使用 any 或 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'

// Sage<any    Sdept<>'CS'   这时父查询块的条件

-- 结果如下
     Sname      Sage
---------------------------
	 王明        18
	 张力        19  

关系型数据库管理系统执行此查询时,首先处理子查询,找出 CS 系中所有学生的年龄,构成以恶搞集合 (20,19)
然后处理父查询,找所有不是 CS 系且年龄小于 20 或者 19 的学生

本查询也可以用聚集函数来实现,首先用子查询找出 CS 系中最大年龄 (20), 然后在父查询中查所有非 CS 系且年龄小于 20 岁的学生

select Sname,Sage
from Student
where Sage<
		(select max(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'

-- 查询结果
    Sname   Sage
--------------------
     王明     18

关系数据库管理系统执行此查询时,首先处理子查询,找出 CS 系中所有学生的年龄,构成一i个集合 (20,19)
然后处理父查询,找所有不是 CS 系且年龄既小于 20 ,也小于 19 的学生

本查询同样也可以用聚集函数实现

select Sname,Sage
from Student 
where Sage<
		(select min(Sage)
		 from Student
		 where Sdept='CS')
	and Sdept<>'CS'

事实上,用聚集函数实现子查询通常比直接用 any 或 all 查询效率要高

any (或aome), all 谓词与聚集函数, in 谓词的等价转换关系

	–	<> 或 !=	<	<=	>	>=
any	in	–	<max	<=max	>min	>=min
all	–	not in	<min	<=min	>max	>=max

= any 等价于 in 谓词, <any 等价于 <max ,<>all 等价于 not in 谓词, <all 等价于 <min

4. 带有 exists 谓词的子查询

带有 exists 谓词的子查询不返回任何数据，只产生逻辑真值 “true” 和逻辑假值 “false”

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

本查询涉及 Student 表和 SC 表，可以在 Student 中依次取每个元组的 Sno 值，用此值去检查 SC 表，若 SC 中存在这样的元组，其 Sno 值等于此 Student.Sno 值，并且其 Cno=‘1’，则取此 Student.Sname 送入结果表

select Sname
from Student
where exists
	(select *
	 from SC
	 where Sno=Student.Sno and Cno='1');

使用存在量词 exists 后，若内层查询结果为非空，则外层的 where 子句返回真值，否则返回假值
由 exists 引出的子查询，其目标列表达式通常都用 * ，因为带 exists 的子查询只返回真值或假值，给出列名无实际意义

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

• 例2 ： 查询没有选修1号课程的学生姓名

select Sname
from Student
where not exists
	(select*
	 from SC
	 where Sno=Student.Sno and Cno='1');

一些带 exists 或 not exists 谓词的子查询不能被其他形式的子查询等价替换，但所有带 in 谓词，比较运算符，any 和 all 谓词的子查询都能用带 exists 谓词的子查询等价替换

 // 查询与 "李四"  在同一个系学习的学生
 select Sno,Sname,Sdept
 from Student S1
 where exists
 	(select*
 	 from Student S2
 	 where S2.Sdept=S1.Sdept and
 	 	S2.Sname='李四');

由于带 exists 量词的相关子查询只关心内层查询是否有返回值，并不需要差具体值，因此其效率并不一定低于不相关子查询，有时是高效的方法

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

sql没有全称量词 （for all），但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式， 查询这样的学生：没有一门课程是他不选修的
从而用 exist / not exist 来实现带全称量词的查询

select Sname
from Student
where not exists
	(select*
	 from Course
	 where not exists
	 	(select*
	 	 from SC
	 	 where Sno=Student.Sno
	 	 	and Cno=Course.Cno));

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

本查询可以用逻辑蕴涵来表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y，形式化表示如下：

它的表达的语义为：不存在这样的课程y，学生201215122选修了y，而学生x没有选

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));