Left join的坑，这么多年了居然才知道

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 条件中。避免结果数据多余或缺失。