当前位置：首页 > news >正文

【SQL】子查询详解（附例题）

news 来源：原创 2025/8/23 16:40:08

子查询

子查询的表示形式为：(SELECT 语句)，它是IN、EXISTS等运算符的运算数，它也出现于FROM子句和VALUES子句。包含子查询的查询叫做嵌套查询。嵌套查询分为相关嵌套查询和不想关嵌套查询

WHERE子句中的子查询

比较运算符

子查询的结果是元组的集合，即一个表，一般情况下，其关系模式有多列，关系实例有若干行。如果子查询的结果是一个单列且单行的表，则可作为比较运算符的运算对象

例1 查询与学号2000012的学生在同一个系的学生的详细信息

注：本文所有例题涉及的关系实例如下：
在这里插入图片描述

学生信息都在Student表中；要查询全部信息，使用*选择所有列；查询条件是和学号为‘2000012’的学生同一个系。
可以先查询学号为‘2000012’的学生的系，再作为父查询的条件查询最终结果

SELECT *    --父查询
FROM Student
WHERE Sdept = (SELECT Sdept   --子查询FROM StudentWHERE Sno = '2000012');

查询结果
在这里插入图片描述
（实验环境：SQL Sever 2012）

上面的SQL语句的WHERE子句中出现了子查询，该语句的执行过程为：先执行子查询，得到一个值’计算机’，再用这个值替换子查询，得到一个新的SQL语句：

SELECT *    --父查询
FROM Student
WHERE Sdept = '计算机';

然后执行新的SQL语句，得到结果。

该例的子查询不依赖于父查询而单独执行，这样的嵌套查询叫做不相关嵌套查询

例：查询选修了1156号课程并且成绩大于该课程平均成绩的学生的学号和成绩

SELECT Sno, Grade
FROM SC
WHERE Cno = '1156' AND Grade > (SELECT AVG(Grade)FROM SCWHERE Cno = '1156');

在这里插入图片描述
因为AVG是一个聚集函数，所以子查询返回一个单行单列的数据，可以按上述方法使用

如果子查询返回一个单列多行的表，则这个子查询不能直接出现在比较表达式，需要使用SOME或ALL修饰符，SOME是值集合的某一元素，ALL代表集合的全体元素

例：查询其他系比管理系某一学生年龄小的学生的姓名和年龄

查询管理系学生的年龄：

SELECT Sage
FROM Student
WHERE Sdept = '管理';

该查询结果为{18, 19, 19}，不是一个单值。比某一学生年龄小要使用表达式Sage<SOME(18,19,19)，如果Sage的值小于集合中某一元素的值，则比较结果为真，否则为假

SELECT Sname, Sage
FROM Student
WHERE Sdept <> '管理' AND Sage < SOME (SELECT SageFROM StudentWHERE Sdept = '管理');

在这里插入图片描述

上面的查询还可以使用聚集函数实现
比某一学生年龄就，就是要小于所有学生年龄的最大值

SELECT Sname, Sage
FROM Student
WHERE Sdept <>'管理' AND Sage < (SELECT MAX(Sage)FROM StudentWHERE Sdept = '管理');

例：查询其他系比管理系所有学生年龄都小的学生的姓名和年龄

SELECT Sname, Sage
FROM Student
WHERE Sdept <> '管理' AND Sage < ALL(SELECT SageFROM StudentWHERE Sdept = '管理');

该查询同样可以使用聚集函数
比所有学生年龄小，就是要小于所有学生年龄的最小值

SELECT Sname, Sage
FROM Student
WHERE Sdept <>'管理' AND Sage < (SELECT MIN(Sage)FROM StudentWHERE Sdept = '管理');

在这里插入图片描述

不存在这样的学生，所以查询结果为空

SOME、ALL修饰符与聚集函数及谓词IN的对应关系如表所示

	=	<>	<	<=	>	>=
SOME	IN		<MAX	<=MAX	>MIN	>=MIN
ALL		NOT IN	<MIN	<=MIN	>MAX	>=MAX

例：查询平均成绩不小于85分的学生的姓名和所在系

按照题意，设计查询过程：首先从Student表中任取一个学生，假设为x，然后从SC表中汇总出x的平均成绩，如果平均成绩不小于85，则输出x的姓名和所在系

求学生x的平均成绩的SQL语句为：

SELECT Sno, AVG(Grade)
FROM SC
WHERE Sno = x.Sno;

在SELECT语句中，除了表名、列名外，现在还出现了一个变量x，x叫做元组变量，表示某个表的一个元组。元组变量的名称只能是表名或表的别名，这样，通过元组变量的名称就知道它代表哪个表的元组

最终查询语句如下：

SELECT x.Sname, x.Sdept
FROM Student x
WHERE (SELECT AVG(Grade)FROM SCWHERE x.Sno = SC.Sno) > 85;

在这里插入图片描述
这样的嵌套查询叫做相关嵌套查询，因为子查询有一个变量x，当未确定x的值时，无法得到查询结果，而x代表父查询的元组，与父查询相关。不相关嵌套查询的子查询先于父查询执行，并且只执行一次，而相关嵌套查询对父查询的每个元组都要执行一次子查询

上述语句的执行过程如下：

执行父查询，顺序扫描Student表
取Student表的一个元组赋予元组变量x
执行父查询的WHERE子句
- 将第2步获取的x传送到子查询
- 执行子查询，得到平均成绩
- 判断平均成绩是否大于等于85
如果WHERE子句的条件为真，则输出x.Sname和x.Sdept
重复步骤2~4，继续处理下一个元组，直到处理完Student表的所有元组

为了易于理解，可以使用表名作为元组变量名

谓词IN

谓词IN是二元运算符，一般书写形式为A IN S，A是一个列名，S是一个集合。如果A是集合S的元素，则结果为真，否则结果为假

例：查询选修了1024号课程的学生的姓名和所在系

本例涉及Student表和SC表，分两步构造。首先在SC表中查询选修了1024号课程的学生集合，记为S

SELECT Sno
FROM SC
WHERE Cno = '1024';

然后对Student表的每个元组t，如果t.Sno∈S成立，则t是查询结果之一。使用IN代替∈，使用元组变量表示t，就得到了下面的SQL语句

SELECT t.Sname, t.Sdept
FROM Student t
WHERE t.Sno IN(SELECT SnoFROM SCWHERE Cno = '1024');

在这里插入图片描述
也可以直接用表名做为元组变量

SELECT Student.Sname, Student.Sdept
FROM Student
WHERE Student .Sno IN(SELECT SnoFROM SCWHERE Cno = '1024');

上述语句执行过程为：先执行子查询，得到一个选了1024号课程的学生的集合，再执行外查询，对Student表中的每个元组，测试元组在Sno列上的分量值是否在子查询的结果中，如满足则输出这个元组

例：查询选修了“管理学”的学生学号和姓名

选修了管理学的学生：

SELECT Sno
FROM SC, Course
WHERE SC.Cno = Course.Cno AND Course.Cname = '管理学';

判断Student表中的每个元组在Sno上的分量是否在学生集合中，得：

SELECT Sno, Sname
FROM Student
WHERE Student.Sno IN(SELECT SnoFROM SC, CourseWHERE SC.Cno = Course.Cno AND Course.Cname = '管理学');

或

SELECT Sno, Sname
FROM Student
WHERE Sno IN(SELECT SnoFROM SCWHERE Cno IN(SELECT CnoFROM CourseWHERE Cname = '管理学'));

在这里插入图片描述

谓词EXISTS

谓词EXISTS是一元运算符，运算数是一个集合，如果该集合不是空集，则运算结果为真，否则运算结果为假。

例：查询所有选修了1024号课程的学生的姓名

SELECT Sname
FROM Student
WHERE Sno IN (SELECT SnoFROM SCWHERE Cno='1024');

这是一个不相关嵌套查询，也可以使用EXISTS谓词实现

对Student表的任何一个元组x，如果选修了1024号课程，则SC表中存在x的选课记录，该记录在Sno列上的分量等于x.Sno，在Cno上的分量等于’1024’。因此，集合SELECT * FROM SC WHERE Sno = x.Sno AND Cno = '1024'一定不是空集，则表达式EXISTS(SELECT * FROM SC WHERE Sno = x.Sno AND Cno = '1024)为真；如果x没有选修1024号课程，则表达式结果为假

SELECT x.Sname
FROM Student x
WHERE EXISTS(SELECT *FROM SCWHERE Sno = x.Sno AND Cno = '1024');

在这里插入图片描述
例：查询至少选修了学号为2000014的学生所选修的全部课程的学生的姓名及所在系

用R表示学号为2000014的学生所选修的全部课程的集合，S表示学生x选修的全部课程的集合，如果R⊆S成立，则x是要查找的学生

SELECT x.Sname x.Sdept
FROM Student x
WHERE x.Sno!='2000014' ANDNOT EXISTS (SELECT t.CnoFROM SC tWHERE Sno='2000014' AND NOT EXISTS(SELECT CnoFROM SCWHERE Sno = x.Sno AND Cno=t.Cno));

例：查询与学号为2000014的学生所选修的课程相同的学生的姓名

用R表示学号为2000014的学生选修的所有课程的集合，用S表示学生x选修的课程，如果R=S，则x是要查询的学生

SELECT Student x
FROM Student x
WHERE x.Sno != '2000014' AND NOT EXISTS (SELECT Cno			--R∈SFROM SC yWHERE Sno = '2000014' AND NOT EXISTS(SELECT CnoFROM SCWHERE Sno = x.Sno AND Cno = y.Cno))ANDNOT EXISTS (SELECT CnoFROM SC zWHERE Sno = x.Sno AND NOT EXISTS(SELECT CnoFROM SCWHERE Sno = '2000014' AND Cno = z.Cno));

FROM 子句中的子查询

FROM子句可以指定查询要使用的表。子查询的结果是一个表，但只是一个中间结果，并没有存放在数据库。为了在FROM子句使用子查询，要给子查询生成的临时表命名，有时还要命名临时表的列

例：查询每门课的名称和平均成绩

首先可以很容易的写出查询每门课的课程号和平均成绩的SQL语句

SELECT Cno, AVG(Grade)
FROM SC
GROUP BY Cno;

在这里插入图片描述
为了得到课程的名字，将临时表和Course表连接即可

SELECT Cname, Grade
FROM Course, (SELECT Cno, AVG(Grade)FROM SCGROUP BY Cno) AS tmp(Cno, Grade)  --命名临时表
WHERE Course.Cno = tmp.Cno;

也可以使用连接操作和分组操作实现

SELECT Cname, AVG(Grade)
FROM Course, SC
WHERE Course.Cno = SC.Cno
GROUP BY Cname;

外连接

使用条件连接运算时，只有满足连接条件的元组才能作为查询条件。假设A表和B表做条件连接，有时A表中会有某个元组t，由于在B表中没有任何一个元组满足与t的连接条件，因此t不会出现在连接结果中。

为了解决参与连接的表的某些元组没有出现在连接结果中的问题，需要使用左外连接、右外连接和全外连接运算，作为区分，前面介绍的连接叫做内连接

左外连接

A表和B表做左外连接，其过程是先按照连接条件做连接运算，得到一个结果。如果A的某个元组t不在结果中，则将t和B的一个“万能元组”做连接，这个万能元组在所有列上取空值，即（NULL, NULL, …, NULL），形成一个新元组，加入最终结果。这个“万能元组”并不真实存在

表达为

A LEFT OUTER JOIN B ON Condition

Course表和SC表做左外连接运算，物理课出现在查询结果中

SELECT *
FROM Course LEFT OUTER JOIN SC ON Course.Cno = SC.Cno

在这里插入图片描述

右外连接

A表和B表做右外连接，其过程是先按照连接条件做连接运算，得到一个结果。如果B的某个元组t不在结果中，则将t和A的一个“万能元组”做连接，形成一个新元组，加入最终结果。

表达为

A RIGHTOUTER JOIN B ON Condition

全外连接

全外连接是左外连接和右外连接的并

例：查询每门课的选修人数

根据题意，查询结果需要包括每门课程，所以使用左外连接，然后对查询结果分组、统计

有两种写法：

SELECT Cname, COUNT(*)
FROM Course LEFT OUTER JOIN SC ON Course.Cno = SC.Cno
GROUP BY Cname;

在这里插入图片描述

SELECT Cname, COUNT(Sno)
FROM Course LEFT OUTER JOIN SC ON Course.Cno = SC.Cno
GROUP BY Cname;

在这里插入图片描述

COUNT(*)函数用于统计分组中的元组数，不会省略值为NULL的元组，所以尽管无人选修物理课，任然会计数1，这是错误的；而COUNT(Sno)在计数时舍弃了NULL值，所以统计结果为0，是符合要求的

未完待续…

子查询

WHERE子句中的子查询

比较运算符

谓词IN

谓词EXISTS

FROM 子句中的子查询

外连接

左外连接

右外连接

全外连接

相关文章：