Skip to content
Lv1SQL 与数据库进度 6 / 12

第 6 章:JOIN 连接多张表 ​

拆表的代价是:任何一条有意义的查询,几乎都要跨两张以上的表。JOIN 就是把拆散的事实重新拼回来的那根线。


学习目标 ​

  • 说清 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 分别保留哪些行
  • 理解笛卡尔积是怎么产生的,以及 ON 在做什么
  • 能写出四张表串起来的查询,并配上聚合
  • 知道 LEFT JOIN 的过滤条件写进 WHERE 会出什么事

6.1 为什么必须 JOIN ​

orders 表里没有客户姓名,只有 customer_id:

sql
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 在做的事:按某个相等的值,把两张表的行对上。

sql
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 行——每张订单都有一个存在的客户,所以全部匹配。

如果反过来,从客户出发连订单:

sql
SELECT c.name, o.id AS 订单号
FROM customers c
JOIN orders o ON o.customer_id = c.id;

结果只有 12 行,孙七不在里面——她没有订单,INNER JOIN 找不到匹配的右表行,就把她丢掉了。

笛卡尔积:不写 ON 会发生什么 ​

sql
SELECT COUNT(*) FROM customers c JOIN orders o ON 1 = 1;  -- 60

5 个客户 × 12 张订单 = 60 行。每一行客户和每一行订单都配了一次对——这叫笛卡尔积。

ON 的作用就是从这 60 行里筛出"真的对应"的 12 行。

忘写 ON 不会报错,只会得到一个看起来很大、实际上全错的结果。所以看到行数异常膨胀,第一反应就是检查连接条件。


6.3 LEFT JOIN:宁可右表空着,也不丢左表 ​

业务上经常需要"以某张表为准":列出所有客户及其订单数,包括没下过单的。

LEFT JOIN 的规则是:左表的每一行都必须出现;右表有匹配就填上,没有就整行补 NULL。

sql
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。

sql
-- 找出从没下单的客户
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 多表连接:顺着外键一路串 ​

真实查询往往要串三四张表。比如"每张订单买了什么书":

sql
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 配聚合:每客户消费额 ​

sql
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。

两个要点:

  1. COUNT(DISTINCT o.id) 不能写成 COUNT(*)。orders 和 order_items 是一对多,连接后一张订单会变成多行,COUNT(*) 数出来是明细条数,不是订单数;
  2. GROUP BY 里带 c.id,避免同名客户被合并。

6.6 最容易错的地方:条件写进了 WHERE ​

需求:每个客户的有效消费额(排除退款订单),没下过单的也要显示 0。

下面这个写法看起来合理,实际是错的:

sql
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 ​

sql
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 是把它理顺的工具。

👉 第 7 章:子查询与 CTE

本文阅读--次|全站访问--次|访客--人