Use EXISTS when you only need to know whether a related row exists
A list needs customers who have at least one overdue invoice. Joining every overdue invoice creates duplicate customer rows, then DISTINCT is added to hide them. An EXISTS condition states the requirement directly.
Express the existence test
SELECT c.id, c.name
FROM customers AS c
WHERE c.tenant_id = 42
AND EXISTS (
SELECT 1 FROM invoices AS i
WHERE i.customer_id = c.id
AND i.paid_at IS NULL
AND i.due_at < CURRENT_TIMESTAMP
);
This returns each qualifying customer row without projecting invoice rows. It does not calculate the overdue amount. Use a separate aggregate if the interface needs a balance as well.
Support the lookup with appropriate indexes
The database still needs to locate related invoices efficiently. Inspect the plan and representative data before choosing an index. Customer relationship, unpaid filtering, and due-date range all influence the access pattern.
Do not assume EXISTS is universally faster than every equivalent join. The optimizer can transform queries, and data distribution matters. Its first benefit here is expressing the correct result shape.
Test no invoices, only future invoices, one overdue invoice, and several overdue invoices. Verify each qualifying customer appears once. Use a fixed cutoff timestamp in tests rather than the real clock so a fixture does not change status overnight. In application code, use bound parameters for the tenant and cutoff, with a deliberate timezone convention.