10.4 外连接 · 波仔的 AI 编程实战课

📄 一句话概括

LEFT JOIN 的意思是「左表每一行都必须出现」，右表配不上就填 NULL。 A RIGHT JOIN B 就等于 B LEFT JOIN A，所以习惯上只用左外。 「左外连接 + 右表主键 IS NULL」是找「孤儿」的标准套路： 没有学生的班、没有订单的用户、没有明细的订单，全靠这一招。 ON 里加条件和 WHERE 里加条件，在外连接里结果不同（Demo 里 6 行对 5 行）。 这是外连接最深的一处。

📝 本章和前面几章的关系

上一篇说内连接会丢行，这一篇就是把丢的那部分捞回来。 外连接的价值不在「多给几行」，而在「让空缺显形」—— 一个班没有学生、一个商品从没被买过，这些「零」在内连接里根本看不见。

一、先造一个「没学生的班」

样例库里三个班各有四个学生，一个孤儿都没有。 所以 Demo 先插一个「四班」。 用内连接统计人数，四班根本不出现——可它明明在 classes 里。 这就是内连接丢掉的那一行。 这个现象在真实数据里到处都是： 新注册还没下单的用户、新上架还没卖的商品、刚建的仓库还没有库存——内连接会把它们全过滤掉。

二、left join：左边的表一行都不许少

classes c LEFT JOIN students s ON s.class_id = c.id，四班出来了，学生那一列是 NULL。 「左表（classes）的每一行都必须出现」，右表配不上就填 NULL。 注意填的是 NULL，不是空串、不是 0—— 这几列后面做运算时要小心，NULL + 1 还是 NULL。 左外连接还能用来「统计含零的计数」：COUNT(s.id) 对四班给 0， 正好是「没有学生」的正确表示。 用 COUNT(*) 就错了—— 它会数「这一行存在」，四班也会数成 1。

三、用 is null 找「没有匹配的」

LEFT JOIN … WHERE 右表主键 IS NULL 只给出四班。 这是找「孤儿」的标准套路。 要筛右表的主键，不是随便哪一列—— 筛 s.name IS NULL 会把「名字真的没填的学生」也算进来。 主键不可能为 NULL，所以「主键是 NULL」只可能是「没配上」。 同一件事也能用 NOT IN 或 NOT EXISTS 写， 但左外连接这种写法通常更快、也更好读。 注意 NOT IN 遇到 NULL 会整个失效—— 子查询里只要有一个 NULL，NOT IN 就一行都不返回。 这是另一个经典坑，左外连接没这个问题。

四、right join：换个方向

13 行，和 classes LEFT JOIN students 一样。 A RIGHT JOIN B 就是 B LEFT JOIN A，只是书写顺序不同。 所以实际写代码时几乎只用 LEFT JOIN： 把要保全的那张表放左边，思维负担最小。 混用左右会让读代码的人每次都要在脑子里翻一次。 唯一的例外是「表已经写好了，临时想把右边保全」—— left 改 right 比调换两个表的位置快。 但改完最好再顺手调换回来，别留在代码里。

五、full outer join：两边都保全

把孙悦的班级号置空之后，两边各有一个「配不上」的。 FULL OUTER JOIN 给两行：「四班没有学生」和「孙悦没有班级」。 它就是左外和右外的并集。 本沙箱支持它，MySQL 不支持—— MySQL 里得写成 LEFT JOIN 加 UNION 加 RIGHT JOIN 拼出来。 这一点教材里未必提，真用的时候会遇到。

六、on 和 where 在外连接里完全不是一回事

LEFT JOIN … ON s.gender = '女' 给 6 行；LEFT JOIN … WHERE s.gender = '女' 给 5 行。 差的这一行就是四班。 原因：ON 只决定「怎么配」，配不上照样保留左表的行（右表填 NULL）； WHERE 是「配完再筛」，四班那一行被筛掉了。 内连接里两者等价，所以很多人以为外连接也一样。 这是外连接里最深的一处，也是「数字差一点点」最难查的原因。 记法： ON 管「怎么配」，WHERE 管「配完之后留哪些」。 想保全左表，筛选条件就往 ON 里放。

七、动手 Demo：内连接丢掉四班，左外把它捞回来

Demo 七段：先造一个没学生的四班，用内连接统计（四班不见），再用左外把它捞出来、用 IS NULL 精确定位它；然后 RIGHT JOIN、FULL OUTER JOIN，最后对照 ON 与 WHERE 的 6 行对 5 行。 第一段和最后一段是全篇的重点——一段说「丢行」，一段说「条件放错地方也会丢行」。

import _q46 as Q
c = Q.con()

# 先造一个没学生的班
Q.run(c, "INSERT INTO classes (id, name, teacher, room) "
          "VALUES (4, '四班', '唐老师', 'B301')")

# 内连接：四班不出现
Q.show(c, "SELECT c.id, c.name, COUNT(s.id) AS 人数 "
          "FROM classes c JOIN students s ON s.class_id = c.id "
          "GROUP BY c.id")

# 左外连接：四班出现，学生那列是 NULL
Q.show(c, "SELECT c.name AS 班级, s.name AS 学生 "
          "FROM classes c LEFT JOIN students s "
          "ON s.class_id = c.id WHERE c.id = 4")

# 找「没有匹配的」：右表主键 IS NULL
Q.show(c, "SELECT c.id, c.name, c.teacher "
          "FROM classes c LEFT JOIN students s "
          "ON s.class_id = c.id WHERE s.id IS NULL")

# 右外与完全外
Q.show(c, "SELECT COUNT(*) AS n FROM students s RIGHT JOIN classes c "
          "ON s.class_id = c.id")
Q.run(c, "UPDATE students SET class_id = NULL WHERE id = 12")
Q.show(c, "SELECT c.name AS 班级, s.name AS 学生 "
          "FROM classes c FULL OUTER JOIN students s "
          "ON s.class_id = c.id "
          "WHERE c.id IS NULL OR s.id IS NULL")

# on 与 where 的差别：6 行 vs 5 行
Q.show(c, "SELECT COUNT(*) AS n_on FROM classes c "
          "LEFT JOIN students s "
          "ON s.class_id = c.id AND s.gender = '女'")
Q.show(c, "SELECT COUNT(*) AS n_where FROM classes c "
          "LEFT JOIN students s ON s.class_id = c.id "
          "WHERE s.gender = '女'")

实跑输出：

--------------------------------------------------------------------
第五十五课：外连接——一边没配上也要留着
--------------------------------------------------------------------

  先造一个「没学生的班」
  SQL> INSERT INTO classes (id, name, teacher, room) VALUES (4, '四班', '唐老师', 'B301')
  影响 1 行
  SQL> SELECT c.id, c.name, COUNT(s.id) AS 人数 FROM classes c JOIN students s
       ON s.class_id = c.id GROUP BY c.id
  id     name     人数
  ---------------------
  1      一班     4
  2      二班     4
  3      三班     4
  （3 行）
  **内连接只给三行**：四班根本没出现。 **可四班明明在 `classes` 里。**

  left join：左边的表一行都不许少
  SQL> SELECT c.name AS 班级, s.name AS 学生 FROM classes c LEFT JOIN students s
       ON s.class_id = c.id WHERE c.id = 4
  班级     学生
  --------------
  四班     NULL
  （1 行）
  **四班出来了，学生那一列是 NULL。**
  **`LEFT JOIN` 的意思是「左表（`classes`）的每一行都必须出现」**，**右表配不上就填 NULL。**

  用 is null 找「没有匹配的」
  SQL> SELECT c.id, c.name, c.teacher FROM classes c LEFT JOIN students s ON s.class_id = c.id
       WHERE s.id IS NULL
  id     name     teacher
  ------------------------
  4      四班     唐老师
  （1 行）
  **只给出四班。** **「左外连接 + 右表主键 IS NULL」是找「孤儿」的标准套路**：
  **没有学生的班、没有订单的用户、没有明细的订单，全靠这一招。**
  **注意筛的是 `s.id`（右表的主键），不是别的列。**
  **筛 `s.name IS NULL` 会连带把「名字真的没填的学生」也算进来。**

  right join：换个方向
  SQL> SELECT COUNT(*) AS n FROM students s RIGHT JOIN classes c ON s.class_id = c.id
  n
  ---
  13
  （1 行）
  **13 行，和 `classes LEFT JOIN students` 一样。** **`A RIGHT JOIN B` 就是 `B LEFT JOIN A`**，
  只是书写顺序不同。
  **所以实际写代码时几乎只用 `LEFT JOIN`**——
  **把要保全的那张表放左边，思维负担最小。** **混用左右会让读代码的人每次都要在脑子里翻一次。**

  full outer join：两边都保全
  SQL> UPDATE students SET class_id = NULL WHERE id = 12
  影响 1 行
  **孙悦的班级号置空了**，现在两边各有一个「配不上」的。
  SQL> SELECT c.name AS 班级, s.name AS 学生 FROM classes c FULL OUTER JOIN students s
       ON s.class_id = c.id WHERE c.id IS NULL OR s.id IS NULL
  班级     学生
  --------------
  四班     NULL
  NULL     孙悦
  （2 行）
  **两行**：一行是「四班没有学生」，一行是「孙悦没有班级」。
  **`FULL OUTER JOIN` 就是左外和右外的并集。**
  **注意本沙箱支持它，MySQL 不支持**——
  **MySQL 里得写成 `LEFT JOIN` 加 `UNION` 加 `RIGHT JOIN` 拼出来。**
  **这一点教材里未必提，真用的时候会遇到。**

  on 和 where 在外连接里完全不是一回事
  SQL> SELECT COUNT(*) AS n_on FROM classes c LEFT JOIN students s
       ON s.class_id = c.id AND s.gender = '女'
  n_on
  -----
  6
  （1 行）
  SQL> SELECT COUNT(*) AS n_where FROM classes c LEFT JOIN students s ON s.class_id = c.id
       WHERE s.gender = '女'
  n_where
  --------
  5
  （1 行）
  **`on` 里加条件给 6 行，`where` 里加条件给 5 行。** **差的这一行就是四班**——
  **`on` 只是「配不上的填 NULL」，左表照样保留；`where` 是「配完再筛」，四班那一行被筛掉了。**
  **这是外连接里最深的一处**：
  **左外连接的条件写 `on` 还是 `where`，结果不同。**
  **内连接里两者等价，所以很多人以为外连接也一样。**
  **记法**：
  **`on` 管「怎么配」，`where` 管「配完之后留哪些」。** **想保全左表，筛选条件就往 `on` 里放。**

第一段内连接只给三行，四班彻底不见。 第二段左外连接，四班出现、学生是 NULL。 第三段 IS NULL 精确筛出四班，一行。 第四段 RIGHT JOIN 给 13 行；第五段 FULL OUTER JOIN 给两行孤儿。 第六段 ON 里加条件 6 行、WHERE 里加条件 5 行——差的就是四班。

30 秒实验

要「统计每个班的人数，没有学生的班也要显示 0」，用内连接行不行？该怎么写？
LEFT JOIN 之后用 COUNT(*) 和 COUNT(s.id) 统计四班，结果一样吗？
LEFT JOIN … ON 条件 和 LEFT JOIN … WHERE 条件 结果为什么不同？

常见误区

用内连接统计「每个班的人数」。 没学生的班直接消失，报表上少一行还很难发现。
左外连接之后用 COUNT(*) 数人数。 没配上的行也数成 1，该用 COUNT(右表主键)。
把筛选条件写在 WHERE 里还指望保全左表。 WHERE 是配完再筛，左表那一行照样会被筛掉。

自测三问（点开对答案）

1. `LEFT JOIN` 到底保证什么？ —— 保证左表每一行都出现，右表配不上就填 NULL。 2. 怎么找出「没有学生的班」？为什么筛右表的主键而不是别的列？ —— 左外连接加 `WHERE 右表主键 IS NULL`。主键不可能为 NULL，所以「主键是 NULL」只可能是「没配上」。 3. 外连接里 `ON` 加条件和 `WHERE` 加条件差在哪？ —— `ON` 只影响怎么配，配不上照样保留左表行；`WHERE` 是配完之后再筛，会把这些行筛掉。

内连接和外连接都会配对，下一篇讲一个「不配对」的：10.5 笛卡儿积连接。

☐ （本篇完，下一篇 10.5 笛卡儿积连接）