第 8 章子查询与派生表

站长2026-10-23 08:00:00

内容摘要

子查询（subquery）就是"查询里套查询"，是 SQL 表达复杂业务逻辑的利器。本篇讲标量子查询、`IN`/`NOT IN`、`EXISTS`、`ANY`/`ALL`、关联子查询，以及 `FROM` 子句里的派生表（derived table）。我们会对比子查询与 JOIN 的优劣、讲清楚 `NOT IN` 遇到 NULL 全灭的经典坑，最后给出 MySQL 8.0 中优化器对子查询的处理。学完你能写出"查成绩高于本系平均的学生"这类业务 SQL。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

📖 本篇简介：子查询（subquery）就是"查询里套查询"，是 SQL 表达复杂业务逻辑的利器。本篇讲标量子查询、IN/NOT IN、EXISTS、ANY/ALL、关联子查询，以及 FROM 子句里的派生表（derived table）。我们会对比子查询与 JOIN 的优劣、讲清楚 NOT IN 遇到 NULL 全灭的经典坑，最后给出 MySQL 8.0 中优化器对子查询的处理。学完你能写出"查成绩高于本系平均的学生"这类业务 SQL。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

1 子查询概述与分类

子查询就是嵌套在另一个 SQL 里的 SELECT，通常用括号包起来。按返回结果的形态，分三类：

| 类型 | 返回值 | 可出现的位置 |

|---|---|---|

| 标量子查询 | 单行单列（一个值） | SELECT、WHERE、HAVING |

| 列子查询 | 单列多行（一个列表） | IN/ANY/ALL 后面 |

| 行/表子查询 | 多行多列 | FROM 后面、IN 后面 |

按"是否依赖外层查询"，又分：

非关联子查询：子查询独立执行一次，结果给外层用；
关联子查询：子查询引用外层的列，外层每行都要执行一次。

2 标量子查询

2.1 基本用法

标量子查询返回一个值，可以直接当表达式用：

-- 查所有学生，以及"全校平均分"（每行都显示这个平均分）
SELECT
    name,
    score,
    (SELECT AVG(score) FROM students) AS avg_score_all
FROM students;

子查询 (SELECT AVG(score) FROM students) 只返回一行一列，结果是个数字，外层查询把它当成一列拼出来。

2.2 用在 WHERE 里

-- 查成绩高于全校平均分的学生
SELECT name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);

这种"先算出平均值，再和每行比较"的逻辑，用子查询非常自然。

2.3 多列标量子查询（行子查询）

-- 假设要查"和张三同院系、同性别"的学生
SELECT name, dept_id, gender
FROM students
WHERE (dept_id, gender) = (
    SELECT dept_id, gender FROM students WHERE name = '张三'
);

(dept_id, gender) = (SELECT ...) 这种"多列同时比较"叫行构造器，子查询必须返回一行多列。

3 IN 与 NOT IN 子查询

3.1 IN 子查询

IN 后面跟一列多行的子查询：

-- 查"计算机系"所有学生的名字
-- 思路：先从 departments 找到计算机系的 dept_id，再用 IN 查学生
SELECT name
FROM students
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE dept_name = '计算机系'
);

等价于 INNER JOIN 写法：

SELECT s.name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id
WHERE d.dept_name = '计算机系';

两种写法结果一样，优化器在 8.0 里通常能把它们转换成相同执行计划。

3.2 NOT IN 与 NULL 坑（重点）

这是经典中的经典坑：

-- 查"不在计算机系、也不在中文系"的学生
SELECT name FROM students
WHERE dept_id NOT IN (
    SELECT dept_id FROM departments WHERE dept_name IN ('计算机系','中文系')
);

如果子查询返回的 dept_id 列表里有一个 NULL，整个 NOT IN 结果就是空——即使外面明明有学生满足条件。

原因：dept_id NOT IN (1, 2, NULL) 等价于 dept_id <> 1 AND dept_id <> 2 AND dept_id <> NULL。而 任何值 <> NULL 结果是 NULL（不是 TRUE），整个 AND 表达式结果是 NULL，被 WHERE 当成"不通过"。

解决办法：

-- 办法一：子查询里过滤 NULL
SELECT name FROM students
WHERE dept_id NOT IN (
    SELECT dept_id FROM departments
    WHERE dept_name IN ('计算机系','中文系')
      AND dept_id IS NOT NULL
);

-- 办法二：改用 NOT EXISTS（下面讲）

4 EXISTS 与 NOT EXISTS

4.1 EXISTS 用法

EXISTS 后面跟一个子查询，子查询"有没有返回行"决定真假：

-- 查有学生的院系
SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT * FROM students s WHERE s.dept_id = d.dept_id
);

注意：子查询里的 WHERE s.dept_id = d.dept_id 引用了外层 d.dept_id——这就是关联子查询。对外层每个院系，MySQL 去 students 表里找有没有匹配行，有就保留该院系。

4.2 EXISTS 与 IN 的等价

-- 用 IN 写：查计算机系学生
SELECT name FROM students
WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name='计算机系');

-- 用 EXISTS 写（逻辑等价）
SELECT name FROM students s
WHERE EXISTS (
    SELECT * FROM departments d
    WHERE d.dept_id = s.dept_id AND d.dept_name='计算机系'
);

4.3 NOT EXISTS 不受 NULL 影响

-- 查没有学生的院系
SELECT dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT * FROM students s WHERE s.dept_id = d.dept_id
);

为什么 NOT EXISTS 没那个 NULL 坑？因为 EXISTS 判断的是"子查询有没有行"，不是"值是否等于列表"。即使子查询返回 NULL，"有没有行"这件事是二值的（TRUE/FALSE），不会出现三值逻辑。

经验：写 NOT IN 时如果担心 NULL，直接换成 NOT EXISTS，更安全。

5 ANY 与 ALL

ANY/ALL 配合比较运算符使用，是 IN 的扩展：

| 运算符 | 含义 | 等价于 |

|---|---|---|

| = ANY | 等于列表任意一个 | IN |

| > ANY | 大于列表最小的那个 | —— |

| < ANY | 小于列表最大的那个 | —— |

| > ALL | 大于列表所有的（即大于最大值） | —— |

| < ALL | 小于列表所有的（即小于最小值） | —— |

-- 查成绩大于"计算机系所有学生成绩"的学生
-- 也就是成绩比计算机系最高分还高
SELECT name, score
FROM students
WHERE score > ALL (
    SELECT score FROM students s
    JOIN departments d ON s.dept_id = d.dept_id
    WHERE d.dept_name = '计算机系'
);

ANY/ALL 在业务中用得比 IN/EXISTS 少，理解上面的对照表就够。

6 关联子查询

6.1 概念

关联子查询 = 子查询里引用了外层查询的列。外层每出一行，子查询就执行一次。

-- 查"成绩高于本系平均分"的学生
SELECT name, dept_id, score
FROM students s1
WHERE score > (
    -- 子查询：计算 s1 当前所在系的平均分
    SELECT AVG(score)
    FROM students s2
    WHERE s2.dept_id = s1.dept_id
);

执行过程：

1. 外层取张三（dept_id=1, score=90）；

2. 子查询算 dept_id=1 的平均分（比如 87.5）；

3. 比较 90 > 87.5，张三保留；

4. 外层取下一行李四，重复...

6.2 关联子查询用在 SELECT 列表

-- 每行显示该生所在系的平均成绩
SELECT
    name,
    score,
    dept_id,
    (SELECT AVG(score) FROM students s2
     WHERE s2.dept_id = s1.dept_id) AS dept_avg
FROM students s1;

这种写法在做"每行对照平均值"的报表时很常见。

6.3 性能提醒

关联子查询是"外层 N 行 × 子查询 1 次"，N 大时性能差。8.0 优化器会尝试把简单的关联子查询改写成 JOIN，但复杂写法还是慢。性能敏感场景优先用 JOIN。

7 FROM 子句的派生表

7.1 派生表是什么

FROM 后面跟的子查询结果，在逻辑上就是一张"临时表"，叫派生表：

-- 先按城市分组算平均分，再从派生表里挑平均分 > 80 的城市
SELECT city, avg_score
FROM (
    SELECT city, AVG(score) AS avg_score
    FROM students
    GROUP BY city
) AS city_avg
WHERE avg_score > 80;

注意：派生表必须起别名（这里是 city_avg），否则报错。

7.2 为什么需要派生表

有些"先聚合再过滤"的逻辑，直接用 WHERE 写不了：

-- 错误：WHERE 里不能用聚合函数
SELECT city, AVG(score) AS avg_score
FROM students
WHERE avg_score > 80    -- 报错：Unknown column
GROUP BY city;

-- 正确：用 HAVING（本例场景）
SELECT city, AVG(score) AS avg_score
FROM students
GROUP BY city
HAVING avg_score > 80;

本例用 HAVING 就行。但更复杂的"二次聚合"（比如"按城市分组后，再按平均分分档统计"）必须用派生表：

-- 统计"高分城市"和"低分城市"各有多少个
SELECT
    CASE
        WHEN avg_score >= 80 THEN '高分'
        ELSE '低分'
    END AS level,
    COUNT(*) AS city_count
FROM (
    SELECT city, AVG(score) AS avg_score
    FROM students
    GROUP BY city
) AS t
GROUP BY level;

7.3 派生表 vs CTE

MySQL 8.0 引入了 CTE（Common Table Expression，WITH 语法），可读性比派生表好：

-- 8.0 CTE 写法
WITH city_avg AS (
    SELECT city, AVG(score) AS avg_score
    FROM students
    GROUP BY city
)
SELECT city, avg_score FROM city_avg WHERE avg_score > 80;

CTE 可以复用、可以嵌套，后面窗口函数章节会详细讲。

8 子查询 vs JOIN 怎么选

| 场景 | 推荐 | 理由 |

|---|---|---|

| "存在性"判断 | EXISTS | 语义清晰，性能好 |

| "不在集合"判断 | NOT EXISTS（不要 NOT IN） | 避免 NULL 坑 |

| 简单等值匹配 | IN 或 JOIN 都行 | 优化器会等价 |

| 多表列表查询 | JOIN | 最通用 |

| 二次聚合 | 派生表 / CTE | 必然要用 |

| 每行对照外层值 | 关联子查询 | 自然 |

经验法则：写完子查询后，问自己"能不能用 JOIN 重写？"——能 JOIN 就 JOIN，性能通常更好，优化器选择空间更大。

9 子查询速查表

| 需求 | 写法 |

|---|---|

| 与全校平均分比较 | WHERE score > (SELECT AVG(score) FROM t) |

| 在某集合里 | WHERE id IN (SELECT id FROM ...) |

| 不在某集合里 | WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...) |

| 存在性判断 | WHERE EXISTS (SELECT 1 FROM ... WHERE ...) |

| 大于所有 | WHERE score > ALL (SELECT score FROM ...) |

| 大于任意 | WHERE score > ANY (SELECT score FROM ...) |

| 同列多值匹配 | WHERE (a,b) = (SELECT a,b FROM ...) |

| 二次聚合 | FROM (SELECT ... GROUP BY ...) AS t |

| 每行对照外层 | 关联子查询，子查询引用外层列 |

10 常见坑与注意事项

坑 1：`NOT IN` 子查询返回 NULL 时结果为空。

三值逻辑问题。用 NOT EXISTS 替代，或在子查询里加 IS NOT NULL。

坑 2：派生表忘了起别名。

MySQL 要求 FROM 子查询必须有别名，否则报错。

坑 3：关联子查询在大表上性能差。

外层 N 行 × 子查询一次，N 大时慢。改写成 JOIN 或 CTE。

坑 4：WHERE 里把聚合当表达式。

WHERE AVG(score) > 80 报错，要么 HAVING 要么派生表。

坑 5：IN 子查询返回多列。

IN 只接一列。多列匹配用行构造器 (a,b) IN (SELECT a,b ...)。

坑 6：SELECT 列表里的标量子查询返回多行。

标量子查询必须保证一行，否则报 "Subquery returns more than 1 row"。

坑 7：把 EXISTS 的 SELECT * 当成要取数据。

EXISTS 只关心"有没有行"，SELECT * 还是 SELECT 1 性能一样，习惯写 SELECT 1。

坑 8：8.0 之前子查询不支持 LATERAL。

8.0.14 开始支持 LATERAL 派生表，可以引用外层列，之前版本要写关联子查询。

11 练习题

练习 1：标量子查询

题目：查询所有成绩大于全校平均分的学生，显示姓名和成绩。

SELECT name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);

</details>

练习 2：IN 子查询

题目：查询计算机系和中文系所有学生的姓名。

SELECT name FROM students
WHERE dept_id IN (
    SELECT dept_id FROM departments
    WHERE dept_name IN ('计算机系', '中文系')
);

</details>

练习 3：EXISTS

题目：查询有学生的院系名称。

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM students s WHERE s.dept_id = d.dept_id
);

</details>

练习 4：NOT EXISTS 替代 NOT IN

题目：查询没有学生的院系（要求用 NOT EXISTS，避免 NULL 坑）。

SELECT dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM students s WHERE s.dept_id = d.dept_id
);

</details>

练习 5：关联子查询

题目：查询成绩高于本系平均分的学生姓名、成绩、所在系。

SELECT s1.name, s1.score, s1.dept_id
FROM students s1
WHERE s1.score > (
    SELECT AVG(s2.score)
    FROM students s2
    WHERE s2.dept_id = s1.dept_id
);

</details>

练习 6：派生表二次聚合

题目：按城市分组计算平均成绩，然后统计"平均分 >= 80"的城市有多少个。

SELECT COUNT(*) AS high_avg_city_count
FROM (
    SELECT city, AVG(score) AS avg_score
    FROM students
    GROUP BY city
) AS t
WHERE avg_score >= 80;

</details>

练习 7：ALL

题目：查询成绩比机械系所有学生都高的学生。

SELECT name, score
FROM students
WHERE score > ALL (
    SELECT score FROM students s
    JOIN departments d ON s.dept_id = d.dept_id
    WHERE d.dept_name = '机械系'
);

</details>

下一篇预告：第 9 章常用函数大全

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 8 章子查询与派生表

目录

1 子查询概述与分类

2 标量子查询

2.1 基本用法

2.2 用在 WHERE 里

2.3 多列标量子查询（行子查询）

3 IN 与 NOT IN 子查询

3.1 IN 子查询

3.2 NOT IN 与 NULL 坑（重点）

4 EXISTS 与 NOT EXISTS

4.1 EXISTS 用法

4.2 EXISTS 与 IN 的等价

4.3 NOT EXISTS 不受 NULL 影响

5 ANY 与 ALL

6 关联子查询

6.1 概念

6.2 关联子查询用在 SELECT 列表

6.3 性能提醒

7 FROM 子句的派生表

7.1 派生表是什么

7.2 为什么需要派生表

7.3 派生表 vs CTE

8 子查询 vs JOIN 怎么选

9 子查询速查表

10 常见坑与注意事项

11 练习题

练习 1：标量子查询

练习 2：IN 子查询

练习 3：EXISTS

练习 4：NOT EXISTS 替代 NOT IN

练习 5：关联子查询

练习 6：派生表二次聚合

练习 7：ALL

文章推广海报

文章回复

第 8 章 子查询与派生表

目录

1 子查询概述与分类

2 标量子查询

2.1 基本用法

2.2 用在 WHERE 里

2.3 多列标量子查询（行子查询）

3 IN 与 NOT IN 子查询

3.1 IN 子查询

3.2 NOT IN 与 NULL 坑（重点）

4 EXISTS 与 NOT EXISTS

4.1 EXISTS 用法

4.2 EXISTS 与 IN 的等价

4.3 NOT EXISTS 不受 NULL 影响

5 ANY 与 ALL

6 关联子查询

6.1 概念

6.2 关联子查询用在 SELECT 列表

6.3 性能提醒

7 FROM 子句的派生表

7.1 派生表是什么

7.2 为什么需要派生表

7.3 派生表 vs CTE

8 子查询 vs JOIN 怎么选

9 子查询速查表

10 常见坑与注意事项

11 练习题

练习 1：标量子查询

练习 2：IN 子查询

练习 3：EXISTS

练习 4：NOT EXISTS 替代 NOT IN

练习 5：关联子查询

练习 6：派生表二次聚合

练习 7：ALL

第 8 章子查询与派生表