5.4.3 嵌套查询——子查询
子查询是嵌套在其他SQL语句中的SELECT语句，也称嵌套查询。一般情况，子查询出现在SELECT 语句的WHERE子句中，也可以出现在FROM子句和HAVING子句中。子查询使用的原则如下：
一个子查询必须用括号括起来。
一个子查询必须出现在运算符的右边。
子查询可以在许多SQL语句中使用，如SELECT、INSERT、UPDATE、DELETE语句中。
子查询中不能包含ORDER BY 子句。在一个SELECT语句中只能有一个ORDER BY子句，假如要指定ORDER BY子句，该ORDER BY子句必须是主查询语句中的最后一个子句。
5.4.3.1 不相关的子查询
查询条件不依赖于父查询的子查询称为不相关子查询。
不相关子查询的执行过程为：先执行子查询，子查询只执行一次，子查询的结果作为父查询的条件。子查询的查询条件不依赖父查询，子查询可以独立执行。
不相关子查询的特点如下：
子查询能独立运行，子查询的条件不依赖父查询。
子查询只运行一次。
先执行子查询，后执行父查询。
1.带有IN谓词的子查询
在嵌套查询中，如果子查询的结果是一个集合，则必须用IN谓词联结子查询。与IN谓词对应的是NOT IN。
例5-106 查询与“吴晨”在同一个系学习的学生的学号、姓名、所在的系。
查询“吴晨”所在的系是一个子查询，查询学生的学号、姓名、系是主查询。由于可能有多个名为“吴晨”的同学，所以子查询的结果是一个集合。用IN谓词联结子查询。
上述查询的执行过程是：先执行内查询，得到“吴晨”所在的系的集合，然后将该集合作为外查询的条件，执行外查询。
本查询也可以用联结查询来实现。
先从course表中查询课程名称为MATHS的课程编号，再从sc表中查询选修了该课程编号的学生编号，最后从student表中查询该学生编号所对应的姓名。也可用下列语句实现。
2.带ANY谓词的子查询
带ANY谓词的子查询的一般格式如下：
＜表达式＞＜比较运算符＞ANY＜子查询结果集＞
例5-108 查询有一门课程成绩等于95分的学生学号和姓名。
SQL＞SELECT sno，sname
FROM student
WHERE sno ANY
（SELECT sno
FROM sc
WHERE grade 95）；
例5-109 查询其他系中比信息系某一学生年龄小的学生姓名和年龄。
SQL＞SELECT Sname，age
FROM student
WHERE age＜ANY
（SELECT age
FROM student
WHERE dept″IS″）
AND dept＜＞′IS′
ORDER BY age DESC
3.带ALL谓词的子查询
带ALL谓词的子查询的一般格式如下：
＜表达式＞＜比较运算符＞ALL＜子查询结果集＞
例5-110 查询所有课程成绩均大于90分的学生学号和姓名。
SQL＞SELECT sno，sn
FROM student s
WHERE 90＜ALL
（SELECT grade
FROM sc
WHERE s.snosc.sno）；
例5-111 查询比所有女同学年龄大的男同学的学号和姓名。
SQL＞SELECT s1.sno，s1.sname
FROM student s1
WHERE ***′M′
AND age＞ALL
（SELECT s2.age
FROM student s2
WHERE ***′F′）
4.带有比较运算符的子查询
使用ANY或ALL谓词前必须使用比较运算符。
带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行联结。当用户能确切知道子查询返回的是单值时，可以用＞、＜、＞、＜，或＜＞等比较运算符替代IN。
例5-112 查询选修课程名为“信息系统”的学生学号和姓名。
SQL＞SELECT sno，sname
FROM student
WHERE sno IN
（SELECT sno
FROM sc
WHERE cno in
（SELECT cno
FROM course
WHERE cname′信息系统′））
例5-113 查询选修c2课程的学生学号和姓名。
SQL＞SELECT sno，sname
FROM student
WHERE sno in
（SELECT sno
FROM sc
WHERE cno′c2′）；
或者写成如下格式：
SQL＞SELECT sno，sname
FROM student
WHERE sno any
（SELECT sno
FROM sc
WHERE cno′c2′）；
例5-114 查询没选修c2课程的学生学号和姓名。
SQL＞SELECT sno，sname
FROM student
WHERE sno not in
（SELECT sno
FROM sc
WHERE cno′c2′）；
或者写成如下格式：
SQL＞SELECT sno，sname
FROM student
WHERE sno＜＞all
（SELECT sno
FROM sc
WHERE cno′c2′）；
例5-115 查询其他系中比信息系某一学生年龄小的学生姓名和年龄。
SQL＞SELECT sname，age
FROM student
WHERE dept＜＞′IS′
AND age＜any（SELECT age
FROM student
WHERE dept′IS′）
或者用集合函数改写成如下格式：
SQL＞SELECT sname，age
FROM student
WHERE dept＜＞′IS′
AND age＜（SELECT max（age）
FROM student
WHERE dept′IS′）
例5-116 查询其他系中比信息系所有学生年龄都小的学生姓名和年龄。
SQL＞SELECT sname，age
FROM student
WHERE dept＜＞′IS′
AND age＜all（SELECT age
FROM student
WHERE dept′IS′）
或者用集合函数改写成如下格式：
SQL＞SELECT sname，age
FROM student
WHERE dept＜＞′IS′
AND age＜（SELECT min（age）
FROM student
WHERE dept′IS′）
上述查询执行时，先执行子查询，然后根据子查询的结果执行父查询。查询涉及多个表时，用嵌套查询逐步求解，层次清楚，具有结构化程序设计的优点。
5.4.3.2 相关子查询
查询条件依赖于外层父查询的某个属性值的子查询为相关子查询。
相关子查询的执行过程是：首先取外层查询中表1的第1个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句的子查询返回真，则取此元组放入结果表中，然后取外层查询中表1的下一个元组，重复上述过程，直到外层查询中表1全部检索完为止。
相关子查询的特点如下：
子查询不能独立运行，依赖于父查询中取的值。
子查询需多次运行。
先执行外层的主查询，后执行内层的子查询。
1.带有EXISTS谓词的子查询
EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true”或逻辑假值“false”。若内层子查询结果非空，则外层主查询的WHERE后的条件为真，否则为假。
由EXISTS引出的子查询（即相关子查询），其目标列表达式通常都用*（最好用常量），这样执行效率比较高。因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
与EXISTS对应的是NOT EXISTS谓词。
例5-117 查询所有选修C1号课程的学生姓名。
SQL＞SELECT sname
FROM student
WHERE EX ISTS
（SELECT*
FROM sc
WHERE snostudent.sno
AND cno′C1′）；
执行过程是：首先找外层查询中student表的第一行，根据它的sno值处理内层子查询，若子查询结果非空，则exists为真，就把student表的第一行的sname值取出放入查询结果的结果集中；然后找student表的第二行、第三行……重复上述过程，直到student表中所有行均被检索过为止。
例5-122 检索所学课程包含学生S3所学全部课程的学生学号（相当于查询学号X，对所有课程Y，只要S3选修了课程Y，则学生X也选修课程Y）。
SQL＞SELECT distinct x.sno
FROM sc as x
WHE RE not exists
（SELECT′x′
FROM sc as y
WHERE y.sno′s3′and not exists
（SELECT′x′
FROM sc as z
WHERE x.snoz.sno and z.cnoy.cno））；
5.4.3.3 其他子句中的子查询
上述不相关子查询和相关子查询介绍的，都是在SELECT语句的WHERE子句中使用子查询的例子，在其他DML语句和SELECT语句的其他子句中也都可以使用子查询。
1.FROM 子句中的子查询
子查询可以出现在SELECT语句的FROM子句中，与定义和使用视图很类似。FROM子句中的子查询用括号括起来，并且可以给其取一个别名。
例5-123 查询每个部门的平均工资，并且找出每个部门中的每一个雇员的工资与其所在部门的平均工资的差。
SQL＞SELECT e.deptno，e.ename，e.sal salary，a.average，
e.sal-a.average difference
FROM emp e，（SELECT deptno，avg（sal）average
FROM emp
GROUP BY deptno）a
WHERE e.deptnoa.deptno
ORDER BY 1，2
2.HAVING 子句中的子查询
HAVING 子句中也可以使用子查询，Oracle服务器先执行子查询，然后将子查询的结果返回给主查询的HAVING 子句。
例5-124 查询emp表中平均工资超过30号部门平均工资的部门信息。
SQL＞SELECT deptno，avg（sal）
FROM emp
GROUP BY deptno
HAVING avg（sal）＞
（SELECT avg（sal）
FROM emp
WHERE deptno30）
例5-125 查询平均工资最低的job。
SQL＞SELECT job，age（sal）
FROM emp
GROUP BY job
HAVING avg（sal）
（SELECT min（avg（sal））
FROM emp
GROUP BY job）
3.多表查询时的子查询
子查询可作为关系运算中的一部分，也可以查询多个表的信息。
例5-126 在纽约的雇员中，查询工资高于scott的雇员（假设在emp表中只有一个scott）。
SQL＞SELECT ename，job，sal
FROM emp，dept
WHERE loc′net york′
AND emp.deptnodept.deptno
AND sal＞（SELECT sal
FROM emp
WHERE ename′scott′）
如果需要找出在纽约的雇员中，工资高于在DALLAS工作的scott的雇员（假如公司中有不止一个scott），语句如下。
SQL＞SELECT ename，job，sal
FROM emp，dept
WHERE loc′new york′
AND emp.deptnodept.detpno
AND sal＞（SELECT sal
FROM emp，dept
WHERE ename′scott′
AND loc′nallas′
AND emp.deptnodept.deptno）
4.其他DML语句中的子查询
例5-127 用相关子查询实现将所有雇员的工资改成相应部门的最大工资。
SQL＞UPDATE emp
SET sal（SELECT max（sal）
FROM emp e
WHERE e.deptnoemp.deptno）
例5-128 用相关子查询实现删除工资低于该部门平均工资的雇员记录。
SQL＞DELETE FROM emp e
WHERE sal＜（SELECT avg（sal）FROM emp
WHERE deptnoe.deptno）
例5-129 用子查询往表中插入记录。
SQL＞INSERT INTO emp
SELECT*FROM oldemp
也可以在INSERT语句的VALUES子句中指定子查询。
SQL＞INSERT INTO dept
VALUES（（SELECT max（deptno）+10 FROM dept），′education′，′beijing′）
例5-130 使用带聚组函数的子查询来实现查询最早受雇的雇员。
SQL＞SELECT ename，hiredate
FROM emp
WHERE hiredate
（SELECT min（hiredate）
FROM emp）
注意：可以在INSERT、UPDATE、DELETE语句中使用一个子查询替代表名。例如：
SQL＞DELETE FROM（SELECT*FROM dept WHERE deptno＜20）WHERE deptno10；
5.4.4 集合查询
每一个SELECT语句都能获得一个或一组元组。若要把多个SELECT语句的结果合并为一个结果集，可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。运算结果集的结构与第一个查询结果的结构一样。
需要注意的是，参加UNION、INTERSECT、MINUS操作的各查询结果集中的数据项数目必须相同；对应项的数据类型也必须相同。各个查询结果中的列名可以不同。
5.4.4.1 集合的并运算
集合的并运算（UNION）将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。
例5-131 查询计算机科学系的学生或年龄不大于19岁的学生。
SQL＞SELECT*
FROM student
WHERE dept′CS′
UNION
SELECT*
FROM student
WHERE age＜19
5.4.4.2 集合的交运算
集合的交操作（INTERSECT）是将多个查询结果中的公共元组形成一个完整的查询结果。
例5-132 查询计算机系并且年龄不大于19岁的学生。（即查询计算机系的学生与年龄不大于19岁的学生的交集。）
SQL＞SELECT*
FROM student
WHERE dept′CS′
INTERSECT
SELECT*
FROM student
WHERE age＜19
或者可以写成如下格式：
SQL＞SELECT*
FROM student
WHERE dept′CS′
AND age＜19
5.4.4.3 集合的差运算
集合的差操作（MINUS）就是将属于第一个查询结果而不属于其他查询结果的元组形成一个完整的查询结果。
例5-133 查询计算机系并且年龄大于19岁的学生。（即查询计算机系的学生与年龄不大于19岁的学生的差集。）
SQL＞SELECT*
FROM student
WHERE dept′CS′
MINUS
SELECT*
FROM student
WHERE age＜19
或者写成如下格式：
SQL＞SELECT*
FROM student
WHERE dept′CS′
AND age＞19
5.4.5 视图的查询
视图定义后，用户可以像对基本表那样对视图进行查询。
