Mysql子查询5大核心用法代码示例实用指南

作者：袖梨 2026-09-30

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“Mysql子查询5大核心用法代码示例”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

前言

子查询（Subquery），也叫嵌套查询，是 MySQL 中在一个查询语句内部嵌套另一个查询结合项目来看，的高级语法，能实现复杂的多表关联、条件过滤、数据筛选，是 SQL 进阶的核心知识点。

实际处理时，本文围绕图片中的 5 大类子查询，从语法、逻辑、示例代码三个维度，带你彻底掌握 MySQL 子查询。

一、子查询基础概念

1. 核心定义

  • 子查询：嵌套在 SELECT/INSERT/UPDATE/DELETE 语句中的 SELECT 语句，也叫「内查询」
  • 外层查询：包含子查询的主语句，也叫「外查询」
  • 执行顺序：先执行子查询，再执行外层查询，子查询的结果作为外层查询的条件 / 数据源

2. 准备测试数据

新建两张关联表，用来所有示例：

学生表student

CREATE TABLE student (
    stu_id INT PRIMARY KEY AUTO_INCREMENT,
    stu_name VARCHAR(20) NOT NULL,
    class VARCHAR(10) NOT NULL,
    age INT NOT NULL
);
INSERT INTO student VALUES
(1, '张三', '一班', 18),
(2, '李四', '二班', 19),
(3, '王五', '一班', 18),
(4, '赵六', '三班', 20),
(5, '孙七', '二班', 19);

成绩表score

CREATE TABLE score (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    stu_id INT NOT NULL,
    subject VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (stu_id) REFERENCES student(stu_id)
);
INSERT INTO score VALUES
(1, 1, '语文', 85),
(2, 1, '数学', 92),
(3, 2, '语文', 78),
(4, 2, '数学', 88),
(5, 3, '数学', 88),
(6, 4, '英语', 60),
(7, 5, '语文', 95);

二、5 大类子查询详解

1. 带比较运算符的子查询（最基础）

核心逻辑

用 = > < >= <= != 等比较运算符，将外层查询的字段与子查询得到的单行单列结果进行比较。

要求：子查询必须得到1 行 1 列的标量值，否则报错。

示例代码

-- 需求：查询成绩 > 班级平均分的学生信息
-- 步骤1：先查二班的平均分（子查询，返回标量）
-- 步骤2：外层查询筛选成绩 > 平均分的学生
SELECT s.stu_name, sc.subject, sc.score
FROM student s
JOIN score sc ON s.stu_id = sc.stu_id
WHERE s.class = '二班' 
  AND sc.score > (
      -- 子查询：计算二班的平均分
      SELECT AVG(score) FROM score 
      WHERE stu_id IN (SELECT stu_id FROM student WHERE class = '二班')
  );

运行结果

stu_namesubjectscore
李四数学88
孙七语文95

说明

子查询先算出二班平均分 (78+88+95)/3 ≈ 87，外层查询筛选出二班中成绩 > 87 的学生。

2. 带IN关键字的子查询（最常用）

核心逻辑

IN 用来判断外层字段是否在子查询得到的多行单列结果集中，等价于「多个 OR 条件」。

要求：子查询得到多行 1 列的结果集。

示例代码

-- 需求：查询有成绩的学生信息（子查询返回所有有成绩的stu_id）
SELECT * FROM student
WHERE stu_id IN (
    -- 子查询：返回所有有成绩的stu_id（多行单列）
    SELECT DISTINCT stu_id FROM score
);

-- 反向：查询没有成绩的学生（NOT IN）
SELECT * FROM student
WHERE stu_id NOT IN (SELECT DISTINCT stu_id FROM score);

运行结果（IN 示例）

stu_idstu_nameclassage
1张三一班18
2李四二班19
3王五一班18
4赵六三班20
5孙七二班19

说明

子查询先查出所有有成绩的 stu_id，外层查询用 IN 匹配学生表，筛选出对应学生。

3. 带ANY/SOME关键字的子查询

核心逻辑

ANY 和 SOME 完全等价，表示 **「满足子查询结果中的任意一个」**，即「只要有一个满足条件，就得到 true」。

语法：字段 > ANY(子查询) → 字段 > 子查询结果中的任意一个值（等价于 > 最小值）语法：字段 < ANY(子查询) → 字段 < 子查询结果中的任意一个值（等价于 < 最大值）

示例代码

-- 需求：查询成绩 > 一班任意学生成绩的学生（即 > 一班最低分）
SELECT s.stu_name, sc.subject, sc.score
FROM student s
JOIN score sc ON s.stu_id = sc.stu_id
WHERE sc.score > ANY(
    -- 子查询：一班所有学生的成绩（85,92,88）
    SELECT sc2.score FROM score sc2
    JOIN student s2 ON sc2.stu_id = s2.stu_id
    WHERE s2.class = '一班'
);

运行结果

stu_namesubjectscore
张三数学92
李四数学88
王五数学88
孙七语文95

说明

一班成绩为 [85,92,88]，> ANY 等价于 > 85（最小值），所以所有 >85 的成绩都会被筛选出来。

4. 带ALL关键字的子查询

核心逻辑

ALL 表示 **「满足子查询结果中的所有值」**，即「必须全部满足条件，才得到 true」。

语法：字段 > ALL(子查询) → 字段 > 子查询结果中的所有值（等价于 > 最大值）语法：字段 < ALL(子查询) → 字段 < 子查询结果中的所有值（等价于 < 最小值）

示例代码

-- 需求：查询成绩 > 一班所有学生成绩的学生（即 > 一班最高分）
SELECT s.stu_name, sc.subject, sc.score
FROM student s
JOIN score sc ON s.stu_id = sc.stu_id
WHERE sc.score > ALL(
    -- 子查询：一班所有学生的成绩（85,92,88）
    SELECT sc2.score FROM score sc2
    JOIN student s2 ON sc2.stu_id = s2.stu_id
    WHERE s2.class = '一班'
);

运行结果

stu_namesubjectscore
孙七语文95

说明

一班最高分为 92，> ALL 等价于 > 92，所以只有孙七的 95 分满足条件。

5. 带EXISTS关键字的子查询（关联子查询）

核心逻辑

EXISTS 是关联子查询，子查询会引用外层查询的字段，执行逻辑为：

  1. 外层查询逐行遍历表
  2. 子查询用当前行的字段进行查询
  3. 若子查询得到至少 1 行结果，则 EXISTS 为 true，外层行保留；否则丢弃

特点：子查询不得到实际数据，只得到 true/false，性能极高（适合大表）

示例代码

-- 需求：查询有成绩的学生信息（EXISTS 版本，等价于 IN 版本）
SELECT * FROM student s
WHERE EXISTS(
    -- 关联子查询：用外层s的stu_id查询成绩表
    SELECT 1 FROM score sc 
    WHERE sc.stu_id = s.stu_id
);

-- 反向：查询没有成绩的学生（NOT EXISTS）
SELECT * FROM student s
WHERE NOT EXISTS(
    SELECT 1 FROM score sc 
    WHERE sc.stu_id = s.stu_id
);

运行结果

与 IN 示例完全一致，得到所有有成绩的学生。

关键说明

  • EXISTS 子查询中 SELECT 1 是最优写法（无需查询实际字段，只判断是否有行）
  • EXISTS 性能远优于 IN，尤其是大表场景（IN 会全表扫描，EXISTS 逐行匹配）
  • IN 适合子查询结果集小的场景，EXISTS 适合子查询结果集大的场景

三、核心对比与避坑指南

1. 5 大类子查询核心区别

子查询类型关键字子查询结果要求核心逻辑适用场景
比较运算符= > <单行单列与标量值比较单值条件过滤
ININ多行单列匹配结果集中任意一个多值匹配、去重筛选
ANY/SOMEANY/SOME多行单列满足任意一个「比任意一个大 / 小」场景
ALLALL多行单列满足所有「比所有都大 / 小」场景
EXISTSEXISTS任意（只看是否有行）关联匹配，得到 true/false大表关联、高性能筛选

2. 常用避坑

  1. 子查询结果行数错误

    • 比较运算符要求子查询必须得到1 行 1 列，否则报错
    • IN/ANY/ALL 要求子查询得到多行 1 列，否则逻辑错误

  2. NOT IN 空值陷阱

    • 若子查询结果包含 NULL，NOT IN 会得到空结果（因为 NULL 参与比较结果为 UNKNOWN）
    • 解决方案：用 NOT EXISTS 替代 NOT IN，或在子查询中过滤 NULL

  3. 关联子查询性能优化

    • EXISTS 子查询中，关联字段必须加索引，否则性能骤降
    • 避免在子查询中采用 SELECT *，用 SELECT 1 最优

  4. 子查询嵌套层级

    • 结合项目来看，MySQL 兼容多层子查询嵌套，但层级过多会严重影响性能，尽量用连接查询替代

四、综合实战：多子查询组合

-- 需求：查询二班中，成绩 > 一班所有学生成绩的学生信息
SELECT s.stu_name, sc.subject, sc.score
FROM student s
JOIN score sc ON s.stu_id = sc.stu_id
WHERE s.class = '二班'
  AND sc.score > ALL(
      SELECT sc2.score FROM score sc2
      JOIN student s2 ON sc2.stu_id = s2.stu_id
      WHERE s2.class = '一班'
  );

运行结果

stu_namesubjectscore
孙七语文95

五、核心总结

  1. 子查询本质：嵌套查询，先内后外，子查询结果作为外层条件
  2. 5 大核心用法：
    • 比较运算符：单值比较，子查询必须得到标量
    • IN：多值匹配，子查询得到多行单列
    • ANY/SOME：任意满足，等价于「> 最小 / < 最大」
    • ALL：全部满足，等价于「> 最大 / < 最小」
    • EXISTS：关联匹配，高性能，适合大表
  3. 性能优先级：EXISTS > IN > 多层子查询
  4. 避坑关键：注意子查询结果行数、NOT IN 空值陷阱、关联字段加索引

从实现思路看，总的来说，MySQL子查询适合结合实际项目边做边理解。先抓住核心思路，再逐步补上细节和边界处理，最后效果会更稳定，也更容易复用。

相关文章

精彩推荐