# 标量子查询与 IN 子查询

> 使用标量子查询和成员资格子查询，并正确处理空结果和 NULL。

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="36.1" caption="标量子查询与 IN 子查询"}
-- 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`）。从概念上说，它们对每个外层行运行一次，不过规划器通常会把它们优化为连接。

## 运行示例 {#run}

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

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

## 常见陷阱 {#pitfalls}

- 返回多行的标量子查询会引发 *"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)`。

> [!TIP] 提示
> 许多子查询都可以改写为连接或 CTE；选择主要取决于可读性。使用子查询表示*单个值*，使用连接表示*来自另一张表的多行*，在你想为中间结果命名时使用 CTE（后面的 CTE 课程会介绍）。

> [!NOTE] 动手练习
> 找出 fruits 中 id 等于平均 id 的行（适当进行类型转换）：`SELECT * FROM fruits WHERE id = (SELECT avg(id)::integer FROM fruits);`。然后把关联子查询改写成 `LEFT JOIN + GROUP BY`，比较结果。

## 下载 {#downloads}

SQL 文件：[scalar-and-in-subqueries.sql](/examples/source/scalar-and-in-subqueries.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)