I was expecting both queries to give the same outcome. Why is this not the case?
select count(*) as c,number from numbers group by number having count(*) > 1;
select * from numbers as a INNER JOIN numbers as b
on a.number = b.number and a.id < b.id;
Because the first query will always return one row per number.
The second may return more rows with the same number (if you have triplicates etc)
try the below to see the difference
SELECT a.number, count(*) FROM (select * from numbers as a INNER JOIN numbers as b on a.number = b.number and a.id < b.id) c GROUP BY a.number -- having count(*) > 1 -- ORDER BY COUNT(*) DESC