一、问题
在mysql左连接时,
- 写上where时,空值丢失,数据减少
- 不写where,只用and连接,主表条件不起作用,数据变多。
二、错误复现
1、当使用where+条件时,左连接查询空值丢失
SELECT u.id, u.authority_type, sum(complete_value)
FROM USER as u LEFT JOIN `performance_assess` p
ON p.user_id = u.id
where p.assess_time>'2020-09-01 12:00:00'
and u.authority_type = "USER"
GROUP BY u.id
结果:空值丢失,数据变少
2、当使用and连接条件而不写where,左表条件不其作用
SELECT u.id,u.authority_type,sum(complete_value)
FROM USER as u LEFT JOIN `performance_assess` p
ON p.user_id = u.id
and p.assess_time>'2020-09-01 12:00:00'
and u.authority_type = "USER"
GROUP BY u.id
结果:空值存在,但user的条件不起作用,数据变多
三、解决办法
通过on … and … where … 实现,
- on后面跟连接条件
- and后面跟从表条件:空值会被保留,多个从表条件时就and …and …
- where后面跟主表条件:过滤主表数据, 多个主表条件时就where…and …
SELECT u.id,u.authority_type,sum(complete_value)
FROM USER as u LEFT JOIN `performance_assess` p
ON p.user_id = u.id
and p.assess_time>'2020-09-01 12:00:00'
where u.authority_type = "USER"
GROUP BY u.id
结果: