Why can a correlated subquery in a WHERE clause sometimes be rewritten as a JOIN, but the rewrite changes results unless you add DISTINCT?
-- Original
SELECT d.name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.id
);