SQL Reference ยท Joins and Subqueries
SQL EXISTS
Reference for SQL EXISTS and NOT EXISTS: test whether a subquery returns any rows, write semi-joins and anti-joins, and why the select list does not matter.
EXISTS (subquery) is a predicate that is true if the subquery returns at least one row and false if it returns none. It does not care about the values, only about whether a row exists, and it stops as soon as it finds the first one.
That makes it the natural tool for "does a related row exist?" questions: customers who have placed an order, projects with no assigned employees, books that were never sold. NOT EXISTS answers the opposite question and, unlike NOT IN, is not confused by NULL.
SELECT name FROM customers AS c WHERE EXISTS ( SELECT 1 FROM orders AS o WHERE o.customer_id = c.id );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
[NOT] EXISTS (SELECT ... FROM ... [WHERE ...])
| Part | Description |
|---|---|
subquery |
Any SELECT. Usually correlated to the outer query through its WHERE clause. |
Returns: TRUE or FALSE, never NULL. An empty subquery gives FALSE; any row, even one containing only NULLs, gives TRUE.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | EXISTS works as a value too: it returns 1 or 0 and can appear in the select list. |
| PostgreSQL | Same. Returns a real boolean. |
| MySQL | Same; the result is 1 or 0. |
| SQL Server | EXISTS is a predicate only. It cannot appear in the select list; wrap it in CASE WHEN EXISTS (...) THEN 1 ELSE 0 END. |
| Oracle | A predicate only (no boolean in SQL until 23ai), so use CASE WHEN EXISTS (...). |
Usage notes
- The select list is irrelevant.
SELECT 1,SELECT *andSELECT NULLbehave identically.SELECT 1is the common convention. - Correlation is the point. Without a link to the outer row (
o.customer_id = c.id), the subquery is either always or never empty, so every outer row is kept or none are. NOT EXISTSisNULL-safe. It never returns unknown, so it avoids the NOT IN trap. That is why it is the preferred form for anti-joins.- Semi-join.
EXISTSreturns each outer row once, even when several inner rows match. A join would repeat the outer row. - Performance. The database stops at the first matching row. Index the correlated column (
orders.customer_id) so each check is a quick lookup. Modern optimizers turnEXISTSandINinto the same semi-join plan. - Checking a whole table.
SELECT EXISTS (SELECT 1 FROM t)is a cheap "is the table empty?" test, much cheaper thanCOUNT(*)on big tables. - Don't use
COUNT(*) > 0in a subquery whenEXISTSsays the same thing and can stop early.