DocsSubqueriesEXISTS & NOT EXISTS

EXISTS & NOT EXISTS

Subqueries

Concept

EXISTS tests whether a subquery returns at least one row. It returns TRUE if the subquery has results and FALSE if it is empty. The subquery is typically correlated — it references the outer query's current row. EXISTS does not care about the actual values returned, only whether any row exists.

NOT EXISTS is the negation: it returns TRUE when the subquery produces no rows. This is the standard way to find records with no matching counterpart — customers who have never ordered, products never sold, categories with no active items. It is generally more readable and often more efficient than LEFT JOIN ... IS NULL patterns.

One key advantage of EXISTS over IN is its handling of NULLs. IN can produce unexpected results when the subquery returns NULL values, because NULL comparisons yield UNKNOWN. EXISTS avoids this entirely since it only checks for row existence, not value equality.

Syntax

-- EXISTS: customers who have placed at least one order
SELECT c.customer_id, c.first_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- NOT EXISTS: customers who have never ordered
SELECT c.customer_id, c.first_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Products that have been ordered at least once
SELECT p.product_id, p.name, p.category
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.product_id
)
ORDER BY p.name;

-- Categories where every product costs more than $20
SELECT DISTINCT p.category
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM products p2
  WHERE p2.category = p.category
    AND p2.price <= 20
);

Common Pitfalls & Tips

1SELECT 1 in the EXISTS subquery is a convention. The actual select list does not matter — EXISTS only checks row existence. SELECT * works too but SELECT 1 makes intent clearer.
2EXISTS generally outperforms IN for large subquery results because the database can stop as soon as the first matching row is found.
3Forgetting the correlation (WHERE inner.col = outer.col) makes EXISTS always return TRUE for non-empty tables, which is almost certainly a bug.

Practice EXISTS queries