SQL 查询：连接、聚合与子查询

一句话定义

DQL（数据查询语言）的核心是把连接（JOIN）拼出数据、用聚合（GROUP BY + 聚合函数）压缩数据、用子查询（Subquery）与 CTE 分层表达逻辑，三者构成日常 90% 的分析型 SQL。

为什么重要

几乎所有报表、对账、风控规则都以「连接 + 聚合」为骨架，这是数据库岗位的硬技能。
连接写法直接决定执行代价：错用 LEFT JOIN 后再 WHERE 过滤、或在连接列上隐式类型转换，都可能把索引查询变成全表扫描。
子查询与 CTE 的正确选择影响可读性与优化器行为，是代码评审的高频议题。

前置知识

kp-002（关系代数）、kp-003（可操作的表结构）。

核心概念

内连接（INNER JOIN）：只保留两表匹配行；外连接（LEFT/RIGHT/FULL JOIN）：保留一侧全部行，未匹配侧填 NULL。
分组聚合：GROUP BY 把行分成组，COUNT/SUM/AVG/MIN/MAX 在组内聚合；聚合后过滤用 HAVING。
子查询：出现在 WHERE/FROM/SELECT 中的嵌套查询；EXISTS 只判断存在性，通常比 IN 大列表更稳。
逻辑执行顺序：FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。
每行聚合：窗口函数可对「组内每一行」输出结果（详见 kp-005）。

原理与机制

连接的实现有三种物理算法：嵌套循环（Nested Loop，外层每行探测内层索引，适合小驱动集）、哈希连接（Hash Join，小表建哈希表、大表探测，适合无索引的大表等值连接）、归并连接（Sort-Merge，两输入有序时归并，适合大规模有序数据）。优化器基于代价在三者间选择，因此「连接列上有没有索引、数据量比例多少」比 SQL 写法本身更影响性能。聚合的机制是维护分组状态（计数器、累加器）；HAVING 在分组完成后过滤组，而 WHERE 在分组前过滤行——两者写错位置是语义错误。子查询会被优化器改写（如 IN 子查询去重后转半连接 Semi-Join），但改写能力因库而异，复杂相关子查询可能退化为逐行执行。

实例或案例

「每个用户的订单数与最近下单时间，只保留下单 ≥ 2 次的用户，按订单数倒序」：

SELECT u.user_id,
       u.name,
       COUNT(o.order_id)      AS order_cnt,
       MAX(o.created_at)      AS last_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.name
HAVING COUNT(o.order_id) >= 2
ORDER BY order_cnt DESC
LIMIT 20;

若把 HAVING 的条件误写进 WHERE，聚合还没发生就直接报错——因为 WHERE 阶段尚无聚合值。

常见误区

对 LEFT JOIN 结果再做 WHERE o.col = x，把外连接悄悄变成内连接：过滤右表条件应写在 ON 里（或显式接受退化）。
COUNT(*) 与 COUNT(col) 混用：前者计行数，后者不计 NULL，统计口径不同。
用 SELECT DISTINCT 掩盖连接产生的重复：应先理解为什么出现重复（一对多扇出），而不是事后去重。

自测题

WHERE 与 HAVING 的本质区别？答：WHERE 在分组前过滤行，不能使用聚合函数；HAVING 在分组后过滤组，可以使用聚合结果。
什么场景哈希连接优于嵌套循环？答：两表都较大且连接列缺少可用索引时，哈希连接一次构建、一次探测，代价近似线性；嵌套循环无索引会退化为笛卡尔积量级的扫描。

公式或模型

嵌套循环代价 ≈ N_outer + N_outer × cost_probe（cost_probe 有索引时近似 log 级，无索引为 N_inner）；哈希连接代价 ≈ N_outer + N_inner + 构建哈希表成本。优化器据此与统计信息估算选路（详见 kp-013）。

图示

本节不适用：三种连接算法的数据流适合用文字加代价公式描述，本库在 kp-012 以真实执行计划树展示对应结构。

直观类比

LEFT JOIN 像点名：左表全员到齐，右表没来的留空位（NULL）；INNER JOIN 则是只留「配对成功」的人。GROUP BY 像把一堆发票按报销人分堆，然后每堆只报一个总数。

与其他知识点的关系

kp-005 在聚合之上引入窗口函数；kp-012 教你用 EXPLAIN 验证连接算法选择；kp-029 汇总连接相关的性能反模式。

前置知识点

相关知识点