Databases & SQL Practice Question 1: Left Join Where Filter
Databases & SQL · Test 1 · question 1 of 10
A report must list every customer, with the id of their paid order if they have one and NULL otherwise. The data is:
customers(id, name): (1, 'Ana'), (2, 'Ben'), (3, 'Cai')
orders(id, customer_id, status): (10, 1, 'paid'), (11, 1, 'refunded'), (12, 2, 'refunded')
The current query is SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid'; and it returns only one row, Ana with order 10. Which change makes the query return exactly Ana/10, Ben/NULL and Cai/NULL?
Show answer and explanation
Answer:
A LEFT JOIN first finds the matching rows, then adds a row padded with NULLs for every left-hand row that matched nothing. The WHERE clause runs after the join. For Cai, the padded row has o.status = NULL, and NULL = 'paid' is unknown, not true, so the row is dropped. Ben's only joined row has status = 'refunded', so it is dropped as well. A WHERE filter on the right-hand table like this turns the LEFT JOIN back into an inner join. If you move the condition into ON, it becomes part of the match: Ana matches order 10, while Ben and Cai match nothing and are kept with a NULL order id. That gives exactly the three rows you want (option C).
Option A makes the query an inner join on purpose. It returns the same single row as before and never keeps customers without a paid order.
Option B is the usual half-fix. It brings Cai back, because Cai has no orders at all and so gets a NULL-padded row with status NULL. It still loses Ben: Ben *did* match an order (the refunded one), so Ben's joined row has status = 'refunded', which fails both conditions. The result is two rows, not three.
Option D reverses which side is preserved. A RIGHT JOIN keeps unmatched orders rows, not customers rows, and the WHERE filter still removes everything except order 10.
Reference: PostgreSQL: Table Expressions (joined tables, ON vs WHERE)