Existence tests and correlated questions

The idea

EXISTS asks whether its subquery produces at least one row. A correlated subquery can refer to the current outer row, allowing a query to select customers with an open order without copying each order into the output. Multiple matching orders still produce one outer customer row when the outer table contains one row per customer. This differs from an ordinary join that can multiply rows.

Absence tests need an explicit NULL policy. NOT IN can become unknown when the subquery includes NULL, even for a value that has no equal match. A correlated NOT EXISTS often expresses the intended absence relationship more directly. Specify the equality condition and decide how missing identifiers should behave rather than relying on the visual appearance of a short predicate.

WITH customers(id) AS (VALUES(1),(2),(3)), orders(customer_id) AS (VALUES(1),(1),(3))
SELECT c.id FROM customers c WHERE EXISTS(SELECT 1 FROM orders o WHERE o.customer_id=c.id) ORDER BY c.id;

id
1
3

EXISTS retains each matching customer once, despite two matching orders for customer one.