> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL EXISTS

> How the MySQL EXISTS operator tests whether a subquery finds any row, how NOT EXISTS lists the rows with no match, and why it is safe where NOT IN is not.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

`EXISTS` asks a yes or no question: did this subquery find anything. It does
not care what the subquery returned, only whether there was a row, which makes
it the right tool when you want to test for a related row and none of its
columns.

Open the client with `mysql -u root -p sakila` before you start.

## How EXISTS works

```sql theme={null}
SELECT COLUMN_LIST
FROM TABLE_NAME AS OUTER_ALIAS
WHERE EXISTS (
  SELECT 1 FROM OTHER_TABLE AS INNER_ALIAS
  WHERE INNER_ALIAS.COLUMN_NAME = OUTER_ALIAS.COLUMN_NAME
);
```

Replace `COLUMN_LIST` with the columns you want, `TABLE_NAME` and `OTHER_TABLE`
with the 2 tables, `OUTER_ALIAS` and `INNER_ALIAS` with names for them,
`COLUMN_NAME` with the columns that link them, and the `WHERE` inside with
whatever makes a row related.

`SELECT 1` is the convention. The select list is ignored, so `SELECT 1`,
`SELECT *` and `SELECT column` behave identically, and `1` says plainly that
nothing is being read.

The subquery is correlated: it names the outer row. The server can stop at the
first matching row, because 1 is enough to answer yes.

## Examples

### 1) Rows that have a match

```sql theme={null}
SELECT l.name
FROM language AS l
WHERE EXISTS (SELECT 1 FROM film AS f WHERE f.language_id = l.language_id)
ORDER BY l.name;
```

```text theme={null}
+---------+
| name    |
+---------+
| English |
+---------+
1 row in set (0.00 sec)
```

1 of the 6 languages has films in it.

### 2) Rows that have none

`NOT EXISTS` is the other half, and it is how you write an anti-join.

```sql theme={null}
SELECT l.name
FROM language AS l
WHERE NOT EXISTS (SELECT 1 FROM film AS f WHERE f.language_id = l.language_id)
ORDER BY l.name;
```

```text theme={null}
+----------+
| name     |
+----------+
| French   |
| German   |
| Italian  |
| Japanese |
| Mandarin |
+----------+
5 rows in set (0.00 sec)
```

The other 5. The [LEFT JOIN](/docs/tutorial/left-join) lesson answers the same
question with `WHERE f.film_id IS NULL`, and the 2 forms return the same rows.

## NOT EXISTS is safe where NOT IN is not

The [IN](/docs/tutorial/in) lesson shows `NOT IN` returning no rows at all when the
list contains a NULL. `NOT EXISTS` has no such trap, because it never compares
values: it counts rows.

So when the subquery reads a column that can be NULL, prefer `NOT EXISTS`.
`NOT IN` is fine over a column declared `NOT NULL`, and a liability the moment
that stops being true.

## EXISTS never multiplies rows

A join to a table with several matching rows repeats the outer row once per
match, which the [INNER JOIN](/docs/tutorial/inner-join) lesson shows. `EXISTS`
cannot do that: it answers yes or no, so each outer row appears once however
many matches there are.

`EXISTS`, `IN` and a join can all express "has a related row", and which is
fastest depends on the data.
[Subqueries vs JOINs in MySQL](/docs/guides/subqueries-vs-joins) compares them with
plans.

## Summary

* Use `EXISTS` to test whether a subquery finds any row.
* Write `SELECT 1` inside, because the select list is ignored.
* Use `NOT EXISTS` for the rows with no match.
* Prefer `NOT EXISTS` over `NOT IN` when the subquery column can be NULL.
* Expect no row multiplication, which a join would give you.

## See also

* [Correlated subqueries](/docs/tutorial/correlated-subqueries) — the subquery form EXISTS uses
* [IN](/docs/tutorial/in) — the operator with the NULL trap
* [LEFT JOIN](/docs/tutorial/left-join) — the same question asked with a join
* [Subqueries vs JOINs in MySQL](/docs/guides/subqueries-vs-joins) — which shape the optimizer prefers