WHERE 子句中的子查询

Q: 查找缺失的关联时，NOT IN 和 NOT EXISTS 哪个更好？

在大多数实际任务中，NOT EXISTS 更稳妥。如果 NOT IN 的子查询返回 NULL，结果可能出乎意料，并过滤掉过多的行。

Q: 为什么在 EXISTS 中通常写 SELECT 1 而不是 SELECT *？

因为 EXISTS 只检查行是否存在，并不使用所选列的值，所以 SELECT 1 是一种标准而清晰的写法。

Q: 什么时候用 ANY，什么时候用 ALL？

当条件只需对子查询中的至少一个值成立时，使用 ANY。当条件必须对集合中的每个值都成立时，使用 ALL。

第 6.2 课 · 阅读时间：约 8 分钟 · 练习题 ↓

WHERE 中的子查询可以根据另一条查询的中间结果来过滤行。在本课中，你将学习何时使用比较运算符、IN、NOT IN、EXISTS、NOT EXISTS、ANY 和 ALL，以及如何在实际任务中选择最稳妥的写法。

在上一课中，我们介绍了子查询的基本概念。现在聚焦最常见的场景：在 WHERE 中过滤，此时外层查询依赖于动态计算出的值。

在实际工作中，这种写法随处可见：从查找没有付款记录的客户，到把一行数据与一个结果集进行比较。

WHERE 子查询示意图：IN、EXISTS、ANY 和 ALL 运算符

标量子查询与比较运算符

如果子查询恰好返回一个值，它就称为标量子查询。这时可以使用标准运算符 =、<>、>、>=、<、<=。

场景： 查找与 actor_id = 10 的演员名字相同的演员。

SELECT
    first_name,
    last_name
FROM
    actor
WHERE
    first_name = (
        SELECT first_name
        FROM actor
        WHERE actor_id = 10
    )
    AND actor_id <> 10;

说明：如果内层查询返回多行，这条查询会报错。

多行子查询：IN 和 NOT IN

当子查询返回一组值（一列多行）时，使用 IN。

场景： 查找 Action 类别中的电影。

SELECT
    f.title
FROM
    film AS f
WHERE
    f.film_id IN (
        SELECT
            fc.film_id
        FROM
            film_category AS fc
        WHERE
            fc.category_id = (
                SELECT
                    c.category_id
                FROM
                    category AS c
                WHERE
                    c.name = 'Action'
            )
    );

结果：得到通过 film_category 表与 Action 类别关联的所有电影。

NOT IN 执行相反的过滤，但要记住一个重要的陷阱：如果子查询结果中包含 NULL，条件可能会意外地返回空结果。这种情况下，NOT EXISTS 通常更稳妥。

存在性检查：EXISTS 和 NOT EXISTS

EXISTS 检查子查询中是否至少存在一行。数据库在找到第一条匹配后就可以停止，因此这种方法在大表上通常很高效。

EXISTS

场景： 查找至少有一笔付款的客户。

SELECT
    c.first_name,
    c.last_name
FROM
    customer AS c
WHERE
    EXISTS (
        SELECT
            1
        FROM
            payment AS p
        WHERE
            p.customer_id = c.customer_id
    );

说明：使用 EXISTS 时通常写 SELECT 1，因为重要的只是行是否存在，而不是返回的列值。

NOT EXISTS

场景： 查找没有任何付款的客户。

SELECT
    c.first_name,
    c.last_name
FROM
    customer AS c
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            payment AS p
        WHERE
            p.customer_id = c.customer_id
    );

结果：只返回在 payment 中没有对应记录的客户。

与集合比较：ANY 和 ALL

ANY：只要对子查询中的至少一个值成立，条件就为真。
ALL：只有对子查询中的每一个值都成立，条件才为真。

场景： 把电影的时长与 Comedy 类别中电影的时长进行比较。

SELECT
    f.title,
    f.length
FROM
    film AS f
WHERE
    f.length > ANY (
        SELECT
            f2.length
        FROM
            film AS f2
        INNER JOIN film_category AS fc ON f2.film_id = fc.film_id
        INNER JOIN category AS c ON fc.category_id = c.category_id
        WHERE
            c.name = 'Comedy'
    );

结果：只要比 Comedy 中至少一部电影长，该电影就会被选中。

SELECT
    f.title,
    f.length
FROM
    film AS f
WHERE
    f.length > ALL (
        SELECT
            f2.length
        FROM
            film AS f2
        INNER JOIN film_category AS fc ON f2.film_id = fc.film_id
        INNER JOIN category AS c ON fc.category_id = c.category_id
        WHERE
            c.name = 'Comedy'
    );

结果：只有比 Comedy 中每一部电影都长，该电影才会被选中。

实际查询中需要注意的地方

只需要一个值时，使用标量子查询加比较运算符。
需要一组值时，根据任务选择 IN 或 EXISTS。
查找缺失的关联时，优先使用 NOT EXISTS，尤其是可能存在 NULL 值时。
始终检查子查询是否可能返回比预期更多的行。

本课要点：

在 WHERE 中使用子查询可以动态过滤，无需手动代入值。
IN 便于检查某个值是否属于一个列表。
EXISTS 和 NOT EXISTS 能有效检查相关行是否存在。
ANY 和 ALL 让你可以把一行与整组值进行比较。
选对运算符能让查询更精确、更易读、更可靠。

常见问题

查找缺失的关联时，NOT IN 和 NOT EXISTS 哪个更好？

在大多数实际任务中，NOT EXISTS 更稳妥。如果 NOT IN 的子查询返回 NULL，结果可能出乎意料，并过滤掉过多的行。

为什么在 EXISTS 中通常写 SELECT 1 而不是 SELECT *？

因为 EXISTS 只检查行是否存在，并不使用所选列的值，所以 SELECT 1 是一种标准而清晰的写法。

什么时候用 ANY，什么时候用 ALL？

当条件只需对子查询中的至少一个值成立时，使用 ANY。当条件必须对集合中的每个值都成立时，使用 ALL。

面试问题

SQL 中 IN 和 EXISTS 有什么区别？

IN 把一个值与子查询返回的列表进行比较，而 EXISTS 检查是否至少存在一条匹配的行。在大数据量的相关子查询中，EXISTS 往往更高效，因为它可以在第一条匹配处停止。

你会如何解释标量子查询和多行子查询的区别？

标量子查询返回一个值，与 = 或 > 等运算符一起使用。多行子查询返回一组值，通常与 IN、ANY 或 ALL 一起使用。

为什么使用 = 运算符和子查询的查询可能会失败？

= 运算符要求右侧是单个值。如果子查询返回多于一行，SQL 引擎就无法进行明确的比较，因此会报错。