mysql-在left join中where条件跟on条件的位置放错导致运行结果不符合预期效果

场景：

示例：用户信息表user_profile

id	device_id	gender	age	university	gpa	active_days_within_30
1	2138	male	21	北京大学	3.4	7
2	3214	male		复旦大学	4.0	15
3	6543	female	20	北京大学	3.2	12
4	2315	female	23	浙江大学	3.6	5
5	5432	male	25	山东大学	3.8	20
6	2131	male	28	山东大学	3.3	15
7	4321	female	26	复旦大学	3.6	9

示例：question_practice_detail

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15

根据示例，你的查询应返回以下结果：

device_id	university	question_cnt	right_question_cnt
3214	复旦大学	3	0
4321	复旦大学	0	0

注意：对于在8月份没有练习过的用户，答题数结果返回0

问：复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况

---

思路：

• 限定条件：需要是复旦大学的（来自表user_profile.university），8月份练习情况（来自表question_practice_detail.date）
• 从date中取month：用month函数即可；
• 总题目：count(question_id)
• 正确的题目数：sum(if(qpd.result='right', 1, 0))
• 按列聚合：需要输出每个用户的统计结果，因此加上group by up.device_id

出现问题：

修改前的SQL：

select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id 
//注意这里两个条件的位置
where up.university = '复旦大学' and month(qpd.date) = 8
group by up.device_id

运行结果：

 与预期不符，并没有出现答题为0的学生

修改后SQL：

select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
//再次注意两个条件的位置
  on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'
group by up.device_id

运行结果：

符合预期 

---

原因分析：

mysql left join中where和on条件的区别

    1.多表left join是会生成一张临时表，并返回给用户
    2.where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。
    3.on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL
    4.on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。