MySQL EXISTS 的用法与实例

在本文中,我们讨论在 MySQL 中的 EXISTS 操作符,以及使用 EXISTS 操作符的时机。

在 MySQL 中,EXISTS 操作符用来判断一个子查询是否返回数据行。如果一个子查询返回了至少一个数据行,则 EXISTS 的计算结果为 TRUE,否则计算结果为 FALSE

EXISTS 语法

EXISTS 操作符是一个单目操作符,它需要一个子查询作为参数。EXISTS 操作符的语法如下:

SELECT column_name
FROM table_name
WHERE EXISTS(subquery);

EXISTS 使用说明:

  • EXISTS 一般用在 WHERE 子句中。
  • EXISTS 是一个单目操作符,它需要一个子查询 subquery 作为参数。
  • 如果子查询 subquery 返回了至少一个数据行,则 EXISTS 的计算结果为 TRUE,否则计算结果为 FALSE
  • EXISTS 运算时,一旦子查询找到一个匹配的行,EXISTS 运算就会返回。这对提高查询新能很有帮助。
  • EXISTS 不关心子查询中的列的数量或者名称,它只在乎子查询是否返回数据行。所以在 EXISTS 的子查询中,无论你是使用 SELECT 1 还是 SELECT *,亦或是 SELECT column_list,都不影响 EXISTS 运算的结果。
  • NOT EXISTS 则是 EXISTS 的否定操作。

EXISTS 实例

下面我们通过一些实例来了解 EXISTS 的使用方法。

在以下实例中,我们使用 Sakila 示例数据库中的 filmlanguage进行演示。

EXISTS 实例

下面的实例查询 language 表的一些语种,该语种在 film 表中存在相关语种的影片。

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    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)

在上面的例子中,我们在 WHERE 子句中使用了 EXISTS 操作符。在 EXISTS 的子查询中,我们从 film 表中检索记录,检索的条件是 film.language_id = language.language_id

在检索 language 表的每一行时,只要 film 表存在一行数据具有相同的 language_idEXISTS 就返回 TRUE。然后进入 language 表的下一行,直到所有行都检索完成,然后返回 language 表中所有匹配成功的行。

最终的结果告诉我们,在 language 表中,只有语种 English 拥有影片。

NOT EXISTS 实例

上一个例子检索了 language 表中的拥有影片的行,下面的实例则检索了 language 表中的没有影片的行。这只是对上例中 EXISTS 的否定操作,只需要使用 NOT EXISTS 即可。如下:

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)

EXISTS 子查询的列

EXISTS 不关心子查询中的列的数量或者名称,它只在乎子查询是否返回数据行。我们看下面 3 个例子:

  1. 在子查询中使用 SELECT *

    SELECT *
    FROM language
    WHERE EXISTS(
        SELECT *
        FROM film
        WHERE film.language_id = language.language_id
    );
    
  2. 在子查询中使用 SELECT 1

    SELECT *
    FROM language
    WHERE EXISTS(
        SELECT 1
        FROM film
        WHERE film.language_id = language.language_id
    );
    
  3. 在子查询中使用 SELECT column_name

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

他们返回的结果都一样。这说明了 EXISTS 的子查询中的 SELECT 的列不影响 EXISTS 的结果。

EXISTS 与 IN

有时候 EXISTS 可以使用 IN 来实现。

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

对应的 IN 操作符的版本是:

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

大多数情况下,使用 EXISTS 的语句的性能比对应的使用 IN 的语句要好。

在子查询中使用 TABLE 语句

在 MySQL 8.0.19 和以后的版本中,我们可以直接在 EXISTSNOT EXISTS 的子查询中使用 TABLE 语句。就像下面的一样:

SELECT column1 FROM t1 WHERE EXISTS (TABLE t2);

这相当于在子查询使用不带任何条件的 SELECT *。等同于如下的语句:

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

结论

在本文中,我们讨论在 MySQL 中的 EXISTS 操作符,以及使用 EXISTS 操作符的时机。以下是 EXISTS 操作符的要点:

  • EXISTS 操作符用来判断一个子查询是否返回数据行。如果一个子查询返回了至少一个数据行,则 EXISTS 的计算结果为 TRUE,否则计算结果为 FALSE
  • NOT EXISTS 则是 EXISTS 的否定操作。
  • EXISTS 运算的结果只与子查询是否返回数据行有关,子查询中的列的数量或者名称不影响运算结果。
  • 大多数情况下,使用 EXISTS 的语句的性能比对应的使用 IN 的语句要好。