Why doesn't this work with an ON clause, but does with a WHERE clause?

Question

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

My solution that doesn't work:

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
-- no where clause
GROUP BY h.hacker_id, h.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, h.hacker_id;

Solution that works:

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
-- the only real difference:
where s.score = d.score
group by h.hacker_id, h.name 
having count(*) > 1
order by count(*) desc, h.hacker_id;

Why does having s.score = d.score in the WHERE clause make the query work, but having it in an ON clause as part of an INNER JOIN make it not work (on HackerRank.com where the query comes from)? I thought for INNER JOINs it didn't matter, because the optimizer rearranges them at will?

How do I know when to use something like s.score = d.score (or whatever the columns are) in a WHERE clause and not in an ON clause as part of an INNER JOIN?

Answer 1

Why would your code be correct? The problem is not ON vs WHERE.

You:

ON h.hacker_id = c.hacker_id
ON c.difficulty_level = d.difficulty_level
ON s.score = d.score

Them:

on s.hacker_id = h.hacker_id 
on s.challenge_id = c.challenge_id
on c.Difficulty_level = d.Difficulty_level
where s.score = d.score

You have h & c for hacker_id but they have h & s. You are missing challenge_id.

You are correct that in a sequence of inner/cross joins conjuncts can appear anywhere among the ON & WHERE as long as aliases are in scope.

CROSS JOIN vs INNER JOIN in SQL
Is there any rule of thumb to construct SQL query from a human-readable description?

Answer 2

select hacker_id,name
from
(select t.hacker_id,t.name,count(*)
from
(select s.hacker_id,h.name,s.score,d.difficulty_level,c.difficulty_level,s.challenge_id
from submissions s INNER JOIN hackers h ON h.hacker_id = s.hacker_id
               INNER JOIN difficulty d ON s.score = d.score
               INNER JOIN challenges c ON s.challenge_id = c.challenge_id
               WHERE c.difficulty_level = d.difficulty_level) t
group by t.hacker_id,t.name
HAVING count(*) > 1
order by count(*) DESC,hacker_id);