课程 6.2 · 阅读时间：~8 分钟

WHERE 中的子查询让您可以根据另一个查询的中间结果过滤行。在本课中，您将学习何时使用比较操作符 IN、NOT IN、EXISTS、NOT EXISTS、ANY 和 ALL，以及如何为实际任务选择最安全的选项。

WHERE 子句中的子查询

在上一课中，我们介绍了子查询的一般概念。现在我们专注于最常见的场景：在 WHERE 中过滤，当外部查询依赖于动态计算的值时。

在实际工作中，这种情况经常使用：从查找没有付款的客户到将一行与结果集进行比较。

标量子查询和比较操作符

如果子查询返回恰好一个值，则称为标量子查询。在这种情况下，您可以使用标准操作符 =、<>、>、>=、<、<=。

场景： 查找与 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 引擎无法进行明确的比较并会引发错误。

在下一课中，我们将研究相关子查询以及它们如何在外部查询中逐行执行。