EXISTS and NOT EXISTS

Tests whether a correlated subquery returns at least one row: semi-joins and anti-joins.

Syntax

WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id [AND ...])
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)

EXISTS is TRUE as soon as the subquery finds one row; what it selects does not matter (SELECT 1 is conventional).
It never duplicates outer rows, unlike a join to a one-to-many table — a semi-join.
NOT EXISTS is the anti-join: rows of a with no matching row in b.
NULL-safe: EXISTS returns only TRUE or FALSE, never NULL. That is why NOT EXISTS is preferred over NOT IN (subquery), which returns nothing if the subquery contains a NULL.
The subquery must be correlated (refer to the outer row); otherwise it is the same answer for every row.

Examples

Customers with at least one cancelled order starter

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND o.status = 'cancelled'
)
ORDER BY c.customer_id;

Each customer appears once, however many cancelled orders they have.

Products never sold (anti-join) shop

SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id)
ORDER BY p.product_id;

Returns the products with no order line at all.

Accounts without any activity in 2025 bank

SELECT a.account_id, a.status
FROM accounts a
WHERE NOT EXISTS (
  SELECT 1 FROM transactions t
  WHERE t.account_id = a.account_id
    AND t.posted_at >= '2025-01-01' AND t.posted_at < '2026-01-01'
)
ORDER BY a.account_id
LIMIT 10;

Extra conditions inside the subquery define what counts as a match.

Pitfalls

Forgetting the correlation (WHERE EXISTS (SELECT 1 FROM orders) is true for every row if orders is non-empty).
Using NOT IN with a nullable subquery column instead of NOT EXISTS.
Joining instead of EXISTS when you only need to know whether a match exists (duplicates).