第 10 章 子查询与高级SQL面试精讲

📖 本篇简介：子查询和高级 SQL 是手写题的高频区，也是 SQL 能力从"会写"到"写得好"的关键。本篇用 16 道高频题讲清子查询分类、IN/EXISTS/ANY/ALL、NOT IN 遇 NULL 翻车、相关子查询、派生表、8.0 CTE、ROLLUP/GROUPING SETS、INSERT ON DUPLICATE KEY UPDATE、REPLACE vs INSERT IGNORE、UNION vs UNION ALL，以及多表 UPDATE/DELETE。学完本篇，你能在白板上写出"TopN 每组前 3 名""去重插入""复杂报表"等高频手写题，并避开 NOT IN 坑。前置知识：JOIN、聚合、GROUP BY；本系列示例基于 MySQL 8.0。

目录

• 1 子查询与高级 SQL 高频考点地图
• 题目 1什么是子查询有哪些分类
• 题目 2IN ANY ALL SOME 的区别
• 题目 3相关子查询和非相关子查询的区别
• 题目 4IN 和 EXISTS 哪个快
• 题目 5NOT IN 为什么遇到 NULL 会出问题
• 题目 6子查询和 JOIN 哪个性能好
• 题目 7FROM 里的子查询派生表是什么
• 题目 880 CTE 公用表表达式 WITH 怎么用
• 题目 9GROUP BY 高级ROLLUP GROUPING SETS 怎么用
• 题目 10HAVING 和 WHERE 的区别
• 题目 11INSERT ON DUPLICATE KEY UPDATE 怎么用
• 题目 12REPLACE INTO 和 INSERT IGNORE 的区别
• 题目 13UNION 和 UNION ALL 的区别
• 题目 14多表 UPDATEDELETE 怎么写
• 题目 15CASE WHEN 怎么用做行列转换
• 题目 16窗口函数预告ROWNUMBER 和 RANK 怎么选
• 高级 SQL 速查表
• 常见坑与注意事项
• 复习清单
• 练习题

1 子查询与高级 SQL 高频考点地图

| 难度 | 典型问题 | 本篇对应 |

|---|---|---|

| 入门 | 子查询分类？IN vs EXISTS？ | 题目 1、2、4 |

| 基础 | NOT IN 坑？HAVING？UNION？ | 题目 5、10、13 |

| 进阶 | CTE？ROLLUP？UPSERT？ | 题目 8、9、11 |

| 实战 | 多表 UPDATE？行列转换？ | 题目 14、15 |

一条主线记住：8.0 优化器很强，子查询很多会被改写成 JOIN；手写题重点是 NOT IN 坑、UPSERT、CTE、ROLLUP。

题目 1：什么是子查询？有哪些分类？

考点

• 嵌套 SELECT；
• 按返回值分：标量/列/行/表；
• 按位置分：WHERE/FROM/SELECT。

参考答案

• 子查询：嵌套在另一个 SQL 里的 SELECT；
• 按返回值分：

| 类型 | 返回 | 示例 |

|---|---|---|

| 标量子查询 | 单值 | (SELECT MAX(price) FROM ...) |

| 列子查询 | 一列多行 | id IN (SELECT ...) |

| 行子查询 | 一行多列 | (a,b) = (SELECT ...) |

| 表子查询 | 多行多列 | FROM (SELECT ...) t |

• 按位置分：WHERE 子查询、FROM 子查询（派生表）、SELECT 列表子查询。

-- 标量子查询：查比平均价高的商品
SELECT * FROM goods WHERE price > (SELECT AVG(price) FROM goods);
-- 列子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status=1);

追问

• 问：SELECT 列表里的子查询叫什么？答：标量子查询相关写法，每行执行一次，性能差，能改 JOIN 就改。
• 问：子查询最多嵌套几层？答：SQL 标准没限，MySQL 实际能嵌套很深，但可读性差，业务控制在 2~3 层。

题目 2：IN / ANY / ALL / SOME 的区别？

考点

• IN = 等于集合任一；
• ANY/SOME 与比较符搭配；
• ALL = 与集合所有比较。

参考答案

| 运算符 | 含义 |

|---|---|

| IN | 等于子查询结果中任一值 |

| = ANY / = SOME | 等价 IN |

| > ALL | 大于集合中最大值 |

| < ALL | 小于集合中最小值 |

| > ANY | 大于集合中任一（即大于最小值） |

-- 查价格比任一北京商品高的商品
SELECT * FROM goods WHERE price > ANY (
  SELECT price FROM goods WHERE city='北京'
);
-- 查价格比所有北京商品都高的商品
SELECT * FROM goods WHERE price > ALL (
  SELECT price FROM goods WHERE city='北京'
);

追问

• 问：SOME 和 ANY 一样吗？答：一样，SOME 是 ANY 的别名。
• 问：> ANY 和 > ALL 哪个容易？答：> ANY 只要大于最小值；> ALL 要大于最大值。

题目 3：相关子查询和非相关子查询的区别？

考点

• 非相关：子查询独立执行一次；
• 相关：子查询引用外表列，每行执行；
• 性能差异。

参考答案

• 非相关子查询：子查询不引用外层表，独立执行一次，结果缓存；
• 相关子查询：子查询引用外层表的列，外层每一行都执行一次子查询，性能差；
• 优化器会尽量把相关子查询改写成 JOIN/Semi Join。

-- 非相关：子查询独立
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 相关：子查询引用 o.user_id，外层每行执行一次
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status=1);

追问

• 问：相关子查询一定慢吗？答：8.0 Semi Join 优化后通常不慢；老版本要手工改 JOIN。
• 问：EXISTS 是相关子查询吗？答：是经典的相关子查询写法。

题目 4：IN 和 EXISTS 哪个快？

考点

• 外表小/内表小；
• 8.0 优化器等价；
• 历史经验。

参考答案

• 老经验：
• 外表小、内表大：用 IN（内表建索引）；
• 外表大、内表小：用 EXISTS（外表每行探测内表）；
• 现代 MySQL（8.0）优化器会把 IN/EXISTS 都改写成 Semi Join，性能基本等价；
• 写的时候可读性优先，别过度纠结。

-- IN 写法
SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders WHERE status=1);
-- EXISTS 写法
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.status=1
);
-- 8.0 EXPLAIN 两者都是 Semi join

追问

• 问：NOT IN 和 NOT EXISTS 哪个好？答：NOT IN 遇到 NULL 翻车（题目 5），生产优先 NOT EXISTS 或 LEFT JOIN ... IS NULL。
• 问：IN 子查询结果集多大合适？答：百万级以内；太大改 JOIN。

题目 5：NOT IN 为什么遇到 NULL 会出问题？

考点

• NULL 三值逻辑；
• NOT IN (..., NULL) 结果为空；
• 经典坑。

参考答案

• SQL 三值逻辑：= NULL 结果是 UNKNOWN，不是 TRUE/FALSE；
• x NOT IN (1, 2, NULL) 等价 x<>1 AND x<>2 AND x<>NULL，其中 x<>NULL 是 UNKNOWN，整个 AND 变 UNKNOWN，WHERE 只保留 TRUE，结果集为空；
• 经典翻车：子查询里某列有 NULL，NOT IN 直接查不出数据。

-- 子查询 user_id 里有 NULL，NOT IN 查不到任何用户
SELECT * FROM users WHERE id NOT IN (
  SELECT user_id FROM orders   -- 如果 user_id 有 NULL
);
-- 修复 1：子查询里过滤 NULL
SELECT * FROM users WHERE id NOT IN (
  SELECT user_id FROM orders WHERE user_id IS NOT NULL
);
-- 修复 2：改用 NOT EXISTS
SELECT * FROM users u WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id=u.id
);

追问

• 问：为什么会这样？答：SQL 把 NULL 当"未知"，任何与 NULL 的比较结果都是未知，WHERE 未知行被丢弃。
• 问：业务怎么避免？答：关键字段尽量 NOT NULL；写 NOT IN 永远加 IS NOT NULL 或用 NOT EXISTS。

题目 6：子查询和 JOIN 哪个性能好？

考点

• 8.0 优化器改写；
• 可读性；
• 实践建议。

参考答案

• 8.0 优化器会把子查询自动改写成 JOIN/Semi Join，多数情况性能等价；
• 老版本/复杂子查询可能改写失败，手工改 JOIN 更稳；
• 实践建议：

1. 简单过滤用 IN/EXISTS 可读性好；

2. 要拼右表列用 JOIN；

3. 性能疑问一律 EXPLAIN 验证。

-- 子查询写法
SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders);
-- 等价 JOIN 写法
SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id=o.user_id;

追问

• 问：SELECT 列表里的子查询呢？答：每行执行一次，性能差，改 JOIN。
• 问：JOIN 一定比子查询快吗？答：不一定，要看数据分布和索引；EXPLAIN 说话。

题目 7：FROM 里的子查询（派生表）是什么？

考点

• derived table；
• 必须起别名；
• 8.0 合并优化。

参考答案

• 派生表（derived table）：FROM (SELECT ...) t 里的子查询，必须起别名；
• 5.7 之前派生表会物化成临时表，性能差；
• 8.0 优化器会合并派生表（merge）到外层查询，等价 JOIN；
• 用途：复杂计算先聚合再 JOIN。

-- 先算每个用户订单数，再 JOIN 用户表
SELECT u.name, c.cnt
FROM users u
JOIN (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) c
  ON u.id = c.user_id;

追问

• 问：派生表能加索引吗？答：8.0 物化派生表会自动建索引；合并后无此问题。
• 问：派生表为什么必须起别名？答：SQL 标准要求，FROM 里的子查询必须是一个"表"，要有名字。

题目 8：8.0 CTE 公用表表达式 WITH 怎么用？

考点

• WITH 语法；
• 非递归 CTE；
• 递归 CTE；
• 可读性优于派生表。

参考答案

• CTE（Common Table Expression）：8.0 引入 WITH 语法，定义临时结果集；
• 比派生表可读、可引用多次；
• 分非递归和递归。

-- 非递归 CTE
WITH order_stat AS (
  SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
  FROM orders GROUP BY user_id
)
SELECT u.name, s.cnt, s.total
FROM users u JOIN order_stat s ON u.id=s.user_id;

-- 递归 CTE：生成 1~10 的序列
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM seq WHERE n < 10
)
SELECT * FROM seq;

追问

• 问：CTE 和临时表区别？答：CTE 是查询级，语句结束就释放；临时表是会话级，可重复用。
• 问：递归 CTE 有什么用？答：树形结构（菜单、组织架构）遍历。

题目 9：GROUP BY 高级：ROLLUP / GROUPING SETS 怎么用？

考点

• 多维汇总；
• ROLLUP 小计；
• 报表常用。

参考答案

• ROLLUP：GROUP BY 时自动生成各级小计和总计；
• GROUPING SETS：自定义要汇总的维度组合；
• 报表常用，不用写 UNION 拼多次 GROUP BY。

-- 按城市+品类汇总，并自动加小计和总计
SELECT city, category, SUM(amount) AS total
FROM sales
GROUP BY city, category WITH ROLLUP;
-- 输出：
-- 北京  电子  100
-- 北京  NULL   100   ← 北京小计（category 为 NULL）
-- 上海  电子   80
-- 上海  NULL    80
-- NULL NULL   180   ← 总计

追问

• 问：怎么区分 NULL 是真数据还是小计？答：GROUPING(city) 函数返回 1 表示是小计行。
• 问：8.0 支持 GROUPING SETS 吗？答：MySQL 8.0 支持 GROUP BY ... GROUPING SETS / CUBE / ROLLUP 全套。

题目 10：HAVING 和 WHERE 的区别？

考点

• WHERE 行过滤；
• HAVING 分组后过滤；
• 聚合函数只能在 HAVING。

参考答案

• WHERE：分组前过滤行，不能用聚合函数；
• HAVING：GROUP BY 后过滤分组结果，可以用聚合函数；
• 执行顺序：WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。

-- 查订单数超过 100 的城市
SELECT city, COUNT(*) AS cnt
FROM orders
WHERE status = 1              -- 先过滤已支付
GROUP BY city
HAVING COUNT(*) > 100;        -- 再过滤分组

追问

• 问：能用 WHERE 替代 HAVING 吗？答：聚合函数结果不能放 WHERE，必须 HAVING。
• 问：HAVING 里能用别名吗？答：MySQL 支持 HAVING cnt > 100，其他数据库不一定。

题目 11：INSERT ... ON DUPLICATE KEY UPDATE 怎么用？

考点

• UPSERT；
• 依赖唯一键/主键；
• 幂等写入。

参考答案

• UPSERT：插入时如果主键/唯一键冲突，自动改 UPDATE；
• 幂等写入经典方案（点赞计数、累计报表）；
• 依赖唯一索引/主键。

-- 阅读数累加：同一文章 id 存在就 +1，不存在就插入
INSERT INTO article_counter(article_id, cnt)
VALUES (123, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

-- 也可以用 VALUES() 引用拟插入的值
INSERT INTO t(a,b) VALUES(1,10),(2,20)
ON DUPLICATE KEY UPDATE b = VALUES(b);

追问

• 问：和 REPLACE INTO 区别？答：REPLACE 是先 DELETE 再 INSERT（主键全变、外键受影响）；ON DUPLICATE 是原地 UPDATE，更优。
• 问：8.0 还有新写法吗？答：8.0.20+ 支持 INSERT ... AS new ON DUPLICATE KEY UPDATE b=new.b，不用 VALUES()。

题目 12：REPLACE INTO 和 INSERT IGNORE 的区别？

考点

• REPLACE 删旧插新；
• INSERT IGNORE 冲突跳过；
• ON DUPLICATE 是第三种选择。

参考答案

| 写法 | 冲突时行为 |

|---|---|

| INSERT | 报错 |

| INSERT IGNORE | 跳过冲突行，不报错 |

| REPLACE INTO | 先 DELETE 旧行，再 INSERT 新行 |

| ON DUPLICATE KEY UPDATE | 原地 UPDATE |

• REPLACE：主键全变（自增 ID 变）、外键级联 DELETE，副作用大；
• INSERT IGNORE：冲突跳过，适合"幂等导入"。

-- 幂等导入：已存在就跳过
INSERT IGNORE INTO user(id, name) VALUES (1, 'a');
-- REPLACE：冲突就删旧插新
REPLACE INTO user(id, name) VALUES (1, 'b');

追问

• 问：REPLACE 为什么副作用大？答：它是 DELETE+INSERT，自增 ID 重新分配，触发器/外键会触发，线上慎用。
• 问：导入数据推荐用哪个？答：INSERT ... ON DUPLICATE KEY UPDATE，可控且高效。

题目 13：UNION 和 UNION ALL 的区别？

考点

• UNION 去重；
• UNION ALL 不去重；
• 性能差异。

参考答案

• UNION：合并结果并去重（内部临时表排序）；
• UNION ALL：直接合并，不去重；
• 业务上确认不会重复时永远用 UNION ALL，性能好很多。

-- 查北京和上海的用户
SELECT * FROM users WHERE city='北京'
UNION ALL
SELECT * FROM users WHERE city='上海';
-- 不会重复（两个城市互斥），用 UNION ALL

追问

• 问：UNION 去重怎么实现？答：对合并结果排序/临时表去重，大结果集慢。
• 问：UNION 各分支列数要一致吗？答：要，列类型兼容。

题目 14：多表 UPDATE/DELETE 怎么写？

考点

• UPDATE JOIN；
• DELETE JOIN；
• 注意事项。

参考答案

-- 多表 UPDATE：根据另一表条件更新
UPDATE users u
JOIN orders o ON u.id=o.user_id
SET u.total_order = o.cnt
WHERE o.status=1;

-- 多表 DELETE：删订单时关联删明细
DELETE o, oi FROM orders o
JOIN order_items oi ON o.id=oi.order_id
WHERE o.id=123;
-- DELETE o, oi 表示删两张表的行；只写 o 只删订单主表

• 注意：多表 UPDATE/DELETE 会锁多张表的行，小心死锁；
• 大表批量操作分批做。

追问

• 问：UPDATE 能改被 JOIN 的表吗？答：能，SET 后指定表别名。
• 问：多表 DELETE 不加别名会怎样？答：DELETE FROM orders ... 只删单表；DELETE orders, oi FROM ... 才删多表。

题目 15：CASE WHEN 怎么用？做行列转换？

考点

• CASE 表达式；
• 行列转换（pivot）；
• 条件聚合。

参考答案

• CASE WHEN ... THEN ... ELSE ... END 是 SQL 里的 if-else；
• 经典用法：行转列（pivot）、条件聚合。

-- 行列转换：把每个用户的各月订单数转成列
SELECT user_id,
       SUM(CASE WHEN MONTH(created_at)=1 THEN 1 ELSE 0 END) AS m1,
       SUM(CASE WHEN MONTH(created_at)=2 THEN 1 ELSE 0 END) AS m2,
       SUM(CASE WHEN MONTH(created_at)=3 THEN 1 ELSE 0 END) AS m3
FROM orders
WHERE YEAR(created_at)=2026
GROUP BY user_id;

追问

• 问：CASE 有简单 CASE 和搜索 CASE 两种？答：CASE x WHEN 1 THEN ... END 是简单 CASE；CASE WHEN x=1 THEN ... END 是搜索 CASE。
• 问：行列转换 MySQL 比 PostgreSQL 差？答：MySQL 没有内置 PIVOT，靠 CASE WHEN 手写；8.0 窗口函数辅助。

题目 16：窗口函数预告：ROW_NUMBER 和 RANK 怎么选？

考点

• 窗口函数 8.0 支持；
• ROW_NUMBER/RANK/DENSE_RANK 区别；
• 下一篇详讲。

参考答案

• 窗口函数是 8.0 重磅特性，本篇点到为止，下一篇详讲；
• 三个高频排名函数：

| 函数 | 相同值排名 | 下一名 |

|---|---|---|

| ROW_NUMBER | 不重复，强制 1,2,3 | 紧接 |

| RANK | 并列 1,1,3 | 跳号 |

| DENSE_RANK | 并列 1,1,2 | 不跳号 |

-- 每组订单按金额排名（预览）
SELECT user_id, amount,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;
-- 取每组前 3 名：WHERE rn <= 3（包一层子查询/CTE）

追问

• 问：为什么需要窗口函数？答：TopN、累计、移动平均等场景，以前要写复杂相关子查询，现在一行搞定。
• 问：5.7 能用窗口函数吗？答：不能，8.0 才有，老版本要升级。

高级 SQL 速查表

| 写法 | 用途 |

|---|---|

| IN (subquery) | 列匹配集合 |

| EXISTS | 半连接判断 |

| NOT IN (NULL) | 坑！结果空 |

| WITH x AS (...) | CTE 临时结果集 |

| GROUP BY ... WITH ROLLUP | 自动小计总计 |

| INSERT ... ON DUPLICATE KEY UPDATE | UPSERT |

| INSERT IGNORE | 冲突跳过 |

| REPLACE INTO | 删旧插新（慎用） |

| UNION ALL | 合并不去重 |

| CASE WHEN | 条件表达式/行列转换 |

| ROW_NUMBER() OVER (...) | 排名（下一篇详） |

| 运算符 | 含义 |

|---|---|

| > ALL | 大于最大值 |

| > ANY | 大于最小值 |

| = ANY | 等于任一（= IN） |

常见坑与注意事项

1. NOT IN 子查询里有 NULL：结果集直接空，业务翻车。

2. UNION 当 UNION ALL 用：大结果集去重临时表慢。

3. REPLACE INTO 当 UPSERT：删旧插新，自增 ID 变，外键级联。

4. 相关子查询没索引：外层每行执行一次，慢到爆。

5. WHERE 里写聚合函数：语法错，必须 HAVING。

6. 多表 UPDATE 没分批：大表一次性锁全表。

7. CASE WHEN 漏 ELSE：不匹配的行返回 NULL，业务要注意。

8. INSERT ON DUPLICATE 没唯一键：不触发 UPDATE，变成纯插入。

复习清单

| 题目 | 核心结论 | 易错点 |

|---|---|---|

| 子查询分类 | 标量/列/行/表 | 漏行子查询 |

| ANY/ALL | > ALL=大于最大值 | 和 ANY 混淆 |

| 相关子查询 | 引用外表列 | 性能差 |

| IN vs EXISTS | 8.0 等价 | 老经验别死记 |

| NOT IN 坑 | 遇 NULL 结果空 | 必背 |

| CTE | WITH 语法 | 递归 CTE 语法 |

| ROLLUP | 自动小计 | NULL 含义 |

| HAVING | 分组后过滤 | WHERE 用聚合 |

| UPSERT | ON DUPLICATE | 依赖唯一键 |

| UNION | 去重慢 | 该用 ALL |

| REPLACE | 删旧插新 | 别当 UPSERT |

| CASE WHEN | 行列转换 | 漏 ELSE |

练习题

练 1：SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders) 查不出任何数据，但 orders 表明明有数据。最可能原因？怎么修？

<details>

<summary>参考答案</summary>

orders.user_id 列里有 NULL。NOT IN (..., NULL) 等价 id<>1 AND id<>2 AND ... AND id<>NULL，id<>NULL 是 UNKNOWN，整个 WHERE 不成立，结果空。修复：

SELECT * FROM users WHERE id NOT IN (
  SELECT user_id FROM orders WHERE user_id IS NOT NULL
);
-- 或用 NOT EXISTS
SELECT * FROM users u WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id=u.id
);

</details>

练 2：业务要实现"文章阅读数累加"，并发高，怎么写 SQL？

<details>

<summary>参考答案</summary>

用 UPSERT：

INSERT INTO article_counter(article_id, cnt)
VALUES (123, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

article_id 建唯一主键/唯一索引。这条 SQL 原子，并发安全，不需要先 SELECT 再 UPDATE。

</details>

练 3：用 8.0 CTE 写 SQL：查每个城市订单数 Top3 的用户。

<details>

<summary>参考答案</summary>

WITH order_rk AS (
  SELECT u.city, u.id, u.name, COUNT(*) AS cnt,
         ROW_NUMBER() OVER (PARTITION BY u.city ORDER BY COUNT(*) DESC) AS rn
  FROM users u JOIN orders o ON u.id=o.user_id
  GROUP BY u.city, u.id, u.name
)
SELECT city, id, name, cnt
FROM order_rk WHERE rn <= 3;

窗口函数下一篇详讲，这里先感受 CTE + 窗口函数的组合威力。

</details>

下一篇预告：第 11 章 窗口函数面试精讲