为何INNER JOIN中ON子句用s.score=d.score失效，WHERE子句却有效？

问题背景 #

Julia刚完成一场编程竞赛，需要生成排行榜！请编写查询语句，打印出在多个挑战中获得满分的黑客的hacker_id和姓名。结果按获得满分的挑战总数降序排序；若多个黑客满分挑战数相同，则按hacker_id升序排序。

无效解决方案 #

SELECT h.hacker_id, h.name
FROM Hackers h
JOIN Challenges c
ON h.hacker_id = c.hacker_id
JOIN Difficulty d
ON c.difficulty_level = d.difficulty_level
JOIN Submissions s
ON s.score = d.score
-- 缺少WHERE子句
GROUP BY h.hacker_id, h.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, h.hacker_id;

可行解决方案 #

select h.hacker_id, h.name 
from Submissions as s 
join Hackers as h 
on s.hacker_id = h.hacker_id 
join Challenges as c 
on s.challenge_id = c.challenge_id
join Difficulty as d 
on c.Difficulty_level = d.Difficulty_level
-- 关键区别：将满分条件放在WHERE子句
where s.score = d.score
group by h.hacker_id, h.name 
having count(*) > 1
order by count(*) desc, h.hacker_id;

疑问解答 #

你提到的INNER JOIN的ON和WHERE条件等价，是在关联逻辑和过滤逻辑清晰区分的前提下才成立，但你的无效写法里，把s.score = d.score放在Submissions和Difficulty的JOIN ON条件中，会导致核心逻辑错误：

1. 关联顺序与逻辑错误：无效写法的表连接顺序是Hackers → Challenges → Difficulty → Submissions，用s.score = d.score关联Submissions和Difficulty时，没有先把Submissions和对应的Challenge关联，这会让Submissions和Difficulty产生错误的笛卡尔积——一个提交会和所有难度等级匹配其分数的难度记录关联，而非它对应的那道挑战的难度记录，最终统计的数量完全失真。

2. 正确逻辑路径：必须先通过Submissions.challenge_id关联到Challenges，再通过Challenges.difficulty_level关联到Difficulty，最后用WHERE过滤出提交分数等于该挑战满分的记录，这样才能准确统计每个黑客的满分挑战数。

3. 条件放置判断原则：

   • 当条件是表之间的关联逻辑（比如主键/外键关联、明确的父子表对应关系），放在JOIN ON中；
   • 当条件是过滤符合业务要求的记录（比如筛选满分提交），放在WHERE中；
   • 若过滤条件涉及多表关联后的结果，必须确保关联逻辑先完成，再用WHERE过滤，否则会出现错误的关联匹配。

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