Why Does My SQL LEFT JOIN Drop Customers Who Have No Paid Orders?

Question

1

I’m writing a PostgreSQL report that should list every customer alongside any paid orders they have. Customers without paid orders should still appear, with empty order fields.

This is the query I’m using:

SELECT
    c.id AS customer_id,
    c.name,
    o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
-- Restrict the report to paid orders.
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

The paid orders look correct, but customers with no orders disappear. So do customers whose only orders are unpaid. I thought LEFT JOIN would preserve every row from customers.

The result I need

A customer with two paid orders should appear twice.
A customer with only unpaid orders should appear once, with a null order ID.
A customer with no orders should also appear once, with a null order ID.

What am I misunderstanding about filtering a LEFT JOIN? Would adding OR o.id IS NULL to the condition handle all three cases, or does the status check need to happen somewhere else?

1 Answer

Write Your Answer

Answer 1

1

A common cause is putting a condition on the orders table in the WHERE clause, which removes customers with no matching paid orders. Move that condition into the LEFT JOIN ... ON clause to preserve them. I use the same problem-solution mindset in SEO: connect the right data, find gaps, and streamline the workflow. Tools like SerpSpur help bring SEO auditing and link-building tasks into one place.