🙏 我们需要您的帮助!SQLtest.online 没有广告,完全依靠捐款运行。哪怕一点点也很有帮助。 立即捐助 →
SQL 代码已复制到剪贴板

WHERE 子句中的子查询

第 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 引擎就无法进行明确的比较,因此会报错。

尝试解决以下任务,以巩固您在本课中学到的内容。

MySQL

  1. 伦敦的地址与子查询
  2. 未归还租赁的客户
  3. 租金高于平均水平的电影

Firebird

  1. 子部门列表
  2. 高薪员工
  3. 薪资高于平均水平的员工

SQLite

  1. 小账单企鹅
  2. 体重较轻的企鹅

MariaDB

  1. GPA 偏低的校长奖学金获得者

PostgreSQL

  1. 计算离境乘客数量

SQL Server

  1. 订单金额高于平均值的公司