【MySQL】外连接 where 和 on 的区别

力扣题

1、题目地址

1158. 市场分析 I

2、模拟表

User

Column Name	Type
user_id	int
join_date	date
favorite_brand	varchar

• user_id 是此表主键（具有唯一值的列）。
• 表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。

Orders

Column Name	Type
order_id	int
order_date	date
item_id	int
buyer_id	int
seller_id	int

• order_id 是此表主键（具有唯一值的列）。
• item_id 是 Items 表的外键（reference 列）。
• （buyer_id，seller_id）是 User 表的外键。

Items

Column Name	Type
item_id	int
item_brand	varchar

• item_id 是此表的主键（具有唯一值的列）。

3、要求

编写解决方案找出 每个用户的注册日期 和 在 2019 年 作为 买家的 订单总数。
以 任意顺序 返回结果表。
查询结果格式如下。

示例 1:

输入：
Users 表:

user_id	join_date	favorite_brand
1	2018-01-01	Lenovo
2	2018-02-09	Samsung
3	2018-01-19	LG
4	2018-05-21	HP

Orders 表:

order_id	order_date	item_id	buyer_id	seller_id
1	2019-08-01	4	1	2
2	2018-08-02	2	1	3
3	2019-08-03	3	2	3
4	2018-08-04	1	4	2
5	2018-08-04	1	3	4
6	2019-08-05	2	2	4

Items 表:

item_id	item_brand
1	Samsung
2	Lenovo
3	LG
4	HP

输出：

buyer_id	join_date	orders_in_2019
1	2018-01-01	1
2	2018-02-09	2
3	2018-01-19	0
4	2018-05-21	0

4、代码编写

正确写法

SELECT a.user_id AS buyer_id,a.join_date, count(b.buyer_id) AS orders_in_2019
FROM Users aLEFT join Orders b ON a.user_id = b.buyer_id AND YEAR(b.order_date) = '2019'
GROUP BY a.user_id

| buyer_id | join_date  | orders_in_2019 |
| -------- | ---------- | -------------- |
| 1        | 2018-01-01 | 1              |
| 2        | 2018-02-09 | 2              |
| 3        | 2018-01-19 | 0              |
| 4        | 2018-05-21 | 0              |

错误写法

SELECT a.user_id AS buyer_id,a.join_date, count(b.buyer_id) AS orders_in_2019
FROM Users aLEFT join Orders b ON a.user_id = b.buyer_id
WHERE YEAR(b.order_date) = '2019'
GROUP BY a.user_id

| buyer_id | join_date  | orders_in_2019 |
| -------- | ---------- | -------------- |
| 1        | 2018-01-01 | 1              |
| 2        | 2018-02-09 | 2              |

错误分析（网友回答）

外连接时要注意 where 和 on 的区别：

• on 是在连接构造临时表时执行的，不管 on 中条件是否成立都会返回主表（也就是 left join 左边的表）的内容，
• where 是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。
• 如果这里用的是 where year(order_date)='2019' 那么得到的结果将会把不满足条件的 user_id 为 3，4 的行给删掉。
• 用 on 的话会保留 user_id 为 3，4 的行。