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.
During EXISTS operation, once the subquery finds a matching row, the EXISTS operation will be returned. This is very helpful to improve query performance.
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:

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

Sometimes, EXISTS can be replaced by IN.

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 DISTINCT language_id
    FROM film
  );

In most cases, the performance of statements with EXISTS is better than the corresponding statements with IN.

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.
In most cases, the performance of statements with EXISTS is better than the corresponding statements with IN.