波仔
🦏 第10章 多表查询
📄 一句话概括

FROM a JOIN b ON 条件 是最推荐的写法，JOIN 不写 INNER 也是内连接， INNER 只是让人一眼看出意图。 逗号加 where 是老写法，效果完全等价， 但 join on 把「配对」和「筛选」分开了，改成外连接时只要换一个词。 内连接会「丢行」：class_id 为空或指向不存在的班的学生， 整个不会出现。 要一个人都不漏，得用外连接。

📝 本章和前面几章的关系

集合运算是「上下摞」，从这一篇开始「左右拼」，也就是真正的连接。 内连接是多表查询的主力，实际工作里九成以上的连表都是它。 这一篇的重点是「怎么写清楚」和「什么情况下它会丢行」。

一、join ... on：最推荐的写法

students s 是给表起别名，s.class_id = c.id 是配对条件。 ON 后面只写配对，别写筛选。 别名在多表查询里几乎必写： 表名太长、两表有同名列时要靠别名区分，自连接时不起别名根本写不出来。 JOIN 前面不写 INNER 也是内连接，INNER 只是明示。 表多的时候建议写上——读代码的人不用猜这是内连接还是漏了 LEFT。

二、逗号加 where：老写法，效果一样

FROM students s, classes c WHERE s.class_id = c.id 也是 12 行， 和 join on 完全等价。 这种写法在很老的教材和代码里到处都是。 但推荐 join on： 配对条件在 ON、筛选条件在 WHERE，两者分开了。 混在一起时，一旦要改成外连接就得大改；而 join on 只要把 join 换成 left join。 还有一个隐患：逗号写法里漏一个 where 条件就是笛卡儿积， 而 join on 漏写 on 会更容易被看出来（JOIN 后面紧跟 WHERE 很显眼）。 格式也是防线的一部分。

三、三张表连起来

三张表、两个 ON，一路连下去。 Demo 里查「一班学生的数学成绩」， 从 scores 出发，经 students 到 classes。 中间那张 students 是桥梁： scores 指着它、它指着 classes。 连着三张以上的表时，先在心里画一条「谁指着谁」的链， 链的顺序就是 JOIN 的顺序。 连接顺序不影响结果（优化器会重排），但影响可读性。 从「结果里最主体的那张表」开始连，读起来最顺。

四、natural join：一个千万别用的捷径

classes NATURAL JOIN students 给 0 行。 它会自动拿「两边同名的所有列」当条件—— 两张表都有 id 和 name，于是按「id 相等且 name 相等」去配，一行都配不上。 它不报错、只是结果空了，而且很难看出为什么。 所以实际项目里基本禁用 NATURAL JOIN—— 条件必须显式写出来，那才是能读、能改、能查错的代码。 还有 USING (列)，是「指定用哪一列当条件」的简写，比 NATURAL 安全一些， 但它要求两边列名完全相同，而且结果里那一列只出现一次—— 这两点在多表时容易出意外，实际也少用。

五、内连接会「丢行」，这不是 bug

12 个学生、12 行结果，看着刚好。 但只要有学生的 class_id 是 NULL、或者指向一个不存在的班，内连接就会把他整个丢掉。 这不是 bug，是内连接的定义：「两边都配上」才留。 所以「统计全校学生」这种需求，用内连接就可能少数人。 判断办法： 先 count(*) 一下主表，再 count(*) 一下连完的结果。 少了就说明有行没配上，该换外连接， 或者先查清楚「哪些行的外键是空的」。 《统计数字对不上》这类问题，一半出在这里。

六、动手 Demo：join on、逗号加 where、三表连、natural join 撞空

Demo 五段：join on 连两张表、逗号加 where 的等价写法、三张表一路连到「一班数学成绩」， 再撞一次 NATURAL JOIN，最后讲内连接为什么会丢行。 第四段那个 0 行很值得看——一个「不报错的错误答案」比报错难查得多。

import _q46 as Q
c = Q.con()

# join ... on：推荐写法
Q.show(c, "SELECT s.name AS 学生, c.name AS 班级, c.teacher AS 老师 "
          "FROM students s JOIN classes c ON s.class_id = c.id LIMIT 4")

# 逗号 + where：等价的老写法
Q.show(c, "SELECT COUNT(*) AS n FROM students s, classes c "
          "WHERE s.class_id = c.id")

# 三张表一路连下去
Q.show(c, "SELECT s.name AS 学生, c.name AS 班级, sc.course AS 科目, "
          "sc.score AS 分数 "
          "FROM scores sc "
          "JOIN students s ON sc.student_id = s.id "
          "JOIN classes c ON s.class_id = c.id "
          "WHERE c.name = '一班' AND sc.course = '数学'")

# natural join 的坑：拿同名列当条件，结果 0 行
Q.show(c, "SELECT COUNT(*) AS n FROM classes NATURAL JOIN students")

实跑输出：

--------------------------------------------------------------------
第五十四课：内连接——只留两边都配上的
--------------------------------------------------------------------

  join ... on：最推荐的写法
  SQL> SELECT s.name AS 学生, c.name AS 班级, c.teacher AS 老师 FROM students s JOIN classes c
       ON s.class_id = c.id LIMIT 4
  学生     班级     老师
  -------------------------
  张伟     一班     马老师
  王芳     一班     马老师
  李娜     二班     孔老师
  刘洋     二班     孔老师
  （4 行）
  **`students s` 是给表起别名**，`s.class_id = c.id` 是配对条件。
  **别名在多表查询里几乎必写**：表名太长、而且自连接时不起别名根本写不出来。
  **`JOIN` 前面不写 `INNER` 也是内连接**，
  **`INNER` 只是让人一眼看出这是内连接。** **表多的时候建议写上**——**读代码的人不用猜。**

  逗号加 where：老写法，效果一样
  SQL> SELECT COUNT(*) AS n FROM students s, classes c WHERE s.class_id = c.id
  n
  ---
  12
  （1 行）
  **也是 12 行，和 `join on` 完全等价。** **这种写法在很老的教材和代码里到处都是。**
  **但推荐用 `join on`**：**配对条件和筛选条件分开了**——`on` 管配对，`where` 管筛。
  **混在一起时，一旦要改成外连接就得大改**，**而 `join on` 只要把 `join` 改成 `left join` 就行。**

  三张表连起来
  SQL> SELECT s.name AS 学生, c.name AS 班级, sc.course AS 科目, sc.score AS 分数 FROM scores sc
       JOIN students s ON sc.student_id = s.id JOIN classes c ON s.class_id = c.id
       WHERE c.name = '一班' AND sc.course = '数学'
  学生     班级     科目     分数
  ---------------------------------
  张伟     一班     数学     95.00
  王芳     一班     数学     92.00
  杨帆     一班     数学     85.00
  周涛     一班     数学     72.00
  （4 行）
  **三张表、两个 `on`，一路连下去。** **中间那张 `students` 是桥梁**：
  **`scores` 指着它、它指着 `classes`。**
  **连接顺序不影响结果，但影响可读性**：**从「结果里最主体的那张表」开始连**，读起来最顺。
  **这里主体是成绩，所以 `scores` 放最前面。**

  natural join：一个千万别用的捷径
  SQL> SELECT COUNT(*) AS n FROM classes NATURAL JOIN students
  n
  --
  0
  （1 行）
  **0 行。** **`NATURAL JOIN` 会自动拿「两边同名的所有列」当条件**——
  **两张表都有 `id` 和 `name`，于是它按「`id` 相等且 `name` 相等」去配，一行都配不上。**
  **它不报错、只是结果空了，而且你很难看出为什么。** **所以实际项目里基本禁用 `NATURAL JOIN`**——
  **条件必须显式写出来，那才是能读、能改、能查错的代码。**

  内连接会「丢行」
  **12 个学生、12 行结果，看着刚好。**
  **但如果有个学生的 `class_id` 是 NULL、或者指向一个不存在的班，内连接就会把他整个丢掉。**
  **这不是 bug，是内连接的定义**：
  **「两边都配上」才留。** **所以「统计全校学生」这种需求，用内连接就可能少数人。**
  **要一个人都不漏，得用外连接——下一篇。**

第一段四行，班级名和老师都出来了。 第二段 12 行，和第一段等价，只是写法老。 第三段三表连接，给出四个人的数学成绩：95、92、85、72。 第四段 NATURAL JOIN 给 0 行——它按 id 和 name 两列一起配，一行都配不上。 第五、六段没有 SQL，讲内连接丢行的条件和怎么发现。

30 秒实验

  1. FROM a, b WHERE a.x = b.y 和 FROM a JOIN b ON a.x = b.y 有什么区别？
  2. NATURAL JOIN 为什么在这里给 0 行？它到底按什么条件配的？
  3. SELECT COUNT(*) FROM students 是 12，连完 classes 也是 12——能说明没丢行吗？

常见误区

  1. 用 NATURAL JOIN 图省事。 它按同名列自动配，配错了不报错、只是结果空或者变少。
  2. 配对条件和筛选条件全塞进 where。 后面想改外连接就得大改，而且一眼看不出哪句是配对。
  3. 以为内连接不会丢数据。 外键为空或指向不存在的行会被整个丢掉，统计人数时最容易少数。
自测三问（点开对答案） 1. `JOIN` 和 `INNER JOIN` 有区别吗？ —— 没有，`JOIN` 默认就是内连接，`INNER` 只是显式写出来让人一眼看出意图。 2. 为什么推荐 `JOIN ... ON` 而不是逗号加 `WHERE`？ —— 它把配对条件和筛选条件分开了，改成外连接只要换一个词，可读性也更好。 3. 内连接什么时候会丢行？怎么提前发现？ —— 外键为空或指向不存在的父行时。先分别 `count(*)` 主表和连完的结果，少了对不上就是丢了。

丢了行正是外连接要解决的。 下一篇 10.4 外连接——一边没配上也要留着。

相关笔记