第 6 章:JOIN 连接多张表
拆表的代价是:任何一条有意义的查询,几乎都要跨两张以上的表。
JOIN就是把拆散的事实重新拼回来的那根线。
学习目标
- 说清
INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN分别保留哪些行 - 理解笛卡尔积是怎么产生的,以及
ON在做什么 - 能写出四张表串起来的查询,并配上聚合
- 知道
LEFT JOIN的过滤条件写进WHERE会出什么事
6.1 为什么必须 JOIN
orders 表里没有客户姓名,只有 customer_id:
SELECT id, customer_id, ordered_at, status FROM orders LIMIT 3;┌────┬─────────────┬────────────┬────────┐
│ id │ customer_id │ ordered_at │ status │
├────┼─────────────┼────────────┼────────┤
│ 1 │ 1 │ 2025-06-01 │ paid │
│ 2 │ 1 │ 2025-06-15 │ shipped│
│ 3 │ 2 │ 2025-06-20 │ paid │
└────┴─────────────┴────────────┴────────┘看到 customer_id = 1,你并不知道是谁。要拿到姓名,得去 customers 表查 id = 1 那一行——这就是 JOIN 在做的事:按某个相等的值,把两张表的行对上。
SELECT o.id, c.name, o.ordered_at, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id
LIMIT 3;┌────┬──────┬────────────┬────────┐
│ id │ name │ ordered_at │ status │
├────┼──────┼────────────┼────────┤
│ 1 │ 张三 │ 2025-06-01 │ paid │
│ 2 │ 张三 │ 2025-06-15 │ shipped│
│ 3 │ 李四 │ 2025-06-20 │ paid │
└────┴──────┴────────────┴────────┘几个写法约定:
orders o是给表起别名,后面用o.id引用,省得写全名;ON c.id = o.customer_id是连接条件,说明两边的行怎么对应;- 不写
JOIN类型时默认就是INNER JOIN。
6.2 INNER JOIN:两边都有的才留下
INNER JOIN 的规则很简单:只保留连接条件成立的行。
上面的查询返回 12 行——每张订单都有一个存在的客户,所以全部匹配。
如果反过来,从客户出发连订单:
SELECT c.name, o.id AS 订单号
FROM customers c
JOIN orders o ON o.customer_id = c.id;结果只有 12 行,孙七不在里面——她没有订单,INNER JOIN 找不到匹配的右表行,就把她丢掉了。
笛卡尔积:不写 ON 会发生什么
SELECT COUNT(*) FROM customers c JOIN orders o ON 1 = 1; -- 605 个客户 × 12 张订单 = 60 行。每一行客户和每一行订单都配了一次对——这叫笛卡尔积。
ON 的作用就是从这 60 行里筛出"真的对应"的 12 行。
忘写 ON 不会报错,只会得到一个看起来很大、实际上全错的结果。所以看到行数异常膨胀,第一反应就是检查连接条件。
6.3 LEFT JOIN:宁可右表空着,也不丢左表
业务上经常需要"以某张表为准":列出所有客户及其订单数,包括没下过单的。
LEFT JOIN 的规则是:左表的每一行都必须出现;右表有匹配就填上,没有就整行补 NULL。
SELECT c.name, o.id AS 订单号
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;结果 13 行:12 行是正常订单,最后多一行 孙七 | NULL。
-- 找出从没下单的客户
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;┌──────┐
│ name │
├──────┤
│ 孙七 │
└──────┘"找没有"是 LEFT JOIN 最经典的用法:连接之后右表为 NULL,就说明左表这一行在右表里不存在。找"没有评论的文章""没有激活的用户""没被引用的字段",都是这个套路。
RIGHT JOIN 就是反过来,保留右表全部。逻辑上等价于把两张表位置对调写 LEFT JOIN,实务中很少用。
FULL JOIN 两边都保留,任一侧不匹配都补 NULL。
SQLite 从 3.39(2022 年)开始支持
RIGHT JOIN和FULL JOIN。更早的版本会报语法错误,用交换表序的LEFT JOIN替代即可。
6.4 多表连接:顺着外键一路串
真实查询往往要串三四张表。比如"每张订单买了什么书":
SELECT
o.id AS 订单号,
c.name AS 客户,
b.title AS 书名,
i.quantity AS 数量,
i.unit_price AS 单价
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN books b ON b.id = i.book_id
ORDER BY o.id, b.title
LIMIT 5;┌────────┬──────┬──────────────────────┬──────┬───────┐
│ 订单号 │ 客户 │ 书名 │ 数量 │ 单价 │
├────────┼──────┼──────────────────────┼──────┼───────┤
│ 1 │ 张三 │ 深入理解计算机系统 │ 1 │ 128.0 │
│ 1 │ 张三 │ SQL 必知必会 │ 2 │ 49.0 │
│ 2 │ 张三 │ 活着 │ 1 │ 39.0 │
│ 3 │ 李四 │ 数据库系统概念 │ 1 │ 99.0 │
│ 3 │ 李四 │ 代码整洁之道 │ 1 │ 59.0 │
└────────┴──────┴──────────────────────┴──────┴───────┘写多表连接的顺序建议:从"事实表"出发,顺着外键往外连。这里是 orders(订单事实)→ 客户、明细 → 书籍。
不要一上来就把四张表都摆上再想条件,那样很容易漏 ON。
6.5 JOIN 配聚合:每客户消费额
SELECT
c.name,
COUNT(DISTINCT o.id) AS 订单数,
ROUND(SUM(i.quantity * i.unit_price), 2) AS 消费额
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'refunded'
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY c.id, c.name
ORDER BY 消费额 DESC;结果就是第 5 章那张表:王五 467、李四 385、张三 383、赵六 362、孙七 0。
两个要点:
COUNT(DISTINCT o.id)不能写成COUNT(*)。orders和order_items是一对多,连接后一张订单会变成多行,COUNT(*)数出来是明细条数,不是订单数;GROUP BY里带c.id,避免同名客户被合并。
6.6 最容易错的地方:条件写进了 WHERE
需求:每个客户的有效消费额(排除退款订单),没下过单的也要显示 0。
下面这个写法看起来合理,实际是错的:
SELECT c.name, ROUND(SUM(i.quantity * i.unit_price), 2) AS 消费额
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'refunded' -- ← 问题在这里
GROUP BY c.id, c.name;结果只有 4 个客户,孙七消失了。
原因:WHERE 是对连接后的结果过滤。孙七那一行的 o.status 是 NULL,而 NULL <> 'refunded' 的结果是 NULL(第 3 章的三值逻辑),不是 TRUE,于是她被过滤掉了。LEFT JOIN 承诺"左表全保留",被 WHERE 一句话作废。
正确做法:把条件放进 ON
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status <> 'refunded'ON 的条件只影响"右表哪一行能匹配上",不影响左表是否保留。订单 4 匹配不上,孙七也仍然保留(补 NULL)。
判断规则
| 你想表达 | 条件放哪 |
|---|---|
| 右表里只取符合条件的行来连接 | ON |
| 连接完,整行结果不符合就丢弃 | WHERE |
一个简单的自测:如果这个条件会让"本该出现"的行消失,它就不该在 WHERE 里。
6.7 四种连接速查
| 类型 | 保留谁 | 典型场景 |
|---|---|---|
INNER JOIN | 两边都匹配的行 | 查订单详情(订单必有客户) |
LEFT JOIN | 左表全部 + 右表匹配 | 客户报表(含未下单)、"找没有" |
RIGHT JOIN | 右表全部 + 左表匹配 | 少用,等价于对调写 LEFT |
FULL JOIN | 两边全部 | 对账:两边差异都要看到 |
用错连接类型,结果不会报错,只会"少几行"或"多几行"——所以写完一定要数一下行数是否符合预期。
本章小结
| 要点 | 说明 |
|---|---|
| JOIN 的本质 | 按相等值把两张表的行对上 |
INNER JOIN 只留匹配 | 不匹配的行直接丢 |
LEFT JOIN 保左表 | 右表没匹配就补 NULL |
忘写 ON 产生笛卡尔积 | 5 × 12 = 60 行,不会报错 |
| 多表连接的顺序 | 从事实表出发,顺外键往外连 |
一对多连接后要 COUNT(DISTINCT) | 否则数的是明细条数 |
LEFT JOIN 的过滤条件放 ON | 放 WHERE 会让左表行消失 |
下一章
连接解决"横向拼表",下一章解决"在查询里再套一个查询":WHERE ... IN (SELECT ...)、EXISTS、以及把复杂逻辑拆成几步的 WITH。子查询是 SQL 里最容易被写乱的地方,CTE 是把它理顺的工具。