CodePark/ questions / SQL queries & joins
START PRACTICINGPRACTICE
Backend question · Databases & SQL › SQL queries & joins

The goal is to list every customer, with their paid orders if they have any. What is wrong with this query?

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
The WHERE filter drops customers with no paid orders, because o.status is NULL or not 'paid' for them; move the condition into the ON clause

The WHERE filter drops customers with no paid orders. The fix is to move the condition into the ON clause.

Why it happens:
• WHERE runs after the join.
• For a customer with no matching order, o.status is NULL.
• So o.status = 'paid' is not true, and SQL removes the row.

This silently turns the left join into an inner join. If you write AND o.status = 'paid' in the ON clause, it filters only the orders being joined. Every customer stays.

"Nothing, it returns every customer" is wrong because of the WHERE problem above. "A LEFT JOIN cannot be combined with a WHERE clause" is also wrong: you can combine them, it just changes the result here.