EXISTS and NOT EXISTS

Explanation

EXISTS checks whether a subquery returns at least one row. It doesn't care about the values, only whether any row exists. That makes it ideal for "find rows that have (or don't have) a related row somewhere else" questions.

NOT EXISTS is the negative form: "find rows where no related row exists."

Crucially, EXISTS is correlated, the subquery references the outer query and is evaluated per outer row, stopping as soon as it finds a match.

Syntax

SELECT col
FROM table_a a
WHERE EXISTS (
  SELECT 1 FROM table_b b WHERE b.key = a.key
);

Interactive Example

Find customers who have placed at least one order. Then find customers who have not placed any order using NOT EXISTS.

Common Mistakes

• Using = with EXISTS. EXISTS is a boolean test, not a value, never write WHERE x = EXISTS (...).
• Reaching for NOT IN with NULLs. NOT IN (subquery) returns nothing if the subquery has a NULL; prefer NOT EXISTS.
• Selecting heavy columns inside EXISTS. It's wasted work; SELECT 1 is the convention.

Best Practices

• Use EXISTS for "has related rows" checks against large tables.
• Prefer NOT EXISTS over NOT IN to avoid NULL pitfalls.
• Add the join condition (b.key = a.key) so the subquery is properly correlated.

Practice Question

Using EXISTS, list products (products table) that have never been ordered, i.e. no orders row references that product_id.

Summary

EXISTS returns true if a correlated subquery yields any row, making it a fast, NULL-safe way to test relationships. NOT EXISTS is the reliable choice for "does not appear" conditions.

FAQ