子查询

这一课你将能做到

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 陷阱（必看）