LEFT JOIN orders, then WHERE order status equals PAID?

The 30 second answer

It effectively becomes an inner join. Customers without matching orders have a null status, and null equals PAID is not true, so those customers disappear. To keep every customer, put the status filter in the join ON clause. Use where order id is null on purpose when you want an anti-join for customers with no orders.

It effectively becomes an INNER JOIN: customers without orders have o.status NULL, and NULL = 'PAID' is not true, so those rows are filtered out. To keep all customers, move the condition into the join: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'. (Use WHERE o.id IS NULL deliberately for anti-joins.)

FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'PAID'

Sources:

PostgreSQL — Joined tables (ON vs WHERE)

Sources (0)

See inline citations in the answer above.