卷 II · 取数CH 07深度 07/24

JOIN：把两个集合缝起来

JOIN 是关系数据库的看家本领——数据拆散在多张表里（这叫规范化），查询时再按需缝合。五种 JOIN 你大概都写过，但它们的差别其实只有一句话：没匹配上的行，怎么办。

五种 JOINON vs WHERENULL 补齐物理执行

连接，本质是「配对再筛选」

两张表 customers 和 orders，一个客户可以下多个订单。JOIN 做的事，理论上分两步：先把两张表的行两两配对（笛卡尔积），再按 ON 条件筛掉不匹配的。这是关系代数里的连接算子。

真正让人绕的，是「配对不上的行怎么处理」——这就是五种 JOIN 的分水岭：

  • INNER JOIN：只要配对成功的。落单的行，两边都丢。
  • LEFT JOIN：左表的行全保留；右边没配上的，用 NULL 补齐。
  • RIGHT JOIN：右表的行全保留；左边没配上的，用 NULL 补齐。
  • FULL JOIN：两边的行都保留，各自没配上的都用 NULL 补。
  • CROSS JOIN：不看条件，纯笛卡尔积，每一对都要。
▶ 动手 · 五种 JOIN 的真执行

下面是一台真的连接执行器。customers 有 4 个客户（其中 Cara、Dan 没下过单），orders 有 4 笔订单（其中一笔的 cust=5 指向一个不存在的客户）。挨个点五种 JOIN，看结果行数怎么变、NULL 补在哪（灰色斜纹行就是被 NULL 补齐的未匹配行）。

五种 JOIN 数出来的行数是 INNER 3、LEFT 5、RIGHT 4、FULL 6、CROSS 16——这些都是 demo 当场算的。把它们对照着理解：

  • INNER = 3：只有 Amy 的两单、Ben 的一单配对成功。Cara、Dan 没下单，那笔 cust=5 的订单没有对应客户——统统丢掉。
  • LEFT = 5：在 INNER 的 3 行基础上，把没下单的 Cara、Dan 也保留下来（订单列填 NULL）。3 + 2 = 5。
  • RIGHT = 4：保留所有订单，那笔 cust=5 的孤儿订单也留下（客户列填 NULL）。3 + 1 = 4。
  • FULL = 6：两边落单的都保留。3 + 2（Cara/Dan）+ 1（孤儿订单）= 6。
  • CROSS = 16：4 个客户 × 4 笔订单，不看条件，全配一遍。

看懂这五个数字，你就真正吃透了 JOIN。它们的差别不在「怎么配对」，而在「配不上的孤儿留不留、留下来拿什么填另一半」。

ON 和 WHERE：在外连接里，天壤之别

对 INNER JOIN 来说，条件写在 ON 里还是 WHERE 里，结果一样。但对外连接（LEFT/RIGHT/FULL），这俩位置的语义完全不同，是个高频错误点。

-- A：条件在 ON 里 —— 影响「怎么配对」
SELECT c.name, o.item
FROM customers c
LEFT JOIN orders o ON c.id = o.cust AND o.item = 'Hobbit';

-- B：条件在 WHERE 里 —— 配完之后再筛
SELECT c.name, o.item
FROM customers c
LEFT JOIN orders o ON c.id = o.cust
WHERE o.item = 'Hobbit';

差别在哪？LEFT JOIN 保证左表每一行都留下。

  • A（条件在 ON）：所有客户都还在，只是只有下过 Hobbit 单的客户才配上了订单，其余客户订单列是 NULL。左表完整保留。
  • B（条件在 WHERE）：LEFT JOIN 先把没订单的客户用 NULL 补上，然后 WHERE o.item = 'Hobbit' 一执行，那些 o.item 为 NULL 的行（也就是没下单的客户）全被筛掉了——因为 NULL = 'Hobbit' 不是 TRUE（还记得上一章吗）。结果 LEFT JOIN 被悄悄退化成了 INNER JOIN。

这是新手写外连接最常掉的坑：想过滤右表，却把条件写进了 WHERE，结果外连接白写了。规则：对右表的过滤条件，放 ON；对最终结果的过滤，放 WHERE。

自连接：一张表和自己 JOIN

JOIN 的两张表也可以是同一张。经典例子是员工和经理都在一张 employees 表里，mgr_id 指向同表另一行：

SELECT e.name AS 员工, m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.mgr_id = m.id;   -- 同一张表，两个别名

靠两个别名 e 和 m，把一张表当两张用。这也是第 10 章递归查询要解决的那类「层级 / 树」问题的起点——自连接只能查一层（员工和他的直接经理），要查整条汇报链，就得递归了。

同一句 JOIN，PostgreSQL 有三种执行手法

这里埋一个卷 V 的伏笔。上面 demo 里的连接结果，是逻辑结果——「应该得到哪些行」。但 PostgreSQL 真正执行时，有好几种物理手法可选，得到的行一样，快慢却天差地别：

  • 嵌套循环（Nested Loop）：对左表每一行，去右表找匹配。左表小、右表在连接列上有索引时最优。
  • 哈希连接（Hash Join）：把小表在内存里建成哈希表，大表扫一遍去撞。两张大表做等值连接时最优。
  • 归并连接（Merge Join）：两张表都按连接列排好序，像拉链一样并排扫过。数据已经有序（或有合适索引）时最优。

你写 JOIN 时，从不指定用哪种——你只说「按 c.id = o.cust 连起来」，选哪种手法是查询规划器的事。它会根据两张表的大小、有没有索引、数据分布，估算每种手法的成本，挑最省的。第 20 章我们会用真引擎，看同一句 JOIN 在表变大时，规划器怎么从嵌套循环改用哈希连接。这，就是「你的查询也被当真」的意思。

⇄ 对照 MySQL
  • FULL OUTER JOIN：PostgreSQL 原生支持；MySQL 至今没有 FULL JOIN，你得用 LEFT JOIN ... UNION ... RIGHT JOIN 手动拼出来。这是迁移时会直接报语法错的一处。
  • 哈希连接：PostgreSQL 很早就有三种连接算法；MySQL 直到 8.0.18 才引入 Hash Join，更早的版本几乎只有嵌套循环（Block Nested-Loop），大表连接性能吃亏。
  • 连接顺序的干预：MySQL 有 STRAIGHT_JOIN 强制按书写顺序连接；PostgreSQL 默认不提供这类 hint（哲学上信任规划器），要强行控制得靠 pg_hint_plan 扩展或调 join_collapse_limit——卷 V 会谈。
  • ON / WHERE 的外连接语义：这是 SQL 标准，两家一致，坑也一样。

这一章的一句话

五种 JOIN 的差别只在「配不上的孤儿怎么办」（3/5/4/6/16 行）；外连接里对右表的过滤要放 ON、不能放 WHERE；而用哪种物理手法执行，是规划器替你决定的。

连接把散落的行缝到一起。下一步是反过来——把很多行聚拢、压缩成少数几行。下一章讲 GROUP BY 与聚合。