SQL guide · Filtering

SQL EXISTS: Check Whether a Subquery Returns Rows

The EXISTS operator takes a subquery and returns true if that subquery produces at least one row, and false if it produces none. It does not care about the values returned, only whether any row exists, which is why the select list inside is conventionally SELECT 1.

Most uses are correlated: the subquery refers to a column of the outer query, so it is effectively asked once per outer row, such as "does this customer have any orders?". Databases stop reading the subquery as soon as one matching row is found, so EXISTS is often efficient on large tables.

NOT EXISTS finds rows with no related match. It is generally preferred over NOT IN because it treats NULLs sanely. EXISTS also avoids duplicates that a join can create, since it checks presence without multiplying the outer rows.

Basic syntax

SELECT column1
FROM table1 t1
WHERE EXISTS (
  SELECT 1
  FROM table2 t2
  WHERE t2.key = t1.key
);

WHERE NOT EXISTS (subquery)

The subquery should include a correlation condition linking it to the outer query; without one, EXISTS is true for every row whenever the subquery returns anything. The select list is ignored, so SELECT 1 and SELECT * behave the same. Because only the presence of a row matters, the subquery is free to use any columns and joins it needs, as long as it is correctly tied to the outer row.

SQL EXISTS examples

Customers who have orders

For each customer the inner query looks for at least one order with the same id. A customer with many orders appears once, unlike a plain join.

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

Customers with no orders

NOT EXISTS flips the test to find customers who never ordered. This is the safe way to write an anti-join.

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

EXISTS with extra conditions

The subquery can filter further. This returns products that have been ordered in the last 30 days, using a date threshold inside the inner query.

SELECT p.product_id, p.name
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  WHERE oi.product_id = p.product_id
    AND o.order_date >= '2026-09-26'
);

EXISTS inside a DELETE

The operator also works in data-changing statements. This removes customers who have no orders and have not logged in since a cutoff date.

DELETE FROM customers
WHERE last_login < '2025-01-01'
  AND NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = customers.customer_id
  );

Where SQL dialects differ

PostgreSQL: The planner turns EXISTS and NOT EXISTS into semi-join and anti-join plans, so performance is usually on par with IN and joins.

MySQL: Since version 8.0 EXISTS subqueries are optimized as semi-joins. In older releases (5.5 and earlier) IN subqueries were sometimes slower than the same logic in EXISTS.

SQL Server: Standard behavior. EXISTS short-circuits on the first row, and SELECT * inside it does not cost extra because the columns are not evaluated.

SQLite: Supported, including correlated forms. The select list is ignored. Indexing the correlated column in the inner table matters a lot.

Oracle: Fully supported and the recommended pattern for presence tests. NOT EXISTS avoids the NULL problem of NOT IN.

Common mistakes

Omitting the correlation condition, which makes the subquery independent and EXISTS true for every row if the inner table has any data.
Selecting expensive expressions inside the subquery. They are ignored, so SELECT 1 is clearer.
Using NOT IN instead of NOT EXISTS when NULLs are possible, and getting an empty result.
Missing an index on the inner table join column, leading to slow per-row lookups on large tables.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator