EXISTS Operator
SQL · SQL Subqueries
Theory
EXISTS (subquery) is TRUE if the subquery returns at least one row and FALSE if it returns none. It does not care what the rows contain, which is why people write SELECT 1 inside it. It is almost always correlated: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) keeps customers who have at least one order.
The engine can stop at the first matching row, so EXISTS is efficient, and each outer row appears once even if it has many matches, unlike a join, which repeats it. NOT EXISTS keeps rows with no match, like customers who never ordered. It is the safest way to write an anti-join: NOT IN returns nothing if the subquery contains a NULL, while NOT EXISTS handles NULLs correctly. Most modern optimisers run IN and EXISTS similarly, so choose by meaning and NULL safety.
Why we use it
"Does a related row exist?" is a very common question: customers with orders, products never sold, users without a login. EXISTS answers it without duplicating rows or bringing in extra columns.
When to use it
Use EXISTS / NOT EXISTS to filter rows by whether related rows exist. Use a JOIN when you need columns from the other table, and IN for a short fixed list of values.
How it works
- Take an outer row (customer c)
- Run the inner query with c.id
- Inner query found a row? Yes: EXISTS is TRUE: keep the customer. No: FALSE: discard it.
- Stop scanning orders at the first match
- Move to the next customer
Syntax
SELECT columns FROM table
WHERE EXISTS (SELECT 1 FROM other WHERE condition);Example
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Key points
- Stops scanning as soon as one match is found
- Commonly paired with a correlated subquery
- Each outer row appears at most once, unlike a JOIN
- NOT EXISTS is the NULL-safe way to find rows with no match
- The SELECT list inside does not matter; SELECT 1 is the convention