错题难题丨join后加筛选条件和where后加筛选条件的区别

在where后加筛选条件的话，就相当于在临时表创建后再一起筛选，

如果有多个结果的话，多个结果的筛选条件要一起为真才能被筛选出来，

那么如果一个项目输出两个结果，一个结果为真/有结果一个结果为假/没有结果的情形将会被筛除，

只会输出两个结果都为真/有结果的项目

但是在join加筛选条件的话，像leftjoin的话右边就算为假都会被保留 如示例就输出了exam_cnt有结果，question_cnt无结果的情况

SELECT
a.uid,
COUNT(DISTINCT a.exam_id)exam_cnt,
COUNT(DISTINCT question_id,b.submit_time)question_cnt
FROM exam_record a
LEFT JOIN practice_record b on a.uid=b.uid and YEAR(a.submit_time)=2021 AND year(b.submit_time)=2021
WHERE a.uid in (SELECT 
uid
FROM
user_info
LEFT JOIN exam_record USING (uid)
LEFT JOIN examination_info USING (exam_id)
WHERE level=7
AND difficulty='hard'
AND tag='sql'
GROUP BY uid
HAVING AVG(score)>80)
GROUP BY uid
order by exam_cnt , question_cnt desc;