Keep unmatched rows when filtering a LEFT JOIN
A report should show every customer, including customers without paid invoices. Placing invoices.status = 'paid' in the WHERE clause removes the unmatched rows, because their joined invoice values are NULL.
Put the child filter in the join condition
SELECT customers.id, invoices.id AS paid_invoice_id
FROM customers
LEFT JOIN invoices
ON invoices.customer_id = customers.id
AND invoices.status = 'paid'
WHERE customers.tenant_id = 42;
This preserves customers with no matching paid invoice. It can still return several rows per customer if several paid invoices match. If the desired result is one row per customer, use an aggregate, an existence expression, or another deliberate projection.
Tenant relationships must be trustworthy
If customer IDs are globally unique and foreign keys enforce the relationship, the join can follow that key. If your schema uses composite tenant-local identifiers, include the tenant relationship in the join as well. Do not assume a display code is globally unique.
Test a customer with no invoices, one with only unpaid invoices, and one with multiple paid invoices. Check both inclusion and row multiplicity. A LEFT JOIN does not mean “one row from the left table” in every result; it means unmatched left rows are retained subject to later filtering.
When a report unexpectedly loses zero-activity customers, inspect WHERE predicates on the right-hand table before adding placeholder records to the database.