mysql左连接null值丢失、条件不起作用的问题

一、问题

在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 

结果：