left join是很常见的多表关联查询,比如服装类下面有多少件衣服,衣服对应的有多少图片,每个衣服下面有多少个评论,等等。但是由于对join、on的用法不熟悉,可能会导致得不到预期的效果。
假如一个产品管理业务,有两张表category(分类)和product(产品),具体信息如下:
category数据
id | type |
---|---|
1 | 服装 |
2 | 鞋 |
3 | 手机数码 |
4 | 家用电器 |
product数据
id | category_id | name | price |
---|---|---|---|
1 | 1 | 羽绒服 | 888.88 |
2 | 1 | 棉服 | 666.66 |
3 | 2 | 耐克鞋 | 660 |
4 | 2 | 休闲鞋 | 300 |
5 | 3 | oppoR9 | 1200 |
6 | 3 | vivo | 2000 |
- 需求一:查询每个类别及其价格大于800的的产品总数量
- 需求二:查询服装的产品总数量
需求一大多数人是这两种sql:
第一种sql
SELECT
c.type,COUNT(p.name) sum
FROM category c
LEFT JOIN product p ON c.id=p.category_id
WHERE p.price>800
GROUP BY c.id
运行结果
type | sum |
---|---|
服装 | 1 |
手机数码 | 2 |
第二种sql
SELECT
c.type,COUNT(p.name) sum
FROM category c
LEFT JOIN product p ON c.id=p.category_id AND p.price>800
GROUP BY c.id
运行结果
type | sum |
---|---|
服装 | 1 |
鞋 | 0 |
手机数码 | 2 |
家用电器 | 0 |
需求二大多数人是这两种sql:
第一种sql
SELECT
c.type,COUNT(p.name) sum
FROM category c
LEFT JOIN product p ON c.id=p.category_id AND c.type='服装'
GROUP BY c.id
运行结果
type | sum |
---|---|
服装 | 2 |
鞋 | 0 |
手机数码 | 0 |
家用电器 | 0 |
第二种sql
SELECT
c.type,COUNT(p.name) sum
FROM category c
LEFT JOIN product p ON c.id=p.category_id
WHERE c.type='服装'
GROUP BY c.id
运行结果
type | sum |
---|---|
服装 | 2 |
正确答案都是第二种。
因为需求一第一种方式由于在where条件中对右表进行限制,导致数据缺失;
需求二第一种方式由于在on条件中对左表进行限制,并没有对最终的结果进行限制,导致数据多余。
总结:
在使用 left join 时,如果对左表过滤必须放在 where 条件中;如果对右表过滤放在 on 条件中。避免结果数据多余或缺失。