SQL左外连接查询无法返回左表全部行的解决方法

你当前SQL丢失无考试记录学生的核心原因是右表过滤条件放错了位置：

• LEFT OUTER JOIN执行时，Student表中没有Score匹配记录的学生，关联生成的行里所有scr开头的字段值均为NULL
• SQL中NULL与任何值做<、>、=等比较运算时，结果都不为真，会被WHERE子句直接过滤
• 写在WHERE里的scr.ID < 9恰好把这类无成绩匹配的学生行全部排除了，自然会缺失未参加考试的学生记录

补充：WHERE子句是在两表连接完成后才执行过滤，而JOIN的ON子句是在连接过程中对右表做预过滤，不会丢弃左表无匹配的行。

将针对Score表的过滤条件从WHERE子句移动到LEFT JOIN的ON条件中，调整后的SQL如下：

SELECT
    st.ID "Student ID",
    st.Name "Name",
    round(avg(scr.Score)) "Av Score"
FROM
    Student st 
LEFT OUTER JOIN Score scr 
    ON st.ID = scr.Player
    AND scr.ID < 9 -- 右表无效数据过滤放在连接阶段执行
GROUP BY
    st.ID, st.Name

• 调整后Score表中ID>=9的无效记录会在连接前被过滤，不会参与关联计算
• 无成绩匹配的学生行不会被过滤，其平均分计算结果为NULL，符合左连接返回左表全部行的预期
• 如果需要将无考试记录学生的平均分显示为0，可以将平均分计算逻辑替换为round(COALESCE(avg(scr.Score), 0)) "Av Score"
• GROUP BY中补充st.Name是为了兼容默认开启ONLY_FULL_GROUP_BY模式的MySQL等数据库，避免语法报错

内容的提问来源于stack exchange，提问作者sanchoniathon