5.3 多表链、自连接与三个连接事故

5.3 多表链、自连接与三个连接事故

本节摘要：真实查询常常要连三张以上的表——订单要拼顾客、拼明细、拼图书，形成连接链；分类树与员工汇报线则是"自己连自己"的自连接。链条越长，事故越隐蔽：重复计数让销售额虚高三倍、连接条件错位让数据张冠李戴、外连接的条件写错位置悄悄退化成内连接。本节先学怎么连，再学三起事故的现场勘查。

连接链：从订单到书名的三级拼图

报表需求："每笔订单的顾客昵称、买了哪些书、订单金额。"数据分散在四张表：orders 有顾客编号没有昵称，order_items 有书号没有书名。连接链顺势展开：

-- 三级连接链：订单 → 明细 → 图书，另拼顾客
SELECT
  o.id           AS 订单号,
  cu.name        AS 顾客,
  b.title        AS 书名,
  oi.quantity    AS 数量
FROM orders o
JOIN customers   cu ON o.customer_id = cu.id     -- 第一级：订单拼顾客
JOIN order_items oi ON oi.order_id = o.id        -- 第二级：订单拼明细
JOIN books       b  ON b.id = oi.book_id         -- 第三级：明细拼图书
ORDER BY o.id, b.title;
​

+-----------+--------+-----------------------+--------+
| 订单号    | 顾客   | 书名                  | 数量   |
+-----------+--------+-----------------------+--------+
| 201 | 陈晓   | 算法导论              |      1 |
| 201 | 陈晓   | 数据库系统概念        |      2 |
| 202 | 林川   | SQL必知必会           |      3 |
+-----------+--------+-----------------------+--------+
​

多表链的写法要点：每张表起一个见名知义的短别名（cu、oi、b），每个 JOIN 配一个 ON，ON 紧跟在它所属的 JOIN 后面——不要把三个条件堆到最后一个 ON 里，可读性会立刻崩塌。别名是连接链的路标，起名随意的 t1、t2、t3 在三层之后就开始害人。

链条的"宽度"由基数决定：订单对明细是一对多，订单对顾客是多对一。一对多的方向上，行数会逐级放大——订单 201 有两条明细，拼完明细变成两行。放大本身正常，但它埋着本节第一起事故。

自连接：一张表当两张用

分类树是"子分类指向父分类"的自指结构（上一节 ER 模型里见过），想要"分类名 + 父分类名"同屏，得让 categories 连它自己：

-- 自连接：同一张表取两个别名 当两张表连
SELECT
  child.name  AS 分类,
  parent.name AS 父分类
FROM categories child
LEFT JOIN categories parent
  ON child.parent_id = parent.id
ORDER BY 父分类, 分类;
​

+-----------+-----------+
| 分类      | 父分类    |
+-----------+-----------+
| 数据库    | 计算机    |
| 算法      | 计算机    |
| 计算机    | NULL      |
| 文学      | NULL      |
+-----------+-----------+
​

自连接的全部秘密在别名：child 与 parent 是同一张表的两个"扮演者"，各自戴上名字牌，数据库就当两张表来连。LEFT JOIN 让顶层分类（父分类为 NULL）也保留，父分类列显示 NULL。员工与上级的查询（employees.manager_id 指向 employees.id）完全同构。

自连接只能展开一层父子关系。想要"祖先链"——从"数据库"一路追溯到"计算机"再到顶层——一层连接无能为力，这正是第 8 章递归 CTE 的戏份。技术上还有个隐患：若数据里不慎出现循环（A 的父是 B，B 的父是 A），自连接只展开一层看不出问题，递归查询却会无限转圈——建表时给自指外键加约束或在导入时校验无环，是设计期的责任。

三起连接事故的现场勘查

事故一：重复计数。 现象：按顾客统计销售额，总额比财务系统高三倍。勘查现场：

-- 事故现场：顾客销售额 LEFT JOIN 明细后直接 SUM
SELECT
  cu.name,
  SUM(oi.quantity * oi.unit_price) AS 销售额
FROM customers cu
LEFT JOIN orders      o  ON o.customer_id = cu.id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY cu.name;
​

+--------+--------------+
| name   | 销售额       |
+--------+--------------+
| 陈晓   | 305.00       |   ← 正确值也是 305，见下方说明
| 林川   | 156.00       |
+--------+--------------+
​

这组数据恰好没翻车，因为每个订单的明细互不重叠。翻车条件是连接链上有两个一对多的分叉：比如订单既连明细又连配送记录（一单多条配送流水），两条明细 × 两条流水 = 四行，SUM 里的数量被复制了两份。演示这个机制：

-- 机制演示：一条明细与两条配送流水相乘 行数翻倍
SELECT 明细.编号, 流水.金额 FROM 明细
JOIN 流水 ON 明细.订单号 = 流水.订单号;
-- 明细 2 行 × 流水 2 行 → 输出 4 行，2 号明细出现两次
​

+----------+--------+
| 编号     | 金额   |
+----------+--------+
| 1        |    20  |
| 2        |    20  |
| 2        |    35  |
+----------+--------+
​

预防与修复：聚合先分后连——先用子查询（或第 8 章的 CTE）把每个订单的明细聚合好，再连接汇总结果；或者让连接链保持"线性"，避免同一层级挂两个一对多。看到 SUM 结果异常，第一反应数一下连接后的行数是否大于任一单表。

事故二：连接条件错位。 现象：报表里书名和单价对不上，《算法导论》显示 49 元。勘查发现连接条件写串了表：

-- 事故现场：第三级的 ON 里误写了订单号 = 书号
SELECT b.title, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN books b        ON b.id = o.id;      -- 错位：把订单号当书号匹配
​

Query OK（能执行！），但书名与订单纯属乱点鸳鸯谱
凡是恰好同号的订单与书被拼成一行，业务上完全错误
​

这类事故的可怕在于能跑通——只要两列类型相容（这里都是整数），数据库无条件服从。防线是习惯：ON 里的相等关系永远写成"外键 = 另一边的主键"，写完逐条念一遍"这个外键指向哪张表的主键"。

事故三：外连接被 WHERE 废掉武功。 现象：想列"所有分类及书数（含零本）"，结果空分类消失了：

-- 事故现场：右表条件写进了 WHERE
SELECT c.name, COUNT(b.id) AS 书目数
FROM categories c
LEFT JOIN books b ON b.category_id = c.id
WHERE b.price > 60            -- 坑：对右表列的过滤放到了 WHERE
GROUP BY c.name;
​

+-----------+-----------+
| name      | 书目数    |
+-----------+-----------+
| 算法      |         2 |
| 数据库    |         1 |   ← 文学 消失了
+-----------+-----------+
​

空分类那一行的 b.price 是 NULL，NULL > 60 的结果是未知（第 3 章三值逻辑），未知过不了 WHERE——LEFT JOIN 辛苦保下来的行又被 WHERE 扔了，外连接名存实亡。正确位置是把右表条件挪进 ON：

-- 正解：右表条件放 ON，只影响匹配 不影响左表保留
SELECT c.name, COUNT(b.id) AS 书目数
FROM categories c
LEFT JOIN books b ON b.category_id = c.id
                 AND b.price > 60    -- 匹配时就不考虑高价书
GROUP BY c.name;
​

+-----------+-----------+
| name      | 书目数    |
+-----------+-----------+
| 算法      |         2 |
| 数据库    |         1 |
| 文学      |         0 |   ← 回来了
+-----------+-----------+
​

口诀：左表的筛选取舍写 WHERE，右表的匹配条件写 ON（内连接两者等价，外连接天差地别）。

图 5-3 三起事故对照卡

⚠️ 常见坑：以为查询能执行、结果有数字就是对的。三起事故全部"能跑"。连接结果的正确性要用业务常识抽查：随机挑两三行，沿连接链手工核对每列的来源，这是老手提交前的固定动作。

💡 关键直觉：JOIN 是按条件做行的笛卡尔配对，条件写得越准，配对越干净。写完连接先 SELECT 数行数——行数不符合直觉的预期，就是事故在敲门。

本节要点回顾

• 多表链逐级拼：每 JOIN 配一个 ON 紧随其后，别名见名知义，链的行数随一对多方向放大；
• 自连接靠别名：一张表扮演两个角色，LEFT JOIN 保住无父节点的顶层；环状数据会在递归查询里爆炸，导入期要校验；
• 重复计数：同级两个一对多分叉是成因，先分后连（预聚合再连接）是处方；
• 条件错位：外键必须指向对应主键，"能跑通"不等于"配对对"；
• 外连接退化：右表条件进 WHERE 会把 NULL 行拦掉，右表条件放 ON；
• 抽查习惯：随机几行沿连接链手工溯源，比任何工具都快地暴露张冠李戴。