EXISTS — test for the presence of related rows

EXISTS returns TRUE if its subquery returns at least one row. It stops as soon as the first match is found, which makes it efficient.

Syntax

SELECT col1, col2
FROM table_name t
WHERE EXISTS (
  SELECT 1
  FROM other_table ot
  WHERE ot.fk = t.id
);

The inner SELECT 1 is conventional — EXISTS only cares whether any row is returned, not what the columns contain.

Example: customers with at least one order

SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

NOT EXISTS — find unmatched rows

SELECT first_name, last_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

This returns customers who have never placed an order.

EXISTS vs IN vs LEFT JOIN

Critical rule: Prefer NOT EXISTS over NOT IN when the subquery could return NULLs — NOT IN silently returns zero rows if the list contains a NULL.

Tips

• EXISTS is a semi-join — it does not pull data from the subquery, only checks for existence.
• The correlated subquery (referencing the outer query) runs once per outer row. For large tables, a JOIN may be faster.