子查询

这一课你将能做到

1. 正确写出三类子查询：标量（返回一个值）、列（IN/ANY/ALL）、表（FROM 里的派生表）
2. 在「查存在/不存在」场景中正确选择 EXISTS 与 IN，并说清 NOT IN 遇 NULL 的空结果陷阱
3. 读懂相关子查询的逐行执行逻辑，并在数据量大时改写成 JOIN 或派生表

为什么要学

「找出从没提交过 easy 题的用户」「找出高于平均耗时的提交」——这类问题用 JOIN 写别扭甚至写不出来，子查询才是自然表达。同时子查询也是性能事故高发区：相关子查询逐行执行、NOT IN 撞上 NULL 直接返回空集，这些坑在面试和真实排障里都极高频。

人话：子查询就是「先算一个中间结果，再拿它当条件或当临时表」。小到一行一个值，大到一张虚拟表，都是这个套路。

先建立图像

三类子查询安放的三个位置：

SELECT  (SELECT ...)      ← 标量子查询：当一列用
FROM    (SELECT ...) AS t ← 表子查询：当临时表用（必须有别名）
WHERE   id IN (SELECT ...) ← 列子查询：当值列表用
        EXISTS (SELECT 1 ...)  ← 存在性判断：找到一行就返回

对应到业务直觉：

• 「他的提交数是多少」→ 标量子查询（算一个数当列）
• 「提交过 hard 题的用户」→ 列子查询（给一组 id）
• 「每人平均提交数」→ 派生表（先聚合再统计）

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据（6 用户、6 题、12 提交）

步骤 1：标量子查询——高于平均耗时的提交

SELECT id, user_id, problem_id, runtime_ms
FROM cf_submission
WHERE runtime_ms > (SELECT AVG(runtime_ms) FROM cf_submission);

全表平均 187.6ms（2251/12），预期只返回 1 行：id=5、runtime_ms=2000 那条超时提交。标量子查询必须保证只返回一个值，否则报 ERROR 1242。

步骤 2：列子查询——提交过 hard 题的用户

SELECT username FROM cf_user
WHERE id IN (
  SELECT user_id FROM cf_submission
  WHERE problem_id IN (
    SELECT id FROM cf_problem WHERE difficulty = 'hard'
  )
);
-- 预期：zhao（只有他提交过 graph-bfs）

两层 IN 嵌套读起来就是「hard 题的 id 列表 → 这些题的提交人列表 → 对应的用户名」。列子查询返回多行没关系，IN 负责逐个比对。

步骤 3：EXISTS 与 NOT EXISTS

-- 提交过任何提交的用户（预期：lin zhao wang sun zhou 五人，qian 落榜）
SELECT username FROM cf_user u
WHERE EXISTS (
  SELECT 1 FROM cf_submission s WHERE s.user_id = u.id
);

-- 从未提交过 easy 题的用户（预期：zhao、qian）
SELECT username FROM cf_user u
WHERE NOT EXISTS (
  SELECT 1 FROM cf_submission s
  JOIN cf_problem p ON p.id = s.problem_id
  WHERE s.user_id = u.id AND p.difficulty = 'easy'
);

注意内层引用了外层的 u.id——这就是相关子查询：外层每一行都会驱动一次内层查询。EXISTS 找到第一行匹配就短路返回 TRUE，所以「存在性判断」它效率好、语义准。

步骤 4：NOT IN 的 NULL 陷阱（必看）

-- 正常版：没做过题目 2（LRU）的用户，预期 zhao wang sun qian
SELECT username FROM cf_user
WHERE id NOT IN (SELECT user_id FROM cf_submission WHERE problem_id = 2);

-- 陷阱版：结果列表里混进一个 NULL
SELECT username FROM cf_user
WHERE id NOT IN (
  SELECT user_id FROM cf_submission WHERE problem_id = 2
  UNION SELECT NULL
);
-- 预期：空集！一行都不返回

原因：x NOT IN (1, 5, NULL) 展开是 x != 1 AND x != 5 AND x != NULL，而 x != NULL 结果是 NULL，整串 AND 变 NULL，永远不为 TRUE。防御写法：内层加 WHERE user_id IS NOT NULL，或直接改用 NOT EXISTS。

步骤 5：派生表——先聚合再统计

SELECT AVG(cnt) AS avg_sub_per_active_user
FROM (
  SELECT user_id, COUNT(*) AS cnt
  FROM cf_submission
  GROUP BY user_id
) t;
-- 预期 2.4：(3+3+2+1+3)/5

派生表必须有别名（这里是 t），MySQL 强制。它让「先算每人提交数、再对人数求平均」的两步逻辑一条 SQL 完成。

步骤 6：相关子查询改写成 JOIN

-- 慢形态：外层每行执行一次子查询
SELECT u.username,
       (SELECT COUNT(*) FROM cf_submission s WHERE s.user_id = u.id) AS cnt
FROM cf_user u;

-- 快形态：先聚合一次再 JOIN
SELECT u.username, COALESCE(t.cnt, 0) AS cnt
FROM cf_user u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS cnt
  FROM cf_submission
  GROUP BY user_id
) t ON t.user_id = u.id;

两种写法结果一致（前者 qian 的 cnt 是 0，因为 COUNT(*) 空集为 0；后者用 COALESCE 补 0）。用户 10 行时无感，用户 100 万行时差距是秒级与毫秒级。

讲清楚要点

1. 三类子查询三类用途：标量子查询当值（必须单行单列）、列子查询当集合（配 IN/ANY/ALL）、派生表当临时表（必须别名）。选型的第一问永远是「我需要的是一个数、一组数，还是一张中间表」。
2. 相关子查询逐行驱动：内层引用了外层列时，外层每行触发一次内层执行。它表达力最强但伸缩性最差——驱动行数大时优先改写为 JOIN + 预聚合，或用窗口函数（第 7 课）。
3. EXISTS 是存在性判断的规范写法：内层习惯写 SELECT 1（写 * 也没错，优化器不看列），找到即停；NOT EXISTS 不受 NULL 影响，是「查不存在」的安全选择。
4. NOT IN + NULL = 空结果：这是 SQL 三值逻辑最著名的陷阱。规则记成一句话：只要 NOT IN 的列表里可能混入 NULL，就不要用 NOT IN，改 NOT EXISTS 或过滤 NULL。
5. IN 大集合的优化：MySQL 8 的优化器会把 IN (SELECT ...) 自动转成半连接（semi-join）执行，通常已足够快；但外层小表、内层超大集合时，EXISTS 或改 JOIN 的计划更稳——看 EXPLAIN 说话（第 11 课）。
6. 嵌套超过两层的子查询该换成 CTE：WITH 子句给每一步命名，逻辑从「嵌套洋葱」变成「流水线」，可读性和可维护性都质变——这正是下一课的主角。

你可能会懵的地方

• *EXISTS 里写 SELECT 1 还是 SELECT ？ 都行。优化器只判断「有没有行返回」，不看 SELECT 什么。写 SELECT 1 是约定俗成的「我在判断存在性」的可读性标记。

• IN 和 EXISTS 到底谁快？ 没有绝对答案：8.0 优化器对 IN 会做半连接转换，多数场景两者计划相同。经验法则是「外表小内表大用 EXISTS，外表大内表小用 IN」，但正确做法永远是 EXPLAIN 验证，不靠背口诀。

• 标量子查询返回了多行怎么办？ ERROR 1242。要么给内层加 LIMIT 1 + 明确排序（表达「取最新一条」的业务语义），要么承认你需要的其实是 JOIN 或派生表。

• WHERE 里的子查询能用 LIMIT 吗？ IN (SELECT ... LIMIT 1) 这种子查询内 LIMIT 在 MySQL 里不被支持（ERROR 1235），先把子查询包成派生表再用 LIMIT；EXISTS 子查询不带 LIMIT 本来就会短路，不需要。

• 子查询能写 DML 吗？ DELETE FROM t WHERE id IN (SELECT ...) 里若内层查的是同一张表会报 ERROR 1093，把内层包成派生表即可绕过（优化器会物化它）。

错法对照

错法	问题	正解
NOT IN (SELECT col ...) 且列可能为 NULL	整个查询返回空集	NOT EXISTS 或内层过滤 col IS NOT NULL
标量子查询没保证单行	ERROR 1242 运行时炸	加 ORDER BY ... LIMIT 1 或改 JOIN
派生表不写别名	ERROR 1248	FROM (...) AS t
大表上用相关子查询算计数	逐行执行，秒级变小时级	JOIN 预聚合或窗口函数
五层嵌套子查询	自己都读不懂	拆成带名字的 CTE（下一课）

卡点急救

1. ERROR 1242 Subquery returns more than 1 row：确认内层查询的行数预期；需要「任取一条」就明确 ORDER BY x LIMIT 1，需要多值就改 IN/JOIN。
2. ERROR 1248 Every derived table must have its own alias：给 FROM 里的子查询补 AS 名字。
3. ERROR 1093 You can't specify target table for update in FROM clause：同一张表又删又查，把子查询包一层派生表：WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE ...) x)。

面试常问

• IN 和 EXISTS 的区别与选择？ 语义上 IN 是成员匹配、EXISTS 是存在性短路；性能上 8.0 优化器会把 IN 转半连接，多数场景等价。关键差异在 NULL：NOT IN 列表含 NULL 返回空集，NOT EXISTS 不受影响。「查不存在」永远优先 NOT EXISTS。

• 什么是相关子查询？有什么问题？ 内层查询引用外层列，导致外层每行都触发一次内层执行，复杂度接近 O(外层行数 × 内层执行)。优化方向：JOIN 预聚合、窗口函数，或让驱动集变小。

• 如何找出每个部门工资最高的员工（不用窗口函数）？ 经典相关子查询写法：WHERE salary = (SELECT MAX(salary) FROM emp e2 WHERE e2.dept = e.dept)。若面试官追问数据量大的优化，答窗口函数 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 取 rn=1。

自测三问

1. 为什么 x NOT IN (1, NULL) 永远不为 TRUE？用三值逻辑展开说明。
2. 标量子查询、列子查询、派生表各自的要求和典型位置是什么？
3. 把本课步骤 6 的慢形态改写成窗口函数，你会怎么写？

小结一句话

子查询是「先算中间结果再消费」的表达工具——标量当一个值、IN 当一个集合、派生表当一张临时表；NOT IN 遇 NULL 必炸，查不存在一律 NOT EXISTS。

延伸

• MySQL 8.0 Reference Manual — Subqueries
• 下一课：窗口函数与 CTE——MySQL 8 最重要的新能力，明细与聚合从此不必二选一。