Explain INNER, LEFT and RIGHT joins. What is the classic LEFT JOIN mistake?

Summary

INNER JOIN returns only matching rows; LEFT JOIN returns every row from the left table with NULLs where there is no match; RIGHT JOIN is the mirror image. The classic mistake is filtering the right table in WHERE, which removes the NULL rows and silently turns the LEFT JOIN into an INNER JOIN.

Longer Answer

-- Wrong: customers without 2026 orders disappear
SELECT c.id, COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.id;

-- Right: put the condition in ON
SELECT c.id, COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at >= '2026-01-01'
GROUP BY c.id;

Also remember that COUNT(o.id) counts matches while COUNT(*) counts the NULL row too, and that joining two one-to-many relations multiplies rows (fan-out), inflating sums.

Key Points

• INNER vs LEFT vs RIGHT.
• WHERE on the right table negates the LEFT JOIN.
• COUNT(col) vs COUNT(*).
• Fan-out when joining two child tables.