SQL TOPIC

SQL EXISTS and NOT EXISTS: test whether related rows exist

EXISTS answers a yes-or-no question across tables without multiplying the outer rows.

Use EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) to keep customers who have at least one order. The inner query is correlated: it checks each outer customer.

Use NOT EXISTS for missing relationships. It is safer than NOT IN when the inner column may contain NULL.

Worked example

Which business customers have never ordered?

SELECT c.name, c.email FROM customers c
WHERE c.segment = 'business'
AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Result on the sample data

name	email
Sean Byrne	sean.byrne@example.ie

Common mistakes

Forgetting the link between inner and outer tables, so the result is all-or-nothing.
Using an INNER JOIN to find customers with no orders.