SQL EXISTS

EXISTS tests whether a subquery returns any rows. It returns TRUE as soon as one match is found — without scanning the rest.

Example

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 1000
);

"Show every customer who has at least one order over $1,000".

SELECT 1 is conventional

What the inner SELECT returns doesn't matter — only whether there's any row. SELECT 1, SELECT *, and SELECT NULL all behave identically. Most teams use SELECT 1 as a hint to the reader.

EXISTS vs IN

NOT EXISTS — the safe anti-join

-- Customers who have never placed an order
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTS is the safest alternative to NOT IN when NULLs are possible.

Example

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

Exercise

Test yourself