Oracle PL/SQL——子查询

SQL> desc test.stu
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SNO                                       NOT NULL VARCHAR2(10)
 SNAME                                              VARCHAR2(10)
 SGENTLE                                            VARCHAR2(2)
 SAGE                                               NUMBER(2)
 SDEPT                                              VARCHAR2(20)

SQL> desc test.grade
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SNO                                       NOT NULL VARCHAR2(10)
 CNAME                                              VARCHAR2(20)
 SCORE                                              NUMBER

1.返回单行的子查询

返回单行的子查询是内部的SELECT语句结果作为外部语句中条件子句的一部分，其执行结果只有一行记录并向外返回。

查找出学生表STU中年龄与”赵九“相同的所有学生的记录

SELECT *
FROM test.STU
WHERE SAGE=(
SELECT SAGE
FROM test.STU
WHERE SNAME='赵九')

对于含有子查询的SQL语句来说，Oracle PL/SQL对其执行以下3个步骤。

1. 执行子查询，获取指定字段的返回结果。
2. 将子查询的结果带入外部查询中，通常是外部查询中的where条件子句。
3. 根据外部查询的where子句条件，输出SELECT子句中指定的列值记录。

2.含有聚合函数的单行子查询

使用聚合函数作为子查询中的返回结果，找出STU表中所有学生中年龄最大的学生的所有基本信息。

SELECT *
FROM test.STU
WHERE SAGE=(
SELECT max(SAGE)
FROM test.stu)

3.多表查询中返回结果为单行的子查询

在学生表STU和学生成绩表GRADE中，找出成绩为85分的学生基本信息。

SELECT *
FROM test.stu
WHERE SNO = (
SELECT SNO 
FROM test.grade
WHERE SCORE=85)

注意：使用单行子查询时，用户一定要确认子查询只能返回单个值。如果子查询返回的多个记录的值，那么Oracle将会报错。

4.用谓词IN实现多行子查询

从表STU中找出与学生姓名为“张三”在同一个班的所有学生基本信息。

SELECT *
FROM test.stu
WHERE SDEPT IN (
SELECT SDEPT
FROM test.STU
WHERE SNAME='张三')

带有IN谓词的子查询表示父查询与子查询之间用关键字IN进行连接，判断某个属性列值是否在子查询的结果中。在多行子查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。

注意：IN谓词可以在前面加上NOT关键字，表示取反运算，NOT IN表示WHERE子句后的列取值不能是子查询返回结果中的任意一个。

5.多表查询中使用IN谓词

找出学生表STU和学生成绩表GRADE中成绩高于70分的学生基本信息。

SELECT *
FROM test.stu
WHERE SNO IN(
SELECT SNO 
FROM test.grade
WHERE SCORE>70)

一般来说，在具体应用中，如果能用子查询实现的查询，一般不用连接查询。这是因为子查询更符合人们的思维习惯，其语句可读性更强。更重要的是，子查询的执行效率要高于连接查询，这对于数据量大的数据表来说是非常重要的。

6.EXISTS子查询

在学生表STU和学生成绩表GRADE中，找出有“计算机”课程考试成绩的所有学生基本信息。

SELECT *
FROM test.stu
WHERE EXISTS(
SELECT *
FROM test.grade
WHERE SNO=STU.SNO AND CNAME='计算机')

EXISTS代表存在量词“∃”。带有EXISTS谓词的子查询不返回任何实际数据，其只产生逻辑真值TRUE或逻辑假值FALSE。

注意：EXISTS子查询能取代所有其他子查询，但不是所有的EXISTS子查询都能被其他子查询替代。

7.EXISTE替代单行子查询

在学生表STU中找出与学生“赵九”同一年龄的所有学生信息。

SELECT *
FROM test.stu s1
WHERE EXISTS (
SELECT *
FROM TEST.STU s2
WHERE s1.sage=s2.sage AND s2.sname='赵九')

EXISTS谓词允许在前面加“NOT”关键字表示取反运算

注意：由EXISTS引出的子查询，其目标列表达式通常都用“*”表示，因为EXISTS的子查询只返回逻辑值TRUE或FALSE，给出列名亦无实际意义。

8.相关子查询

在学生表STU和学生成绩表GRADE找出参加了“计算机”课程并且分数在80分以上的所有学生信息。

SELECT *
FROM test.stu
WHERE 80<=(
SELECT SCORE
FROM test.grade
WHERE SNO=STU.SNO AND grade.CNAME='计算机')

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

相关子查询与非相关子查询的不同点在于，相关子查询依赖于父查询，父查询和子查询是有联系的。

9.含聚合函数的相关子查询

在学生表STU和学生成绩表GRADE中找出至少有一门课程有成绩的所有学生基本信息。

SELECT *
FROM test.stu
WHERE 1<=(
SELECT COUNT(*)
FROM test.grade
WHERE SNO=STU.SNO)

10.带IN谓词的相关子查询

在学生表STU和学生成绩表GRADE中找出有“计算机”课程成绩的学生信息。

SELECT *
FROM test.stu
WHERE '计算机' IN(
SELECT CNAME
FROM test.GRADE
WHERE SNO=STU.SNO)

11.包含分组的相关子查询

在学生表STU中找出人数至少有两人的班级及其平均年龄。

SELECT SDEPT 班级,AVG(SAGE) 平均年龄
FROM test.stu s1
GROUP BY s1.sdept
HAVING AVG(SAGE)<ANY(
SELECT SAGE
FROM test.stu s2
WHERE s1.sdept=s2.sdept)

12.带ANY的子查询

学生表STU中，找出其他班中比“外语”班中某一个学生年龄小的学生所有信息。

SELECT *
FROM test.stu
WHERE SDEPT<>'外语'
AND SAGE<ANY(
SELECT SAGE
FROM test.stu
WHERE SDEPT='外语')

13.带ALL的子查询

学生表STU中，找出其他班中比“外语”班中所有学生年龄小的学生所有信息。

SELECT *
FROM test.stu
WHERE SDEPT<>'外语'
AND SAGE<ALL(
SELECT SAGE
FROM test.stu
WHERE SDEPT='外语')

14.嵌套子查询

CREATE TABLE test.course(
cname VARCHAR2(10),
cscore NUMBER)

INSERT INTO test.course VALUES('计算机',6);
INSERT INTO test.course VALUES('数据结构',4);

对学生表STU、学生成绩表GRADE和课程表COURSE进行操作，要求找出“计算机”班的学生获得了6个学分课程的学生基本信息，按年龄降序排列。

SELECT *
FROM test.stu
WHERE SNO IN(
SELECT SNO
FROM test.grade
WHERE CNAME=(
SELECT CNAME
FROM test.Course
WHERE cscore=6))
AND SDEPT='计算机'
ORDER BY SAGE DESC

注意：在子查询内部嵌套其他子查询时，Oracle支持的嵌套层次最多为255。同时，读者应尽量少用嵌套子查询，因为使用表连接时，查询的性能可能会更高。

15.FROM子句后的子查询

在学生基本表STU中找出年龄大于19岁的所有学生的学号、姓名和所在班级信息，要求FROM子句后使用子查询作为查询目标。

SELECT *
FROM(
SELECT SNO,SNAME,SAGE,SDEPT FROM test.STU)
WHERE SAGE>19

16.SELECT子句后的查询

在学生基本信息表STU中找出学号为“0001”的学生所在班级信息，要求在SELECT子句后通过子查询实现，而不能在父查询中使用WHERE子句。

SELECT (
SELECT SDEPT FROM test.STU
WHERE SNO='0001')
FROM test.stu

17.HAVING子句后的子查询

在学生基本信息表STU中找出所有年龄大于18岁的学生所在班级人数，并分组输出。

SELECT SDEPT 所在班级,COUNT(SDEPT) 所在班级人数
FROM test.Stu
GROUP BY SDEPT
HAVING SDEPT IN
(SELECT SDEPT 
FROM test.stu
WHERE SAGE>18)
ORDER BY SDEPT

18.子查询返回值为空

SELECT *
FROM test.stu 
WHERE SDEPT=(
SELECT SDEPT
FROM test.stu
WHERE SAGE>20)
ORDER BY SNO

上述语句中，首先执行子查询，该子查询返回STU表中年龄大于20岁的学生所在班级信息。从原始数据表中可以看出，STU表中没有年龄大于20 的学生，因此其返回的所在班级值为NULL。当父查询的WHERE子句获取该NULL值后，相当于WHERE SDEPT=NULL语句，包含该语句的SELECT语句不能返回任何记录行。

注意：由于子查询的特殊性，读者应尽量避免让子查询的返回值为NULL值，因为这将导致父查询的返回结果也为空值。