9.2 连接查询 · 波仔的 AI 编程实战课

📄 一句话概括

内连接留下「两边都对得上」的行，外连接把对不上的也拉出来、缺的那边填 NULL。 连接条件写在 ON 还是 WHERE：内连接没区别，外连接天差地别——写错就把左连接变成内连接。 交叉连接是笛卡尔积，3 乘 7 等于 21；它是「忘了写连接条件」的后果，不是目的。

📝 本章和前面几章的关系

9.1a 和 9.1b 讲的是「查询里套查询」。这一节讲另一种思路：把两张表并排摆开，一次比完。 教材 9.2 分四小节：内连接、外连接、交叉连接、连接查询的使用。 前三节是三种语法，最后一节是「怎么用」——自连接、多表连接、连接配聚合都在那儿。 演示里用样例库真跑，其中有一处特意做了对照： 同一条 SQL 只把条件从 ON 挪到 WHERE，5 行变 1 行。 这个差别是本篇最该记住的一处。

一、内连接：三种写法，一个结果

同一件事有三种写法，结果完全一样：

-- 1. 逗号 + WHERE（最老）
SELECT st.sname, c.cname FROM tb_student st, tb_class c
WHERE st.class_id = c.id

-- 2. INNER JOIN + ON（推荐）
SELECT st.sname, c.cname FROM tb_student st
INNER JOIN tb_class c ON st.class_id = c.id

-- 3. 省略 INNER 的 JOIN
SELECT st.sname, c.cname FROM tb_student st
JOIN tb_class c ON st.class_id = c.id

三种都返回 7 行。 推荐第二种，理由不是「新语法更好」，是它把「连接条件」和「过滤条件」分开放了——ON 管连接，WHERE 管过滤，一眼看得出哪句是干什么的。 第一种写法最容易出事： 忘了写 WHERE 就变成笛卡尔积， 两张一万行的表连出来一亿行，查询直接卡死。 用 JOIN 的话，ON 是语法必须的，忘不了。 内连接的语义是「两张表都对得上的行才留下」。 学生表里那个班号，班级表里没有对应的——这行学生就消失了，不报错。

二、外连接：把对不上的也拉出来

左连接保左边全部，右边配不上就填 NULL：

SELECT m.uname, o.order_no, o.total
FROM tb_member m LEFT JOIN tb_order o ON o.member_id = m.id

演示里 member005 一笔订单都没有，右半边全是 NULL。 这一行在内连接里是不存在的—— 而它恰恰是最值得看的一行：一个注册了却从没下过单的会员。

类型	保哪边	典型用途
`LEFT JOIN`	左表全部	主表 + 可选的明细（会员与订单）
`RIGHT JOIN`	右表全部	很少用，把两边调个位置就能代替
`FULL OUTER JOIN`	两边都保	对账：两边谁多出来的都要看见

RIGHT JOIN 基本可以不用。 把两张表换个位置、LEFT 改成 LEFT，结果一样——多一种写法只是多一种看错的机会。 FULL OUTER JOIN 的价值在「对账」。 演示里 6 行 = 5 笔配得上的订单 + 1 个没单的会员， 没有「对不上人的野单」。真对账时，野单和没单的会员都要出现在同一张表里，那才用得上它。

三、交叉连接：笛卡尔积

SELECT COUNT(*) FROM tb_class CROSS JOIN tb_student

3 个班乘 7 个学生，等于 21 行。 左边每一行都跟右边每一行配一次—— 这就是笛卡尔积，行数是两者相乘。 它几乎总是「忘了写连接条件」的后果，不是目的。 但偶尔真用得上： 要给每个班都排一份考试科目清单，就是「班 × 科目」——那就该用交叉连接。 排查笛卡尔积的办法：看结果行数。 如果查询返回的行数等于两张表行数相乘， 那一定是漏了 ON 或者 WHERE。 这个信号比看执行计划快得多。

四、连接条件写 ON 还是写 WHERE

这一节请用演示里的两个结果对着看。

-- A：条件放 ON 里 —— 5 行
SELECT m.uname, o.order_no FROM tb_member m
LEFT JOIN tb_order o ON o.member_id = m.id AND o.total > 4000

-- B：条件放 WHERE 里 —— 1 行
SELECT m.uname, o.order_no FROM tb_member m
LEFT JOIN tb_order o ON o.member_id = m.id WHERE o.total > 4000

同一条 SQL，只是把条件挪了个位置，5 行变 1 行。 放 ON： 先按「会员配订单，【并且订单金额大于 4000】」去配。 配不上的会员照样留着，右边填 NULL。 所以 5 个会员一个不少。 放 WHERE： 先按 member_id 配好，再用「金额大于 4000」去筛。 而 NULL > 4000 是未知，被丢掉——于是左连接退化成了内连接。 这就是「LEFT JOIN 写着写着不左了」的头号原因。判断方法很简单： 左连接之后，结果行数不该少于左表行数。 少了，就是条件放错地方了。

五、多表连接、自连接与连接配聚合

三表连接就是连两次，一条 JOIN 接一条：

SELECT st.sname, c.cname, sc.subject, sc.score
FROM tb_student st
JOIN tb_class  c  ON c.id  = st.class_id
JOIN tb_score  sc ON sc.student_id = st.id
WHERE c.cname = '计科2301'

自连接是「同一张表当两张用」，靠别名区分。 查「跟张伟同班的人」就是tb_student a JOIN tb_student b ON b.class_id = a.class_id。 自连接最容易漏的一句是 b.id <> a.id。 不写它，张伟会出现在「张伟的同班同学」里——因为他确实跟自己同班。 连接完还要聚合的时候，有两条要注意的： 一是连接方式。 只要有任何一边可能配不上、而你又不想丢掉那些行，就用 LEFT JOIN。演示里那个「每班人数与均分」，两个连接都必须是 LEFT，否则没成绩的赵敏会被悄悄抹掉。 二是计数。 一个学生有多条成绩，JOIN 之后行数会变多，直接 COUNT(*) 就会重复计数。 该用 COUNT(DISTINCT st.id)——数的是人，不是行。

六、动手 Demo：内连接、外连接与 ON／WHERE 的位置之差

这一段在样例库上把三种连接都跑一遍，再临时插一个没下过单的会员、一个没录成绩的学生，专门看外连接补出来的 NULL 行。 重点是最后一节那个对照：同一条件放 ON 是 5 行，放 WHERE 是 1 行。

import sqlite3

c = sqlite3.connect(u":memory:")
# ... 建六表、装样例数据 ...
c.execute(u"INSERT INTO tb_member VALUES (5,?,?,?,?,?)",
          (u"member005", u"555555", u"13800000005", u"2026-03-10", 20))

# 内连接：三种写法，比行数
q1 = u"SELECT st.sname, c.cname FROM tb_student st, tb_class c WHERE st.class_id = c.id"
q2 = u"SELECT st.sname, c.cname FROM tb_student st INNER JOIN tb_class c ON st.class_id = c.id"
print(len(c.execute(q1).fetchall()), len(c.execute(q2).fetchall()))

# 外连接：没下过单的会员会带 NULL 出来
print(c.execute(u"SELECT m.uname, o.order_no FROM tb_member m "
                u"LEFT JOIN tb_order o ON o.member_id = m.id ORDER BY m.id"
                ).fetchall())

# 同一个条件，放 ON 和放 WHERE
qa = (u"SELECT m.uname, o.order_no FROM tb_member m "
      u"LEFT JOIN tb_order o ON o.member_id = m.id AND o.total > 4000")
qb = (u"SELECT m.uname, o.order_no FROM tb_member m "
      u"LEFT JOIN tb_order o ON o.member_id = m.id WHERE o.total > 4000")
print(len(c.execute(qa).fetchall()), len(c.execute(qb).fetchall()))

# 交叉连接的行数 = 两边相乘
print(c.execute(u"SELECT COUNT(*) FROM tb_class CROSS JOIN tb_student").fetchone())

实跑输出：

--------------------------------------------------------------------
连接查询
--------------------------------------------------------------------


  ① 内连接：三种写法，一个结果
  --------------------------------------------------------------------

  1> SELECT st.sname, c.cname FROM tb_student st INNER JOIN tb_class c ON st.class_id = c.id
  sname  cname
  ---------------
  张伟   计科2301
  王芳   计科2301
  李娜   计科2301
  刘强   计科2302
  陈静   计科2302
  杨洋   软工2301
  赵敏   软工2301
  (7 行受影响)
  三种写法：逗号 + WHERE（最老）、INNER JOIN + ON（推荐）、省略 INNER 的 JOIN。
  结果条数分别是 7、7、7 行——完全一样。JOIN 只是 INNER JOIN 的简写。
  内连接的语义是「两张表都对得上的行才留下」，对不上的直接消失。
  赵敏是刚插进来的第 7 个学生，她所属的班存在，所以她也留着。

  ② 外连接：把对不上的也拉出来
  --------------------------------------------------------------------

  1> SELECT m.uname, o.order_no, o.total
     FROM tb_member m
     LEFT JOIN tb_order o ON o.member_id = m.id
     ORDER BY m.id
  uname      order_no       total
  --------------------------------
  member001  NO20260501001  5088.9
  member001  NO20260505001  399
  member002  NO20260502001  1299
  member003  NO20260503001  977.9
  member004  NO20260506001  549
  member005  NULL           NULL
  (6 行受影响)
  member005 一笔订单都没有，右半边全是 NULL——这就是 LEFT JOIN 的作用。
  LEFT JOIN 保左边全部，右表没配上就填 NULL；RIGHT JOIN 反过来保右边。

  RIGHT JOIN 在 SQLite 3.39 起才支持，本沙箱是 3.53.1，能跑。

  1> SELECT m.uname, o.order_no
     FROM tb_member m
     RIGHT JOIN tb_order o ON o.member_id = m.id
     ORDER BY o.id
  uname      order_no
  ------------------------
  member001  NO20260501001
  member002  NO20260502001
  member003  NO20260503001
  member001  NO20260505001
  member004  NO20260506001
  (5 行受影响)
  5 行，全是右表（订单）的行。上面那 5 行会员里，凡是有单的都在。

  FULL OUTER JOIN 两边都保：

  1> SELECT m.uname, o.order_no
     FROM tb_member m FULL OUTER JOIN tb_order o ON o.member_id = m.id
     ORDER BY m.id, o.id
  uname      order_no
  ------------------------
  member001  NO20260501001
  member001  NO20260505001
  member002  NO20260502001
  member003  NO20260503001
  member004  NO20260506001
  member005  NULL
  (6 行受影响)
  6 行 = 5 笔配得上的订单 + 1 个没下过单的会员 + 0 笔对不上人的野单。
  想让野单也现形，就再造一笔 member_id 不存在的订单——那才用得上 FULL。

  ③ 交叉连接：笛卡尔积
  --------------------------------------------------------------------
| 左表行数 | 右表行数 | 交叉连接后的行数 |
|---:|---:|---:|
| 3 | 7 | 21 |

  3 乘 7 等于 21。这就是笛卡尔积：左边每一行都跟右边每一行配一次。
  交叉连接本身没什么业务意义，它是「忘了写连接条件」的后果。
  但偶尔也真用得上：要给每个班都排一份考试科目清单，就是班 × 科目。

  ④ 连接条件写 ON 还是写 WHERE：内连接没差别，外连接天差地别
  --------------------------------------------------------------------

  A 写法：条件放在 ON 里 —— 5 行
  uname      order_no
  ------------------------
  member001  NO20260501001
  member002  NULL
  member003  NULL
  member004  NULL
  member005  NULL
  (5 行受影响)
  B 写法：条件放在 WHERE 里 —— 1 行
  uname      order_no
  ------------------------
  member001  NO20260501001
  (1 行受影响)
  ! 同一条 SQL 只是把条件挪了个位置，5 行变 1 行。这个差别必须记住。
  放 ON：先按「会员配订单且金额大于 4000」去配，配不上的会员照样留着、右边填 NULL。
  放 WHERE：先按 member_id 配好，再用「金额大于 4000」筛。NULL > 4000 是未知，被丢掉。
  于是外连接退化成了内连接——这是「LEFT JOIN 写着写着不左了」的头号原因。

  ⑤ 三表连接与自连接
  --------------------------------------------------------------------

  1> SELECT st.sname, c.cname, sc.subject, sc.score
     FROM tb_student st JOIN tb_class c ON c.id = st.class_id JOIN tb_score sc ON
         sc.student_id = st.id
     WHERE c.cname = '计科2301'
     ORDER BY st.sno, sc.subject
  sname  cname     subject   score
  --------------------------------
  张伟   计科2301  大学英语  76.5
  张伟   计科2301  高等数学  88
  王芳   计科2301  大学英语  85
  王芳   计科2301  高等数学  92
  李娜   计科2301  高等数学  61
  (5 行受影响)
  三表连接就是连接两次，一条 JOIN 接一条，链条越接越长。

  自连接：同一张表当两张用，靠别名区分。查「跟张伟同班的人」。

  1> SELECT b.sname AS 同班同学
     FROM tb_student a JOIN tb_student b ON b.class_id = a.class_id
     WHERE a.sname = '张伟' AND b.id <> a.id
  同班同学
  --------
  李娜
  王芳
  (2 行受影响)
  必须写 b.id <> a.id，否则会把自己也查出来——自连接最容易漏的一句。

  连接 + 聚合：每个班几个人、平均分多少

  1> SELECT c.cname, COUNT(DISTINCT st.id) AS 人数, ROUND(AVG(sc.score), 2) AS 均分
     FROM tb_class c
     LEFT JOIN tb_student st ON st.class_id = c.id
     LEFT JOIN tb_score sc ON sc.student_id = st.id
     GROUP BY c.cname
  cname     人数  均分
  ---------------------
  计科2301  3     80.5
  计科2302  2     73.25
  软工2301  2     79
  (3 行受影响)
  ! 这里两个连接都必须是 LEFT JOIN，用 INNER 的话没成绩的赵敏会被悄悄抹掉。
  人数那里用了 COUNT(DISTINCT st.id)：一个学生有多条成绩，直接 COUNT 会重复计数。

第一段两个数都是 7。 三种写法在内连接上完全等价， 差别只在「写的清不清楚」和「忘不忘得写连接条件」。 第二段那 6 行里，第 6 行是 member005 配 NULL。 这行数据本身很有用—— 它告诉你「谁注册了但从来没买过」，做用户运营的时候这类名单最值钱。 第三段两个数：5 和 1。 差的那 4 行不是数据没了， 是被 WHERE 里的 NULL > 4000 判成未知丢掉的。 最后那个 21 是 3 乘 7。 注意这时学生表已经有 7 个人了——演示中途插过一个没成绩的学生，所以不是 6 乘 3 的 18。

30 秒实验

把「每班人数与均分」那条里的两个 LEFT JOIN 改成 JOIN，跑一次。 软工2301 的人数会从 2 变成 1——没成绩的赵敏被悄悄抹掉了。
把自连接里的 b.id <> a.id 删掉，看结果。张伟会出现在自己的同班同学名单里。
把 A 写法的 AND o.total > 4000 从 ON 挪到 WHERE，对照行数。 5 变 1。这一个实验能让你以后再也不会写错位置。

常见误区

「LEFT JOIN 就是左边一定全在」。 只要 WHERE 里筛了右表的列，就不再是左连接了——NULL 那一行会被判成未知丢掉。 筛右表的条件要放 ON 里。
「多表连接用 JOIN 还是用逗号加 WHERE 都一样」。语法上一样，可读性上差很远。逗号写法漏了条件就是笛卡尔积，而且两张表一大，卡死的是整台数据库。
「连接之后 COUNT(*) 就是行数」。一个学生对多条成绩，JOIN 之后行数会翻倍。 数人要用 COUNT(DISTINCT id)。

自测三问（点开对答案）

1. `LEFT JOIN` 之后，`WHERE` 里筛右表的列会怎样？ —— **左连接退化成内连接。** 因为右表没配上的那些行是 `NULL`，`NULL` 参与比较是未知， **`WHERE` 只留真，这些行全被丢掉。** 2. 内连接三种写法结果一样吗？推荐哪种？ —— **完全一样。** 推荐 `JOIN ... ON`—— **它把连接条件和过滤条件分开放了，而且 `ON` 是语法必须的，忘不了。** 3. 怎么快速发现一条查询里漏了连接条件？ —— **看结果行数。** 如果行数等于两张表行数相乘，就是笛卡尔积。 **3 乘 7 等于 21，这个数很好认。**

下一篇 9.3 是第 9 章的收尾： 分页的三种写法、深翻页为什么越翻越慢、以及 ROW_NUMBER／RANK／DENSE_RANK 三兄弟的区别。

☐ （本篇完，下一篇 9.3 查询进阶的综合用法）