36 标量子查询与 IN 子查询

子查询是在另一个查询中编写的 SELECT。常见的有三种形式。标量子查询至多返回一行且恰好一列，可以在任何允许单个值的地方使用。IN 子查询用于测试成员资格：WHERE col IN (subquery)。EXISTS 子查询测试子查询是否返回任意行（下一课会介绍）。子查询可以是关联的（引用外层行），也可以是非关联的（不依赖外层查询）。PostgreSQL 很擅长优化它们——你可以放心使用，不必过度担心。

本章要点

• 标量子查询：一行一列
• IN (subquery) 如何测试成员资格
• NOT IN 与 NULL 陷阱
• ANY / ALL 作为更通用的比较符
• 什么时候使用子查询而不是连接

示例

-- Scalar subquery: the fruit with the largest id
SELECT id, name
FROM fruits
WHERE id = (SELECT max(id) FROM fruits);

-- Scalar subquery in the SELECT list
SELECT name,
       id,
       (SELECT count(*) FROM fruits) AS total_fruits
FROM fruits
ORDER BY id
LIMIT 3;

-- IN: membership in a set
SELECT *
FROM fruits
WHERE id IN (SELECT id FROM fruits WHERE id <= 2)
ORDER BY id;

-- NOT IN with no NULLs is fine
SELECT id, name
FROM fruits
WHERE id NOT IN (SELECT id FROM fruits WHERE id > 100)
ORDER BY id;

-- ANY/ALL: generalize comparators with a subquery
SELECT id, name
FROM fruits
WHERE id > ALL (SELECT id FROM fruits WHERE id < 4)
ORDER BY id;

-- A correlated subquery references the outer row (here, c.id)
SELECT c.id, c.name,
       (SELECT count(*) FROM orders_example o WHERE o.customer_id = c.id) AS order_count
FROM customers_example c
ORDER BY c.id;

标量子查询必须至多返回一行。零行会被视为 NULL；多行会引发错误。当你想获得依赖于表本身的值（最大值、计数、最新值）时，它们最有用。

IN (subquery) 等价于 = ANY (subquery)——当外层值等于子查询结果中的至少一个值时为真。规划器通常会把它转换为半连接，效率可能很高。

ANY 和 ALL 将比较推广到一个集合。x > ALL (s) 在 x 大于 s 中的每个元素时为真（包括空集合；对空集合而言，这是空真，因此结果为真）。x > ANY (s) 在 x 大于至少一个元素时为真。

关联子查询引用外层查询中的列（例如上面的 o.customer_id = c.id）。从概念上说，它们对每个外层行运行一次，不过规划器通常会把它们优化为连接。

运行示例

运行方式（需要 fruits 表和连接课程中的表）：

$ psql -f source/scalar-and-in-subqueries.sql postgres
 id  |   name
-----+----------
 103 | fruit_3
(1 row)
 ...

常见陷阱

• 返回多行的标量子查询会引发 “more than one row returned by a subquery used as an expression.” 错误。如果你确实想要“第一行”，请添加 LIMIT 1 和明确的 ORDER BY。
• NOT IN (subquery) 是著名的陷阱：如果存在 NULL，不匹配的比较会产生未知，匹配的比较会产生 false；筛选结果不会保留任何行。为安全起见，请使用 NOT EXISTS。
• SELECT 列表中的关联子查询会对每个外层行运行一次。如果规划器无法优化，代价就是 O(outer * inner)。当子查询代价高时，可以改写为 LEFT JOIN + count() ... GROUP BY。
• 在很长的硬编码列表上使用 IN（如 IN ('a', 'b', ..., thousands)）没问题，但不够优雅。对于动态的大型列表，优先与临时表连接，或使用 unnest(array)。

下载

SQL 文件：scalar-and-in-subqueries.sql · 全部示例文件 · 准备工作