LEFT JOIN

Keeps every row of the left table and adds matching columns from the right table, with NULL where there is no match.

Formulaevery left row stays; no match → NULLs on the right

How we work it through

1
To keep buyers with no paid order, put the paid-status condition in ON.
2
Use WHERE o.order_id IS NULL to select buyers with no matching paid order.
3
Turn the NULLs into zeros only when zero is what they mean.

On the practice dataset

The query we run on Meridian, Atterna’s practice marketplace, and what it returns.

SELECT COUNT(*)
FROM meridian.buyers b
LEFT JOIN meridian.orders o ON o.buyer_id = b.buyer_id
                           AND o.status = 'paid'
WHERE o.order_id IS NULL;

Result3,348 buyers without a paid order. Put status = 'paid' in WHERE instead and they all vanish.

The common trap

A WHERE condition such as o.status = 'paid' rejects unmatched NULL rows. Put the paid-status condition in ON when buyers without a paid order must remain; WHERE o.order_id IS NULL then selects those unmatched buyers.

When an AI answers

An AI assistant

“All 3,652 buyers have at least one paid order.”

What it didn’t know: Its WHERE o.status = 'paid' threw away the rows the LEFT JOIN had kept.
Ask back: “Move the status filter into ON — how many buyers have no paid order?”

Paste a real AI answer into the checker — it writes these questions for you →