Which fix removes the bug?
SELECT u.name, COUNT(*) AS orders FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id; -- users with no orders show 1, not 0
- Switch to an
INNER JOIN - Use
COUNT(DISTINCT u.id) - Add
HAVING COUNT(*) >= 0 - Count a right-side column:
COUNT(o.id)
A user with no orders still gets one joined row (with NULLs), and COUNT(*) counts it. COUNT(o.id) skips the NULL and gives 0.
Play Problem Solving ▸