带谓词in的嵌套查询
select Sname,Dno
from Students
where Sno in
(select Sno
from Reports
where Cno = 'C02')
可接受多层嵌套。
这里查询的结果是Cno为’C02’的Sno的Sname和Dno。
其中,( )括号中的代码称为子查询。括号外的称为父查询或外层查询。
注意:子查询的select语句不可以使用order by子句。
带有比较运算符的嵌套查询
select Sno,Sname,Dno
from Students
where Dno =
(select Dno
from Students
where Sname = '小明')
这里子查询返回的是单个值。
因为这个比较简单,就不多做解释啦。
带谓词any或all的嵌套查询
使用谓词any或all时,必须同时使用比较运算符。
any或all与比较运算符联合使用的含义如下表:
谓词 | 注释 |
---|---|
> any | 大于子查询结果任何一个值 |
> all | 大于子查询结果最大值 |
< any | 小于子查询结果任何一个值 |
< all | 小于子查询结果最小值 |
<> any | 不等于子查询结果某个值 |
<> all | 不等于子查询结果任何一个值 |
还有其他的,>=,<=,=由于比较简单,这里不多做说明。
这里给出使用的例子:
select Sno,Sname,Ssex,Birthday,Dno
from Students
where Dno <> 'D01'
and year(Birthday) <= all
(select year(Birthday)
from Students
where Dno = 'D01')
查询结果为:Dno不等于’D01’的学生比Dno为’D01’的所有学生生日的年份小或者与Dno为‘D01’的学生生日年份最小的学生相等的学生的Sno,Sname,Ssex,Birthday和Dno。
本查询也可以用集函数来实现:
select Sno,Sname,Ssex,Birthday,Dno
from Students
where Dno <> 'D01'
and year(Birthday) <=
(select min(year(Birthday))
from Students
where Dno = 'D01')
一般来说,使用集函数实现子查询比直接用any或all查询效率要高。
下面给出谓词any、all与集函数、谓词in的等价对应关系
谓词any或all | 集函数或谓词in |
---|---|
> any | > min |
> all | > max |
< any | < max |
< all | < min |
>= any | >= min |
>= all | >= max |
<= any | <= max |
<= all | <= min |
= any | in |
=all | 没有 |
<> any | 没有 |
<> all | not in |
带谓词exists的嵌套查询(存在)
带谓词exists的子查询不返回任何数据,只产生逻辑值真(true)或假(false)
select Sname,Dno
from Students
where exists
(select *
from Reports
where Sno = Students.Sno and Cno = 'C01')
由exists引出的子查询,目标属性列表一般用“*”,因为带exists的子查询只返回逻辑值真假,不需要给出具体的列名。(exists一般用于存在,如果是任一的话,则使用两个not exists构成双重否定来实现。)
只要子查询结果非空,exists返回的就是true,否则为false。
(难点)not exists
比如现在要查询一个Sno(学生学号),他选修了所有的Cno(选修的课程号)
select Sname,Dno
from Students
where not exists
(select *
from Courses
where not exists
(select *
from Reports
where Sno = Students.Sno
and Cno = Courses.Cno))
该例子也可以改写为:
select Sname,Dno
from Students
where Sno in
(select Sno
from Reports
group by Sno
having count(*) =
(select count(*)
from Courses))