子查询

导学

子查询（Subquery）是在另一条 SQL 语句中嵌套的查询。它让 SQL 具备了"分步思考"的能力：先用内层查询算出一个值或一个集合，再交给外层查询使用。

定义

子查询：嵌套在另一个查询（主查询）内部的 SELECT 语句。子查询可以返回单一值（标量子查询）、一行、一列，或一个结果集。

完整示例准备：建表与数据

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

-- 考核成绩表
CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    score DECIMAL(5,2)
);

INSERT INTO scores (emp_id, score) VALUES
(1, 90.00),
(2, 85.00);

完整示例一：标量子查询（SELECT 列表中）

当前数据状态

employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

scores 表：

score_idemp_idscore
1190.00
2285.00

执行标量子查询

SELECT
    e.emp_id,
    e.emp_name,
    (SELECT score FROM scores WHERE emp_id = e.emp_id) AS 考核分
FROM employees e;

操作后的结果

emp_idemp_name考核分
1大翔90.00
2白歌85.00

结果解读

  • 标量子查询必须且只能返回一行一列，否则报错
  • 为每个员工查询其在 scores 表中的考核成绩
  • 两个员工在 scores 表中都有记录，因此都返回具体分数

完整示例二：IN 子查询（WHERE 中）

当前数据状态

基于上面的 employees 表和 scores 表。

执行 IN 子查询

-- 查询有考核记录且分数达到 90 的员工
SELECT emp_name, dept
FROM employees
WHERE emp_id IN (
    SELECT emp_id FROM scores WHERE score >= 90
);

操作后的结果

emp_namedept
大翔技术部

结果解读

  • 子查询 (SELECT emp_id FROM scores WHERE score >= 90) 返回：1
  • 外层查询筛选出 emp_id 在这个集合中的员工
  • 只有大翔的考核分 90.00 满足条件
  • 这是非相关子查询，子查询可以独立执行，只执行一次

完整示例三：NOT IN 子查询

当前数据状态

基于上面的 employees 表和 scores 表。

执行 NOT IN 子查询

-- 查询考核分未达到 90 的员工
SELECT emp_name, dept
FROM employees
WHERE emp_id NOT IN (
    SELECT emp_id FROM scores WHERE score >= 90
);

操作后的结果

emp_namedept
白歌技术部

结果解读

  • 子查询返回考核分 >= 90 的员工 emp_id：1
  • NOT IN 排除 emp_id 在这个集合中的记录
  • 白歌（emp_id=2）的考核分 85.00，不满足 >= 90，因此返回

完整示例四：派生表（FROM 中的子查询）

当前数据状态

基于上面的 employees 表。

执行派生表查询

SELECT dept, avg_score
FROM (
    SELECT dept, AVG(score) AS avg_score
    FROM employees
    GROUP BY dept
) AS t
WHERE avg_score > 50;

操作后的结果

deptavg_score
技术部100.0000

结果解读

  • 子查询放在 FROM 中形成派生表，必须给别名（如 AS t），否则 MySQL 报错
  • 内层查询先计算每个部门的平均成绩
  • 外层查询再对派生表的结果进行过滤
  • 技术部平均分 100.00 大于 50，因此保留

完整示例五：EXISTS 子查询

当前数据状态

基于上面的 employees 表和 scores 表。

执行 EXISTS 查询

-- 查询"考核分达到 90"的员工
SELECT emp_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM scores s
    WHERE s.emp_id = e.emp_id AND s.score >= 90
);

操作后的结果

emp_name
大翔

结果解读

  • EXISTS 只关心子查询是否返回行，不关心具体值，因此子查询中用 SELECT 1 即可
  • 一旦找到匹配行，立即停止扫描（短路求值）
  • 大翔的 score_id=1（90.00），满足 >= 90，因此返回
  • 白歌的考核分 85.00，不满足条件，因此不返回

完整示例六：NOT EXISTS 子查询

当前数据状态

基于上面的 employees 表和 scores 表。

执行 NOT EXISTS 查询

-- 查询"考核分未达到 90"的员工
SELECT emp_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM scores s
    WHERE s.emp_id = e.emp_id AND s.score >= 90
);

操作后的结果

emp_name
白歌

结果解读

  • NOT EXISTS 返回子查询结果为空的员工
  • 白歌（emp_id=2）的考核分 85.00，不满足 >= 90，因此子查询返回空
  • NOT EXISTS 在判断"不存在"的场景下非常直观

完整示例七：相关子查询 vs 非相关子查询

当前数据状态

基于上面的 employees 表和 scores 表。

非相关子查询

-- 非相关子查询：子查询可独立执行，只执行一次
SELECT * FROM employees
WHERE emp_id IN (SELECT emp_id FROM scores WHERE score >= 90);

操作后的结果

emp_idemp_namedeptscore
1大翔技术部100

相关子查询

-- 相关子查询：子查询依赖外层表的值，每行执行一次
SELECT * FROM employees e
WHERE score > (
    SELECT score FROM scores WHERE emp_id = e.emp_id
);

操作后的结果

emp_idemp_namedeptscore
1大翔技术部100

结果解读对比

特性非相关子查询相关子查询
执行次数只执行一次外层每行执行一次
依赖关系不依赖外层表依赖外层表的列值
性能通常较好通常较差
示例WHERE emp_id IN (...)WHERE score > (SELECT score ... WHERE emp_id = e.emp_id)

性能警告：相关子查询（Correlated Subquery）通常性能较差，因为子查询会对外层表的每一行都执行一次。MySQL 5.7 的优化器会尝试将其转为 JOIN，但不如手动改写可靠。

完整示例八：子查询优化——用 JOIN 替代

当前数据状态

基于上面的 employees 表。

子查询写法

-- 查询与"大翔"同部门的所有员工
SELECT emp_name FROM employees
WHERE dept = (SELECT dept FROM employees WHERE emp_name = '大翔');

操作后的结果

emp_name
大翔
白歌

优化为 JOIN

-- 使用 JOIN 替代子查询
SELECT e1.emp_name
FROM employees e1
JOIN employees e2 ON e1.dept = e2.dept
WHERE e2.emp_name = '大翔';

操作后的结果

emp_name
大翔
白歌

结果解读

  • 子查询写法需要先执行内层查询得到'技术部'，再执行外层查询
  • JOIN 写法将两张 employees 表关联，通常性能更好，尤其是数据量大时
  • MySQL 5.7 优化器会自动将某些子查询转为半连接（Semi-Join），但手动改写更可控

完整示例九：比较运算符子查询（=, >, < 等）

当前数据状态

基于上面的 employees 表。

执行查询

-- 查询成绩高于全体员工平均分的员工
SELECT emp_name, score
FROM employees
WHERE score > (SELECT AVG(score) FROM employees);

操作后的结果

emp_namescore
（空）

修改条件后重新执行

-- 查询成绩大于等于全体员工平均分的员工
SELECT emp_name, score
FROM employees
WHERE score >= (SELECT AVG(score) FROM employees);

操作后的结果

emp_namescore
大翔100

结果解读

  • 子查询 (SELECT AVG(score) FROM employees) 返回全体员工的平均分 100.0000（NULL 不参与计算）
  • 外层查询筛选出 score 大于等于 100.00 的员工
  • 使用 =, >, <, >=, <=, <> 等比较运算符时，子查询必须返回单一值（标量子查询）

完整示例十：ANY / ALL 子查询

当前数据状态

基于上面的 employees 表和 scores 表。

执行 ANY 查询

-- 查询员工表 score 高于任意一个考核成绩的员工
SELECT emp_name, score
FROM employees
WHERE score > ANY (
    SELECT score FROM scores
);

操作后的结果

emp_namescore
大翔100

执行 ALL 查询

-- 查询员工表 score 高于所有考核成绩的员工
SELECT emp_name, score
FROM employees
WHERE score > ALL (
    SELECT score FROM scores
);

操作后的结果

emp_namescore
大翔100

结果解读

  • ANY 表示大于子查询结果中的任意一个值即可

  • scores 表考核成绩为：90.00, 85.00

  • score > ANY (...) 等价于 score > MIN(...)，即 score > 85.00

  • 大翔 100 > 85，满足条件；白歌 NULL 不满足

  • ALL 表示大于子查询结果中的所有值

  • score > ALL (...) 等价于 score > MAX(...)，即 score > 90.00

  • 大翔 100 > 90，满足条件

常见误区

误区正解
子查询总是比 JOIN 慢不一定。MySQL 5.7 优化器会自动将某些子查询转为半连接（Semi-Join）。
SELECT * 在 EXISTS 中有意义EXISTS 不关心返回什么列，SELECT 1 是最优写法。
派生表不需要别名MySQL 要求 FROM 中的子查询必须指定别名。

面试考点

Q：IN 和 EXISTS 哪个好？

取决于子查询结果集大小和数据分布。EXISTS 通常更适合子查询结果大、主查询结果小的场景，因为它一旦找到匹配就停止扫描。MySQL 5.7 优化器对两者都会做优化，实际差异需用 EXPLAIN 分析。

Q：什么是派生表（Derived Table）？

放在 FROM 子句中的子查询称为派生表。MySQL 5.7 中，派生表会被物化为临时表（Derived Merge 优化前）。MySQL 5.7.6+ 引入了 Derived Condition Pushdown 等优化。

Q：correlated subquery 为什么慢？

因为子查询依赖外层表的列值，外层每处理一行就要执行一次子查询。如果外层有 10000 行，子查询就要执行 10000 次。应尽量改写为 JOIN。

小结

  • 子查询让 SQL 具备分步计算能力，出现在 SELECT、FROM、WHERE、HAVING 中
  • 标量子查询返回单一值，派生表返回结果集
  • EXISTS 适合"是否存在"的判断，找到匹配即短路
  • 相关子查询性能通常较差，可考虑改写为 JOIN

下一章引子：子查询处理"一个查询的结果作为另一个查询的输入"，而 UNION 则处理"把两个查询的结果合并在一起"。