Skip to content
Tutorials Center
My learning

Topics

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.

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

Try it

SQL Customers who bought a Science book ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Books nobody has ordered ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL The select list is ignored ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
[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 * and SELECT NULL behave identically. SELECT 1 is 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 EXISTS is NULL-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. EXISTS returns 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 turn EXISTS and IN into 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 than COUNT(*) on big tables.
  • Don't use COUNT(*) > 0 in a subquery when EXISTS says the same thing and can stop early.