04 子查询
📖 本节导读
- 理解什么是子查询：查询里再套一个查询，先算里面、再算外面。
- 掌握两种最常用的子查询：标量子查询（返回一个值）和列子查询（配合 IN 使用）。
- 学会把子查询放进 FROM 当"派生表"用（必须起别名），并认识 EXISTS。
- 知道子查询和连接查询各自适合什么场景，学会二者之间怎么选。
一、什么是子查询
需求：查出所有高于全校平均分的成绩。
按人脑思路要分两步：
- 先算出全校平均分：78.90909；
- 再查
score > 78.90909的成绩。
难道每次都要人工先查一遍平均分，再抄进第二条 SQL 吗？不用——SQL 允许把第一步整条查询塞进第二步的括号里：
SELECT ... WHERE score > (SELECT AVG(score) FROM score);
括号里的查询叫子查询（也叫内层查询），外面的叫主查询（外层查询）。执行规则和数学里的括号一样：先算里面，再算外面——MySQL 先执行子查询得到 78.90909，再拿这个值去跑外层查询。
子查询按"返回结果的形状"分类，用法各不相同：
| 类型 | 返回形状 | 常配合的操作符 |
|---|---|---|
| 标量子查询 | 一行一列（一个值） | = > < >= <= <> |
| 列子查询 | 多行一列 | IN、ANY、ALL |
| 行子查询 | 一行多列 | = (a, b) |
| 表子查询 | 多行多列 | 放在 FROM 后当表 |
二、标量子查询：返回一个值
"标量"听着唬人，其实就是"单个值"。上面的需求完整写出来（顺便连表显示姓名）：
SELECT s.name AS 学生, sc.course_id, sc.score AS 成绩
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
WHERE sc.score > (SELECT AVG(score) FROM score);
+-----------+-----------+--------+
| 学生 | course_id | 成绩 |
+-----------+-----------+--------+
| 张三 | 1 | 90.0 |
| 张三 | 2 | 85.0 |
| 张三 | 3 | 88.0 |
| 李四 | 2 | 92.0 |
| 王小红 | 1 | 95.0 |
| 赵敏 | 3 | 81.0 |
+-----------+-----------+--------+
6 rows in set (0.00 sec)
全校平均分是 868 ÷ 11 = 78.90909（AVG 忽略陈刚的 NULL），高于它的成绩正好是这 6 条。76.0、73.0、70.0、60.0、58.0 都不够，NULL 与任何值比较结果都不成立，也不会出现。
再来一个：查出年龄最小（生日最大）的学生：
SELECT name, birthday
FROM student
WHERE birthday = (SELECT MAX(birthday) FROM student);
+--------+------------+
| name | birthday |
+--------+------------+
| 陈刚 | 2006-05-30 |
+--------+------------+
1 row in set (0.00 sec)
⚠️ 标量子查询必须恰好返回一个值。如果子查询查出了多行，外层的
=、>就不知道跟谁比，直接报错：ERROR 1242 (21000): Subquery returns more than 1 row。
三、列子查询：多行一列，配 IN
需求：查一班学生的所有成绩。
第一步"一班有哪些学生"返回的是多个 id（1、2、5），一个值装不下，这时用 IN 接住整列结果：
SELECT student_id, course_id, score
FROM score
WHERE student_id IN (SELECT id FROM student WHERE class_id = 1);
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 1 | 1 | 90.0 |
| 1 | 2 | 85.0 |
| 1 | 3 | 88.0 |
| 2 | 1 | 76.0 |
| 2 | 2 | 92.0 |
| 5 | 2 | 73.0 |
| 5 | 3 | NULL |
+------------+-----------+-------+
7 rows in set (0.00 sec)
子查询先算出一班学生 id 列表 (1, 2, 5)，外层等价于 WHERE student_id IN (1, 2, 5)，命中张三 3 条、李四 2 条、陈刚 2 条，共 7 行。
反过来用 NOT IN 就是"不在名单里"，比如查二班学生的成绩记录数等，思路相同。
四、行子查询：一行多列（了解即可）
行子查询一次返回"一行里的多个列"，可以整行比较。比如"找出和张三性别相同、且同班的学生"：
SELECT name, gender, class_id
FROM student
WHERE (gender, class_id) = (SELECT gender, class_id FROM student WHERE name = '张三');
+--------+--------+----------+
| name | gender | class_id |
+--------+--------+----------+
| 张三 | 男 | 1 |
| 李四 | 男 | 1 |
| 陈刚 | 男 | 1 |
+--------+--------+----------+
3 rows in set (0.00 sec)
(gender, class_id) = ('男', 1) 相当于两个条件同时相等。实际开发中出场率不高，见到能认出来即可。
五、子查询放在 FROM 中：派生表
子查询的结果是"多行多列"时，它本身就长得像一张表——那就可以放在 FROM 后面，当成临时表来查。这种表叫派生表。
需求：查出"平均分不低于 80 的学生"的 id 和平均分。平均分是分组算出来的结果，想再对它做筛选和加工，可以先把"每人平均分"做成派生表：
SELECT t.student_id, t.avg_score
FROM (SELECT student_id, AVG(score) AS avg_score
FROM score
GROUP BY student_id) AS t
WHERE t.avg_score >= 80;
+------------+-----------+
| student_id | avg_score |
+------------+-----------+
| 1 | 87.66667 |
| 2 | 84.00000 |
+------------+-----------+
2 rows in set (0.00 sec)
验算：张三 (90+85+88)÷3 = 87.66667；李四 (76+92)÷2 = 84；王小红 75、赵敏 69.5、陈刚 73（NULL 被 AVG 忽略），都不到 80。
派生表必须起别名（上面的 AS t），哪怕你后面根本用不到这个名字。漏写会报错：
ERROR 1248 (42000): Every derived table must have its own alias
可以这样理解：MySQL 把子查询结果当成一张真的表来对待，而表总得有个名字才能被引用。
六、EXISTS：只问"有没有"
EXISTS 不关心子查询查出了什么，只关心能不能查到至少一行：查得到就成立，查不到就不成立。常用于"存在某某记录的……"这类需求。
例：查出"至少有一门课达到 90 分"的学生：
SELECT s.id, s.name
FROM student AS s
WHERE EXISTS (SELECT 1 FROM score AS sc
WHERE sc.student_id = s.id AND sc.score >= 90);
+----+-----------+
| id | name |
+----+-----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王小红 |
+----+-----------+
3 rows in set (0.00 sec)
张三有 90、李四有 92、王小红有 95，成立；赵敏最高 81、陈刚最高 73，不成立。
注意这个子查询里用到了外层的 s.id——外层每检查一个学生，子查询就带着这个学生的 id 跑一次，这种"里面引用外面"的子查询叫相关子查询。SELECT 1 是习惯写法：既然只看有没有行，查什么列都无所谓，写 1 最省事。NOT EXISTS 则是"不存在……的"。
七、子查询 vs 连接查询：怎么选
很多需求两种写法都行。比如"查一班学生的所有成绩"用连接写：
SELECT sc.student_id, sc.course_id, sc.score
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
WHERE s.class_id = 1;
结果和第三节的 IN 子查询完全一样（7 行）。选择建议：
| 场景 | 建议 |
|---|---|
| 结果里需要显示多张表的列（姓名+课程名） | 只能用 JOIN |
| 条件依赖一个统计值（高于平均分） | 标量子查询最自然 |
| 只用另一张表做"名单过滤"，不显示它的列 | IN / EXISTS 子查询更直观 |
| 对分组统计结果再加工 | FROM 派生表 |
经验法则：能用 JOIN 尽量用 JOIN——大多数情况下优化器对 JOIN 处理得更好，而且信息都在一个平面上，方便扩展列。但当子查询能让 SQL 像大白话一样"先算 X 再比 X"时，直观易读也是重要的价值，不必教条。
⚠️ 新手常见坑
- 标量子查询返回了多行：
= / > / <后面的子查询必须恰好一行一列，多行会报 ERROR 1242。拿不准就改用 IN。 - FROM 里的派生表忘记起别名：必报 ERROR 1248（Every derived table must have its own alias），无一例外。
- NOT IN 遇上 NULL 全军覆没：如果子查询结果里含 NULL，
NOT IN会一行都查不出来（NULL 比较的老问题）。子查询里记得加WHERE 列 IS NOT NULL，或改用 NOT EXISTS。 - 括号漏写：子查询必须整个包在圆括号里，
WHERE score > SELECT AVG(score)...是语法错误。 - 什么都套子查询：三层四层嵌套的 SQL 很难读也难优化，先想想能不能用一个 JOIN + GROUP BY 说清楚。
📝 小结
- 子查询 = 查询套查询，先执行内层，再把结果交给外层；必须写在圆括号里。
- 标量子查询返回单个值，配
= > <等比较；列子查询返回一列多行，配 IN / NOT IN。 - 行子查询整行比较，了解即可；子查询放 FROM 里叫派生表，必须起别名。
- EXISTS 只判断"子查询有没有结果"，常写成带外层引用的相关子查询。
- 选择原则：要显示多表的列或追求性能优先 JOIN；"先算一个值再比较"这类需求用子查询更直观。
✍️ 练习题
- 查出低于全校平均分的成绩（显示 student_id、course_id、score）。
参考答案
SELECT student_id, course_id, score
FROM score
WHERE score < (SELECT AVG(score) FROM score);
平均分 78.90909，低于它的 5 条：李四数学 76.0、王小红英语 60.0、王小红计算机 70.0、赵敏数学 58.0、陈刚英语 73.0。陈刚的 NULL 不参与比较，不会出现。
- 用 IN 子查询，查出二班学生的所有成绩记录。
参考答案
SELECT student_id, course_id, score
FROM score
WHERE student_id IN (SELECT id FROM student WHERE class_id = 2);
二班是王小红(3)、赵敏(4)，共 5 行：(3,1,95.0)、(3,2,60.0)、(3,3,70.0)、(4,1,58.0)、(4,3,81.0)。
- 查出数学考了最高分的学生姓名（提示：先用标量子查询算数学最高分）。
参考答案
SELECT s.name, sc.score
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
WHERE sc.course_id = 1
AND sc.score = (SELECT MAX(score) FROM score WHERE course_id = 1);
数学最高分 95.0，是王小红。
- 用 FROM 派生表，查出"各科平均分"中的最高值。
参考答案
SELECT MAX(t.avg_score) AS 最高科目均分
FROM (SELECT course_id, AVG(score) AS avg_score
FROM score
GROUP BY course_id) AS t;
三科平均分为 79.75000、77.50000、79.66667，最高是 79.75000（数学）。别忘了别名 t。
- 用 NOT EXISTS 查出"没有任何一门课达到 85 分"的学生姓名。
参考答案
SELECT s.name
FROM student AS s
WHERE NOT EXISTS (SELECT 1 FROM score AS sc
WHERE sc.student_id = s.id AND sc.score >= 85);
赵敏（最高 81.0）和陈刚（最高 73.0）。张三、李四、王小红都有 85 分以上的成绩，被排除。