I need a report that lists every customer and any paid orders they have. Customers without a paid order should still appear, with NULL in the order columns.
This is the query I’m using:
SELECT
c.id AS customer_id,
o.id AS order_id,
o.status
FROM customers AS c
-- Keep customers even when there is no matching order.
LEFT JOIN orders AS o
ON o.customer_id = c.id
-- Only paid orders should appear in the report.
WHERE o.status = 'paid';
Instead, the result contains only customers with paid orders. I thought LEFT JOIN would preserve every row from customers.
The case I’m unsure about
A customer with only an unpaid order also needs to appear, but without that unpaid order’s details. Would adding OR o.status IS NULL handle that case, or would it only help customers who have no orders at all?
How does filter placement in outer joins affect this result? I’d like to understand the distinction between a condition in ON and one in WHERE, rather than just patching this query.