# JOIN：两张表靠什么拼回一行，INNER 和 LEFT 差在哪条记录上

ON 怎么写配对条件、INNER JOIN 和 LEFT JOIN 留下的行差在哪，以及条件该放在 ON 还是 WHERE

> JOIN · LEFT JOIN · 多表关联 · 约 9 分钟 · 10 月 04 日

## 本篇要点

1. 两张表能拼在一起，是因为它们各有一列在讲同一件事，比如 orders.user_id 和 users.user_id；ON 后面写的就是这条配对规则。
2. JOIN 的产出是一对一对的行：对左表的每一行，去右表找满足 ON 的行，每配上一对就合成一行更宽的记录；右表同一个 key 有多行时，左表这一行会被复制多份，结果行数因此放大。
3. 两张表里同名的列（比如都叫 user_id）在语句里必须用“表别名.列名”限定，否则报 ambiguous 歧义错误；给表起短别名（FROM orders o）是为了少写前缀。
4. INNER JOIN 两边都配上才留行：orders 里 user_id = 9500 的订单在 users 里找不到，整行被丢掉；users 里一单没下的用户也不会出现。
5. LEFT JOIN 保证紧挨它左边的那张表每一行至少出现一次，右表配不上时用“右表所有列都是 NULL”的一行补位。
6. FROM orders LEFT JOIN users 和 FROM users LEFT JOIN orders 可能行数相同（这个例子里都是 9 行），但内容不同：前者多出“找不到用户的订单”，后者多出“没下过单的用户”。选哪张表写在 JOIN 左边，等于选哪份名单不许丢。
7. LEFT JOIN 之后用 WHERE 筛右表的列，会把右表列为 NULL 的那些行筛掉（NULL 比较得到未知，WHERE 不放行未知），效果等同于 INNER JOIN；MySQL 优化器会直接把这种语句改写成内连接。要表达“哪些右表行有资格配对”，条件应该写在 ON 里。
8. ON 决定右表哪些行有资格参与配对，WHERE 决定配对完之后哪些行留下；筛左表自己的列放在 WHERE 里没有这个问题。
9. 用 LEFT JOIN 加上 WHERE 右表 key IS NULL，可以专门查出配不上的行。

---

前三篇里，你写的每条查询都只在一张表里转：从 `orders` 取列、筛行、分组统计。可有一类问题它回答不了——“这些订单是哪位用户下的、从哪个渠道来的”。订单表里只有 `user_id` 这个数字，用户的昵称和注册渠道根本没记在订单表上，它们记在另一张表里。

把这两张表拼起来看，就是 `JOIN` 要做的事。

## 两张表长什么样，靠什么对得上

`users` 表长这样，每行是一位注册用户：

| user_id | user_name | signup_date | channel |
|---|---|---|---|
| 8823 | 阿May | 2024-01-05 | 应用商店 |
| 9107 | 老周 | 2023-11-22 | 好友推荐 |
| 9310 | 小林 | 2024-02-14 | 广告投放 |
| 9402 | Tony | 2024-03-08 | 应用商店 |

`orders` 表比上一篇又多了一行，现在有 9 行：

| order_id | user_id | city | amount | created_at |
|---|---|---|---|---|
| 1001 | 8823 | 杭州 | 129.00 | 2024-03-01 10:23:11 |
| 1002 | 9107 | 上海 | 58.50 | 2024-03-01 11:07:42 |
| 1003 | 8823 | 杭州 | 320.00 | 2024-03-02 09:15:03 |
| 1004 | 9310 | NULL | 76.00 | 2024-03-02 14:02:50 |
| 1005 | 9107 | 杭州 | 45.00 | 2024-03-03 08:40:12 |
| 1006 | 9107 | 上海 | 210.00 | 2024-03-03 19:22:05 |
| 1007 | 9310 | 上海 | 91.50 | 2024-03-04 12:05:33 |
| 1008 | 8823 | NULL | 19.90 | 2024-03-04 21:41:07 |
| 1009 | 9500 | 北京 | 199.00 | 2024-03-05 10:11:26 |

两张表都有 `user_id` 这一列，而且讲的是同一件事：订单表里它是“这单是谁下的”，用户表里它是“这是哪位用户”。这就是两张表能对上的地方。

顺手看清两处对不上：用户 9402（Tony）在 `orders` 里一单都没有；订单 1009 的 `user_id` 是 9500，`users` 里查不到这个人。这两个“对不上”不是脏数据，而是本篇的核心——两张表拼在一起时，它们各自会走向不同的结局。

## JOIN 的动作：拿 ON 的条件，一行一行去配

想给每笔订单补上用户名和渠道：

```sql
SELECT orders.order_id, orders.city, orders.amount,
       users.user_name, users.channel
FROM orders
JOIN users ON orders.user_id = users.user_id;
```

`ON` 后面写的是配对规则：`orders.user_id = users.user_id`。数据库拿到 `orders` 的每一行，就拿这一行的 `user_id` 到 `users` 里找相同的值，找到就把两行合成一行更宽的记录。1001 那一行的 `user_id` 是 8823，在 `users` 里找到阿May，于是拼出一行：订单 1001、杭州、129.00，加上阿May、应用商店。

**关键的一点：JOIN 的产出是一对一对的行，不是给原表“加了几列”。**阿May 在 `orders` 里有 3 单，她的昵称和渠道就会在结果里重复出现 3 次——这不是重复数据，而是同一个用户配上了 3 条不同的订单。同理，如果 `users` 里同一个 `user_id` 出现了两次，那一单就会配出两行，结果行数凭空翻倍。所以看到 JOIN 之后行数比预想的多，第一件要查的就是“配对另一边的 key 是不是唯一的”。

<figure>

```mermaid
flowchart LR
  L["orders 的每一行"] --> Q{"在 users 里能找到同一个 user_id 吗？"}
  Q -->|"找到"| M["和那行拼成一行更宽的记录"]
  Q -->|"找不到"| N["看用的是哪种 JOIN 决定去留"]
```

</figure>

## 表名太长，先起个短名字

上面的语句把 `orders.user_id`、`users.user_name` 一路写全，读起来很累。给表起个别名就清爽了：

```sql
SELECT o.order_id, o.city, o.amount,
       u.user_name, u.channel
FROM orders AS o
JOIN users AS u ON o.user_id = u.user_id;
```

`orders AS o` 的意思是：这条语句里，`orders` 就叫 `o`。之后的 `o.order_id` 就是“`orders` 表的 `order_id` 列”。这里的 `AS` 通常可以省掉，写成 `FROM orders o` 也一样。

那为什么要写 `o.` 这个前缀？试试不写：

```sql
SELECT user_id, o.city, u.user_name
FROM orders o JOIN users u ON o.user_id = u.user_id;
```

这会报错（MySQL 里是 `ERROR 1052: Column 'user_id' in field list is ambiguous`）。“ambiguous”意思是“有歧义”：两张表都有 `user_id`，你说的是哪一个？`ON` 里的 `o.user_id = u.user_id` 也正是因为两边都叫这个名字，才必须各带前缀指明。所以规则很简单：**两张表里同名的列，写的时候必须用“表别名.列名”限定**。

## INNER JOIN：两边都配上，这一行才留下

上面那种写法，在 MySQL 里 `JOIN` 和 `INNER JOIN` 是同义词 [1]。`INNER JOIN` 的规则是：只有 `ON` 条件成立的那一对行才进结果。9 行订单跑下来只剩 8 行——1009 那条订单的 `user_id` 是 9500，在 `users` 里找不到，条件不成立，这一整行被丢掉。

反过来从用户角度看也一样：Tony 一单没下，`INNER JOIN` 里他不会出现。**`INNER JOIN` 是两边一起筛，任何一边配不上，整行就不进结果。**

## LEFT JOIN：左边每一行至少出现一次

如果那 8 行订单是你这次分析的全部对象，1009 丢掉也许可以接受。但如果任务是“把 3 月的订单全部拉出来、能补的用户信息就补上”，用户信息查不到这件事不该让订单本身消失。这时候换成：

```sql
SELECT o.order_id, o.city, o.amount,
       u.user_name, u.channel
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;
```

结果变成 9 行。前 8 行和 `INNER JOIN` 一样，第 9 行对应订单 1009：订单那几列照常显示（北京、199.00），而 `u.user_name` 和 `u.channel` 两列填的是 `NULL`。

规则可以这样记：**`LEFT JOIN` 保证写在它左边的那张表（这里就是 `orders`）的每一行都在结果里至少出现一次。**右边配不上的时候，就用“所有列都是 `NULL`”的一行来补位。PostgreSQL 文档里的描述是：从左表出发逐行去找匹配，找不到就用空值代替右表的列 [3]；MySQL 文档的说法一样——`LEFT JOIN` 中右表没有匹配行时，会生成一行、其右表所有列都设为 `NULL` [1]。

现在把两张表换个位置写：

```sql
SELECT u.user_name, u.channel, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;
```

这一句的“保底名单”变成了 `users`：4 位用户谁都不能丢。结果是 9 行——阿May 3 行（1001、1003、1008）、老周 3 行、小林 2 行、Tony 1 行（订单那几列全是 `NULL`）。

**把这个结果和上一条 LEFT JOIN 放在一起看，是本篇最值得记住的一处对照：两句都返回 9 行，但内容完全不同。**前一句的第 9 行是“找不到用户的订单 1009”，后一句的第 9 行是“没有下过单的用户 Tony”。所以写 LEFT JOIN 时，真正要想清楚的不是“用不用 LEFT”，而是“哪张表是那份不许丢的名单”——答案由你把哪张表写在 `JOIN` 左边决定。

## 左边的行什么时候还是会消失

把 `LEFT JOIN` 理解成“左边一行都不会丢”，很容易接着写出这样的查询：

```sql
SELECT o.order_id, u.user_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.channel = '广告投放';
```

想法是“只看广告投放渠道来的订单”。可结果里只有 1004 和 1007——1009 那条订单还是消失了。

原因要回到上一篇讲 `NULL` 的那一节。1009 在 `LEFT JOIN` 里配不上 `users`，它的 `u.channel` 是 `NULL`。而 `NULL` 参与任何比较，结果都不是真也不是假，而是**未知**；`WHERE` 只放行判断为真的行，未知和假一样被丢掉。也就是说，`LEFT JOIN` 刚刚特意补出来的那一行，马上就被 `WHERE` 里针对右表列的普通条件筛掉了——效果和 `INNER JOIN` 一模一样。MySQL 的优化器甚至会在这种条件下，直接把语句改写成内连接来执行 [2]。

想让条件表达的是“只有广告渠道的用户才配得上”，就该把它写进 `ON`：

```sql
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
  AND u.channel = '广告投放';
```

这时 1009 留下来了：`ON` 条件不成立，它配不上任何一行，于是右表列补 `NULL`，但左边这行照样保留。

分界线只有一句话：**`ON` 决定右边的哪些行有资格参与配对；`WHERE` 决定配对完之后，结果里的哪些行留下。**想清楚你的条件筛的是“匹配资格”还是“最终结果”，就知道该写在哪边。

这里还要补一个容易误伤的边界：并不是“`LEFT JOIN` 之后就不能写 `WHERE`”。筛左表自己的列完全没问题，比如加上 `WHERE o.city = '上海'`——1009 的 `city` 是北京，被筛掉是你本来的意思，跟 `NULL` 没有关系。要小心的只是用 `WHERE` 去筛右表的列，除非你确实是想把整句变回 `INNER JOIN` 的效果。

## 顺手能查的一件事：哪些行对不上

`LEFT JOIN` 补 `NULL` 这个特性，正好可以用来专门找“配不上的行”：

```sql
SELECT o.order_id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;
```

结果就是 1009。判断“有没有配上”要用 `IS NULL` 而不是 `= NULL`，理由和上一篇里一样。

## 小结和练习

`ON` 写配对规则，两边的 key 要在讲同一件事。`INNER JOIN` 两边都配上才留行；`LEFT JOIN` 保证紧挨它左边的那张表一行不丢，右表配不上就补 `NULL`，所以选哪种 JOIN、先写哪张表，本质上是在回答“哪份名单不许丢”。把条件写在 `ON` 还是 `WHERE`，决定了它筛的是匹配资格还是最终结果。

练习题：取 2024 年 3 月杭州或上海的订单，带上用户昵称和渠道；并且要求即使查不到用户信息，订单也必须保留。

<details>
<summary>参考答案</summary>

```sql
SELECT o.order_id, o.city, o.amount, u.user_name, u.channel
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.created_at >= '2024-03-01'
  AND o.created_at <  '2024-04-01'
  AND (o.city = '杭州' OR o.city = '上海');
```

结果 6 行：1001、1002、1003、1005、1006、1007。1004 和 1008 的城市是空的，城市那一组条件得到未知，被筛掉；1009 是北京，被城市条件筛掉。

注意这个例子里 `LEFT JOIN` 和 `INNER JOIN` 的结果完全一样，因为筛剩的这 6 单，用户都在 `users` 里。要看出两者的差别，就得放宽到能留下 1009 的条件。

三组条件都写在 `WHERE` 而不是 `ON`，这是对的——它们筛的都是左表（订单）自己的列，没有哪一组会连带把补 `NULL` 的行误杀。

</details>

下一篇会接着看：当 `LEFT JOIN` 再配上 `GROUP BY` 做统计时，为什么同样想数“每个用户多少单”，`COUNT(*)` 和 `COUNT(u.user_id)` 会给出不同的答案。

## 术语表

- JOIN：把两张表的行按某个条件配成对，每配上一对就合成一行更宽的记录，写在 FROM 后面。
- 连接键（join key）：两张表里讲同一件事、用来判断“这两行能不能配上”的那一列，比如两边的 user_id。
- ON：写在 JOIN 后面、规定配对条件的部分，例如 ON o.user_id = u.user_id。
- 表别名：给表起的短名字（FROM orders o），让 o.order_id 代替 orders.order_id；两张表同名列必须这样限定才不会歧义。
- INNER JOIN：只有两边都配上的行才进结果，任何一边配不上的整行都丢掉。
- LEFT JOIN：保证写在它左边的那张表每行至少出现一次，右表配不上时用右表列全为 NULL 的一行补位。
- 补 NULL 行：LEFT JOIN 里右表没有匹配行时生成的、右表所有列为 NULL 的那一行。
- 条件写在 ON 还是 WHERE：ON 筛“右表哪些行有资格配对”，WHERE 筛“配对完之后哪些行留下”；把右表条件写进 WHERE 会让 LEFT JOIN 失去作用。

## 来源

1. [MySQL 8.4 Reference Manual: JOIN Clause — JOIN 与 INNER JOIN 同义、LEFT JOIN 无匹配行时右表列全部置为 NULL、ON 与 WHERE 的分工、用 WHERE right_tbl.id IS NULL 找出对不上的行](https://dev.mysql.com/doc/refman/8.4/en/join.html)
2. [MySQL 8.4 Reference Manual: Outer Join Optimization — WHERE 条件对生成的 NULL 行恒为假时，LEFT JOIN 会被改写成内连接](https://dev.mysql.com/doc/refman/8.4/en/outer-join-optimization.html)
3. [PostgreSQL Documentation: Joins Between Tables — 内连接丢弃配不上的行，左外连接从左边出发逐行找匹配、找不到就用空值代替右表列](https://www.postgresql.org/docs/18/tutorial-join.html)

---

原文：https://pangzhengboyin.com/articles/sql-join-inner-vs-left-37d5b89a

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com