# SQL EXISTS: Check Whether a Subquery Returns Rows

- URL: https://betteranalyst.com/sql/exists

SQL guide · Filtering

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

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL NOT](https://betteranalyst.com/sql/not)
- [SQL ANY](https://betteranalyst.com/sql/any)
- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL LEFT JOIN](https://betteranalyst.com/sql/left-join)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)