MySQL 子查询完全指南

本文档专门讲 子查询（Subquery）：什么是子查询、可以写在哪些位置、有哪些类型、怎么用、和 JOIN 的区别，以及常见错误。每一步都配有详细说明和大量示例，适合零基础新手跟着做。

什么是子查询
示例表与数据准备
子查询在 WHERE 里（做条件）
子查询与 IN / NOT IN
子查询与 EXISTS / NOT EXISTS
子查询在 FROM 里（派生表）
子查询在 SELECT 里（标量子查询）
相关子查询
子查询与 JOIN 如何选择
常见错误与注意点
综合示例与速查表

1. 什么是子查询

1.1 定义

子查询就是写在一条 SQL 里面的另一条 SELECT，相当于“查询里再套一个查询”。
外面的查询叫外层查询，里面的 SELECT 叫子查询；子查询的结果可以给外层用来做条件、当表用、或当一列用。

1.2 通俗理解

例如：“查年龄大于平均年龄的学生”——要先算出平均年龄，再用这个值和每个人的年龄比较。
“平均年龄”就可以用一条子查询算出来，再在外层 WHERE 里用。
再如：“查有成绩记录的学生”——“有成绩记录”等价于“学号在成绩表里出现过”，可以用 学号 IN (SELECT 学号 FROM 成绩表) 表示。

1.3 子查询可以出现的位置

位置	作用简述	典型用法
WHERE	做比较、IN、EXISTS 等条件	最常用
FROM	当一张“临时表”再查	派生表，必须起别名
SELECT	为每一行算出一个值（标量）	多列展示时常用
HAVING	分组后按聚合结果再筛	较少用，了解即可

下面按“WHERE → FROM → SELECT”的顺序讲，并补充 IN、EXISTS、相关子查询。

2. 示例表与数据准备

后面示例都基于下面两张表，请先建好并插入数据。

2.1 学生表 students

CREATE TABLE IF NOT EXISTS students (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50) NOT NULL,
    gender      CHAR(1) DEFAULT '男',
    age         INT,
    class_name  VARCHAR(20)
);

INSERT INTO students (name, gender, age, class_name) VALUES
('张三', '男', 18, '高一1班'),
('李四', '女', 17, '高一2班'),
('王五', '男', 18, '高一1班'),
('赵六', '女', 17, '高一2班'),
('钱七', '男', 19, '高一1班'),
('孙八', '女', 18, '高一2班');

2.2 成绩表 scores

CREATE TABLE IF NOT EXISTS scores (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject    VARCHAR(20) NOT NULL,
    score      DECIMAL(5,2)
);

INSERT INTO scores (student_id, subject, score) VALUES
(1, '语文', 85),
(1, '数学', 92),
(2, '语文', 88),
(2, '数学', 76),
(3, '语文', 72),
(3, '数学', 85),
(4, '语文', 90),
(5, '语文', 95),
(5, '数学', 88);

3. 子查询在 WHERE 里（做条件）

子查询放在 WHERE 里时，通常用来得到一个值或一列值，再和外层的列做比较或做 IN/EXISTS 判断。

3.1 子查询返回“一个值”（标量子查询）

当子查询只返回一行一列（一个值）时，可以放在 =、>、=、<=、!= 等比较运算符右边。

示例：查年龄大于“平均年龄”的学生

先算平均年龄，再和每条记录的 age 比较：

SELECT * FROM students
WHERE age > (SELECT AVG(age) FROM students);

子查询 (SELECT AVG(age) FROM students) 得到一个数（如 17.83）。
外层相当于 WHERE age > 17.83，只保留年龄大于该值的学生。

示例：查年龄等于“最小年龄”的学生

SELECT * FROM students
WHERE age = (SELECT MIN(age) FROM students);

示例：查成绩高于“语文平均分”的语文成绩记录

SELECT * FROM scores
WHERE subject = '语文' AND score > (SELECT AVG(score) FROM scores WHERE subject = '语文');

注意：子查询必须只返回一个值（一行一列），否则会报错 “Subquery returns more than 1 row”。若可能多行，要用 IN 或别的方式（见下节）。

3.2 子查询返回“多行一列”

多行一列不能直接和 =、> 等比较，要配合 IN、ANY、ALL 等使用，最常用的是 IN（下一节）。

4. 子查询与 IN / NOT IN

4.1 IN (子查询)

列 IN (子查询) 表示：该列的值在子查询返回的那一列结果里就保留。

子查询通常返回一列、多行。

示例：查“有成绩记录”的学生（学号在成绩表里出现过的）

SELECT * FROM students
WHERE id IN (SELECT student_id FROM scores);

子查询 (SELECT student_id FROM scores) 得到一列学号（如 1,2,3,4,5，可能重复）。
外层保留 id 在这些学号里的学生；重复的学号 IN 会自动按“是否在集合里”判断，不影响结果。

示例：查“考过语文”的学生

SELECT * FROM students
WHERE id IN (SELECT student_id FROM scores WHERE subject = '语文');

4.2 NOT IN (子查询)

列 NOT IN (子查询) 表示：该列的值不在子查询返回的结果里才保留。

示例：查“没有成绩记录”的学生

SELECT * FROM students
WHERE id NOT IN (SELECT student_id FROM scores);

重要：NOT IN 与 NULL

若子查询结果里包含 NULL，NOT IN 的结果会变成“永远不为真”（因为和 NULL 比较结果是未知），导致整条条件不成立，可能查不到任何行。

例如：

-- 若子查询可能返回 NULL（如 SELECT some_col FROM ... 且 some_col 有 NULL）
WHERE id NOT IN (SELECT some_col FROM ...)

安全做法：子查询里确保不返回 NULL（例如 SELECT 主键、或 WHERE 列 IS NOT NULL），或改用 NOT EXISTS（见下节）。

示例：查“没考过语文”的学生（子查询用主键，无 NULL）

SELECT * FROM students
WHERE id NOT IN (SELECT student_id FROM scores WHERE subject = '语文');

4.3 IN / NOT IN 小结

IN (子查询)：列的值在子查询结果集合里。
NOT IN (子查询)：列的值不在集合里；子查询结果里不要有 NULL，否则 NOT IN 容易出错。

5. 子查询与 EXISTS / NOT EXISTS

5.1 EXISTS (子查询)

EXISTS (子查询) 只关心子查询有没有返回行：

有至少一行 → 条件为真；
一行都没有 → 条件为假。
不关心子查询具体返回什么列、多少列，所以子查询里常写 SELECT 1 或 **SELECT ***。

示例：查“有成绩记录”的学生（用 EXISTS）

思路：对每个学生，看成绩表里是否存在该学生的记录。

SELECT * FROM students s
WHERE EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id);

对外层每一行 s，子查询查 scores 里是否有 student_id = s.id。
有则 EXISTS 为真，该学生保留；没有则为假，不保留。

这种“子查询里用到了外层的列（s.id）”叫相关子查询，后面会再提。

示例：查“考过语文”的学生

SELECT * FROM students s
WHERE EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id AND sc.subject = '语文');

5.2 NOT EXISTS (子查询)

NOT EXISTS (子查询) 表示：子查询没有返回任何行时条件为真。

示例：查“没有成绩记录”的学生

SELECT * FROM students s
WHERE NOT EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id);

优点：和 NULL 无关，即使 scores 里某列有 NULL 也不会像 NOT IN 那样出问题，适合“不存在”类判断。

5.3 EXISTS 与 IN 的简单对比

IN：先执行子查询得到一整份结果集，再判断“在不在里面”；子查询不依赖外层时常用。
EXISTS：往往对外层每一行都执行一次子查询，看有没有匹配；适合“是否存在”的判断，且 NOT EXISTS 比 NOT IN 更安全（避免 NULL）。

6. 子查询在 FROM 里（派生表）

子查询写在 FROM 后面时，相当于一张临时表，外层再对这张“表”做 SELECT。
规定：FROM 里的子查询必须起别名（如 AS t），否则会报错。

6.1 基本语法

SELECT 列...
FROM (SELECT 列... FROM 表名 [WHERE ...] [GROUP BY ...]) AS 别名
[WHERE ...] [ORDER BY ...];

6.2 示例：先按班级统计人数，再从中查“人数 ≥ 2”的班级

第一步：按班级分组统计人数（当成一张临时表）；第二步：对这张表再筛“人数 >= 2”：

SELECT 班级名, 人数
FROM (
    SELECT class_name AS 班级名, COUNT(*) AS 人数
    FROM students
    GROUP BY class_name
) AS t
WHERE 人数 >= 2;

6.3 示例：每个学生的平均分，再查“平均分高于 85”的

SELECT 学号, 平均分
FROM (
    SELECT student_id AS 学号, AVG(score) AS 平均分
    FROM scores
    GROUP BY student_id
) AS t
WHERE 平均分 > 85;

6.4 派生表小结

FROM 里的子查询叫派生表，必须写 AS 别名。
适合“先聚合/先算一层，再在这一层上做条件或排序”的场景。

7. 子查询在 SELECT 里（标量子查询）

子查询写在 SELECT 后面时，通常要保证它只返回一个值（标量），这样外层每一行都会得到一列结果。

7.1 基本用法

示例：查每个学生及其所在班级的人数

每个学生一行，多一列“该学生所在班级的总人数”：

SELECT
    s.name AS 姓名,
    s.class_name AS 班级,
    (SELECT COUNT(*) FROM students s2 WHERE s2.class_name = s.class_name) AS 班级人数
FROM students s;

对每一行 s，子查询统计 class_name = s.class_name 的人数，得到一个数，填在“班级人数”列。
这里子查询用到了外层的 s.class_name，也是相关子查询。

示例：每个学生的总成绩（用子查询在 SELECT 里算）

SELECT
    s.name AS 姓名,
    (SELECT SUM(score) FROM scores sc WHERE sc.student_id = s.id) AS 总分
FROM students s;

注意：SELECT 里的子查询必须返回一行一列，否则报错。且这类子查询会对外层每一行执行一次，数据量大时可能较慢，有时用 JOIN + GROUP BY 更高效（见后文“子查询与 JOIN 如何选择”）。

8. 相关子查询

8.1 什么是相关子查询

相关子查询指子查询里引用了外层的列（如外层表的 id、class_name），子查询要依赖“当前这一行”的值，所以往往会对外层每一行执行一次。

前面用过的例子：

EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id) 里的 s.id 是外层列。
*SELECT 里 (SELECT COUNT() FROM students s2 WHERE s2.class_name = s.class_name) 里的 s.class_name** 是外层列。

8.2 再举一例：比本班平均年龄大的学生

“本班平均年龄”依赖当前行的 class_name，所以用相关子查询：

SELECT * FROM students s
WHERE age > (SELECT AVG(age) FROM students s2 WHERE s2.class_name = s.class_name);

对每个学生 s，子查询先算 s.class_name 这个班的平均年龄，再比较 s.age 是否大于该值。

8.3 性能注意

相关子查询会对外层每一行执行一次子查询，行数多时可能较慢。若逻辑能用 JOIN + GROUP BY 或 窗口函数 写，有时性能更好，但初学阶段以“写对、读懂”为主即可。

9. 子查询与 JOIN 如何选择

9.1 很多“存在/包含”类条件可以互换

“有成绩的学生”可以用 IN (SELECT student_id FROM scores)，也可以用 JOIN scores ON … 再 DISTINCT 或 GROUP BY。
“没有成绩的学生”可以用 NOT EXISTS，也可以用 LEFT JOIN … WHERE 右表.列 IS NULL。

9.2 何时倾向用子查询

条件很直观是“一个值”（如大于平均、等于最大）时，WHERE 列 > (SELECT AVG(…)) 很直观。
“是否存在”且要避免 NULL 时，NOT EXISTS 比 NOT IN 安全。
“先聚合再筛选”时，FROM (SELECT … GROUP BY …) AS t WHERE … 逻辑清晰。

9.3 何时倾向用 JOIN

需要同时从多张表取多列、且关系清晰时，JOIN 更常见，例如“学生 + 班级名 + 成绩”。
大表 + 相关子查询可能慢，改成 JOIN 或先聚合再 JOIN 有时更快。

实际中两种都会用，可以按“可读性 + 性能”逐步体会。

10. 常见错误与注意点

10.1 子查询返回多行时用 =、>、<

用 =、>、< 时，右边子查询必须只返回一个值，否则报错 “Subquery returns more than 1 row”。
多行时改用 IN 或 ANY/ALL 等。

10.2 NOT IN 与 NULL

子查询结果里有 NULL 时，NOT IN 可能导致条件永远不成立，查不到数据。解决办法：子查询里排除 NULL，或改用 NOT EXISTS。

10.3 FROM 子查询没有写别名

FROM (SELECT …) AS t 中 AS t 必须写，否则报错 “Every derived table must have its own alias”。

10.4 子查询里列名写错或表名冲突

子查询里若引用外层列，要写清楚是外层表别名.列名；若子查询里也有同名表，要用不同别名（如 s 和 s2）区分。

10.5 SELECT 里标量子查询返回多行

SELECT 里的子查询必须返回一行一列，否则报错。确保子查询有条件限制成“只出一行”（如按主键、或聚合函数）。

11. 综合示例与速查表

11.1 综合示例（可整段执行）

USE school;

-- 1. WHERE：年龄大于平均年龄
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

-- 2. WHERE + IN：有成绩的学生
SELECT * FROM students WHERE id IN (SELECT student_id FROM scores);

-- 3. WHERE + NOT IN：没有成绩的学生（注意子查询别返回 NULL）
SELECT * FROM students WHERE id NOT IN (SELECT student_id FROM scores);

-- 4. WHERE + EXISTS：有成绩的学生
SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id);

-- 5. WHERE + NOT EXISTS：没有成绩的学生
SELECT * FROM students s WHERE NOT EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id);

-- 6. FROM 派生表：每班人数，再筛人数>=2
SELECT * FROM (
    SELECT class_name AS 班级, COUNT(*) AS 人数 FROM students GROUP BY class_name
) AS t WHERE 人数 >= 2;

-- 7. SELECT 标量子查询：每个学生 + 其总分
SELECT s.name, (SELECT SUM(score) FROM scores sc WHERE sc.student_id = s.id) AS 总分
FROM students s;

-- 8. 相关子查询：比本班平均年龄大的学生
SELECT * FROM students s
WHERE age > (SELECT AVG(age) FROM students s2 WHERE s2.class_name = s.class_name);

11.2 子查询速查表

位置	子查询要求	典型写法
WHERE	单值	`WHERE 列 > (SELECT AVG(列) FROM 表)`
WHERE	多行一列	`WHERE 列 IN (SELECT 列 FROM 表)`
WHERE	存在/不存在	`WHERE EXISTS (SELECT 1 FROM 表 WHERE 关联条件)`、`NOT EXISTS ...`
FROM	多行多列（当表用）	`FROM (SELECT ... FROM 表) AS 别名`，必须写别名
SELECT	单值（每行一个）	`SELECT (SELECT 聚合(...) WHERE 关联) AS 列名 FROM 表`

注意：WHERE 里用 =、>、< 时子查询必须返回一个值；IN/EXISTS 不要求单值；FROM 子查询必须 AS 别名。

把“WHERE 子查询、IN/NOT IN、EXISTS/NOT EXISTS、FROM 派生表、SELECT 标量子查询、相关子查询”过一遍后，你就能在需要“先算一层再比较/再查”或“是否存在”时写出正确子查询。建议用文档里的 students 和 scores 把上面的每条示例都跑一遍，再自己改条件多试几种，巩固子查询的用法。

访问数: 23