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
1
2
3
4
5
6
1
2
3
4
5
6
运行结果
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
1
2
3
4
5
1
2
3
4
5
运行结果
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
1
2
3
4
5
1
2
3
4
5
运行结果
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
1
2
3
4
5
6
1
2
3
4
5
6
运行结果
type sum
服装 2
正确答案都是第二种。
因为需求一第一种方式由于在where条件中对右表进行限制，导致数据缺失；
需求二第一种方式由于在on条件中对左表进行限制，并没有对最终的结果进行限制，导致数据多余。
总结：
在使用 left join 时，如果对左表过滤必须放在 where 条件中；如果对右表过滤放在 on 条件中。避免结果数据多余或缺失。
网友评论