You want every customer, with their orders if they have any, so you write a LEFT JOIN:
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
Customers without paid orders disappear from the result. It behaves exactly like an INNER JOIN. This is one of the most common SQL mistakes, and the fix is to move one condition.
Why the rows vanish
A LEFT JOIN runs in two steps:
- Join: for each customer, find matching orders using the
ONcondition. If none match, keep the customer anyway, withNULLin everyo.*column. - Filter: apply
WHEREto the joined rows.
For a customer with no orders, o.status is NULL. NULL = 'paid' isn't true (it's unknown), so WHERE throws the row away, which is exactly the row the LEFT JOIN was keeping for you.
Any WHERE condition on the right-hand table that NULL fails (=, <, >, LIKE, IN, BETWEEN) quietly turns the outer join into an inner join. Many optimizers even rewrite it as an inner join internally.
Fix: put right-table conditions in ON
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o
o.customer_id c.id
o.status
c.country ;
