MySQL EXISTS

In MySQL, the EXISTS operator are used to test whether a subquery returns rows or not. If a subquery returns at least one row, EXISTS returns TRUE. Otherwise, it returns FALSE.

EXISTS syntax

The EXISTS operator is a unary operator, which requires a subquery as a operand. Here is the syntax of the EXISTS operator:

SELECT column_name
FROM table_name
WHERE EXISTS(subquery);

Here:

EXISTS is used in the WHERE clause.
EXISTS is a unary operator, it requires a subqueryas a operand.
If the subquery returns at least one rows, EXISTS returns TRUE. Otherwise it returns FALSE.
The subquery’s select list does not affect the result; MySQL checks whether it returns at least one row.
EXISTS does not care about the number of columns in the subquery, it only cares whether the subquery returns rows or not. Therefore, in the subquery, SELECT 1, SELECT *, or SELECT column_list do not affect the result of the EXISTS operator.
NOT EXISTS is the negative operation of EXISTS.

EXISTS examples

Let us see some examples to understand the EXISTS operator.

In the following example, we use film and language tables as demonstrations.

`EXISTS` Example

The following statement finds all languages from the language table, that have at least one related film in the film table.

SELECT *
FROM language
WHERE EXISTS(
    SELECT 1
    FROM film
    WHERE film.language_id = language.language_id
  );

+-------------+---------+---------------------+
| language_id | name    | last_update         |
+-------------+---------+---------------------+
|           1 | English | 2006-02-15 05:02:19 |
+-------------+---------+---------------------+
1 row in set (0.01 sec)

In this example, please note the subquery:

SELECT 1
FROM film
WHERE film.language_id = language.language_id

In the EXISTS subquery, it select rows from the film table, and the test condition is film.language_id = language.language_id.

When MySQL testing each row of the language table, as long as the film table exists a row that language_id column has the same value, EXISTS returns TRUE. Then continue to select the next row in the language table until all rows are tested.

NOT EXISTS example

If you want to find the languages that do not have related films in the film table, Please use the NOT EXISTS statement:

NOT EXISTS is also useful when the comparison column might contain NULL; a NOT IN subquery containing NULL can filter out every nonmatching row. See MySQL NOT IN for an example.

SELECT *
FROM language
WHERE NOT EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );

+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
5 rows in set (0.01 sec)

Columns of subquery

EXISTS does not care about the number of columns in the subquery, it only cares whether the subquery returns rows or not. Let’s look at the following 3 examples:

Using SELECT * in subquery

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
);

Using SELECT 1 in subquery

SELECT *
FROM language
WHERE EXISTS(
    SELECT 1
    FROM film
    WHERE film.language_id = language.language_id
);

Using SELECT column_name in subquery

SELECT *
FROM language
WHERE EXISTS(
    SELECT film_id
    FROM film
    WHERE film.language_id = language.language_id
);

They all return the same result. This shows that the the columns of SELECT in the subquery does not affect result of EXISTS.

EXISTS and IN

Use EXISTS when you need to know whether a correlated subquery finds a matching row. Use IN when you need to test whether a value belongs to the set returned by a list or subquery. For this example, both forms ask whether a language has a matching film:

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );

The corresponding IN operator version is:

SELECT *
FROM language
WHERE language_id IN (
    SELECT language_id
    FROM film
  );

Do not assume that EXISTS is always faster than IN, or vice versa. MySQL can optimize subqueries with strategies such as semijoin, materialization, or an EXISTS strategy. The best plan depends on the query and data; use EXPLAIN to inspect the plan for your workload. See MySQL’s subquery optimization guide.

EXISTS tests whether any row is returned, while IN compares values and can produce NULL when a comparison involves NULL. This difference is particularly important for NOT IN: if its subquery can return NULL, see MySQL NOT IN and NULL behavior.

Use the TABLE statement in a subquery

In MySQL 8.0.19 and later versions, we can use TABLE statement in subquery of EXISTS or NOT EXISTS. It’s like the following:

SELECT column1 FROM t1 WHERE EXISTS (TABLE t2);

This is equivalent to using SELECT * without any conditions in the subquery, such as the following statement:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Conclusion

In this article, we discussed MySQL EXISTS operator. The following are the main points of the EXISTS operator:

EXISTS is used to test whether a subquery returns rows of not. If a subquery returns at least one row, EXISTS returns TRUE, otherwise returns FALSE.
NOT EXISTS is the negative operation of EXISTS.
Use EXISTS for a matching-row test; compare execution plans instead of assuming one form is always faster.