30. 子查询

子查询（subquery），说白了就是「查询里套查询」。外层叫主查询（outer query），括号里的叫子查询（也叫内层查询、嵌套查询）。PostgreSQL 会先跑子查询，把结果交给外层去用。

子查询能出现在很多位置：SELECT 列表、FROM 后面（充当临时表）、WHERE 和 HAVING 的条件里。按返回结果的「形状」，可以分成几种类型，下面逐一讲。

继续用前面那两张表（结构见第 29 章）：

• users(id, username, email, age, created_at)
• orders(id, user_id, amount, status, created_at)

标量子查询（返回单个值）

子查询只返回一行一列，就可以当成一个普通值用在 WHERE、SELECT 里。比如「找出消费金额高于全站平均订单金额的订单」：

SELECT id, user_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount) FROM orders
);

这里子查询 SELECT AVG(amount) FROM orders 只算出一个平均值，外层拿它当比较的基准。这比先查平均、再手填数值要方便得多，而且数据变了也不用改 SQL。

标量子查询也能直接放在 SELECT 列表里，给每行附一个汇总值：

SELECT id, amount,
       (SELECT AVG(amount) FROM orders) AS 全站平均,
       amount - (SELECT AVG(amount) FROM orders) AS 高出均值
FROM orders;

IN 子查询（返回多行）

子查询返回一列多行时，用 IN 判断「是否在这个集合里」。比如「找出下过单的用户」：

SELECT username, email
FROM users
WHERE id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE user_id IS NOT NULL
);

子查询先捞出所有有订单的 user_id，外层再筛选出这些用户。加上 DISTINCT 是好习惯——即便不写，IN 也不在乎重复，但去重能让子查询更轻。

反过来，把 IN 换成 NOT IN，就能找出「从没下过单的用户」。

SELECT username
FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

ANY 与 ALL 子查询

ANY 表示「和子查询结果里的任意一个比，只要有一个成立就为真」；ALL 表示「和子查询结果里的全部比，每一个都成立才为真」。它们前面必须跟一个比较运算符（<、<=、>、>=、=、<>）。

-- 找出金额大于「任意一笔已支付订单」的订单
SELECT id, amount
FROM orders
WHERE amount > ANY (
    SELECT amount FROM orders WHERE status = 'paid'
);

-- 找出金额大于「所有已支付订单」的订单（即比最大值还大）
SELECT id, amount
FROM orders
WHERE amount > ALL (
    SELECT amount FROM orders WHERE status = 'paid'
);

SOME 是 ANY 的同义词，两者可以互换。> ANY 约等于「大于最小值」，> ALL 约等于「大于最大值」。同理 < ALL 约等于「小于最小值」。

-- 等于 ANY 等价于 IN
SELECT id, amount FROM orders
WHERE amount = ANY (SELECT amount FROM orders WHERE status = 'paid');

相关子查询（依赖外层）

普通子查询能独立运行；相关子查询（correlated subquery）则引用了外层表的列，外层每处理一行，它就要重算一次。

比如「找出每笔订单里，金额高于该用户平均订单金额的订单」：

SELECT o.id, o.user_id, o.amount
FROM orders o
WHERE o.amount > (
    SELECT AVG(amount)
    FROM orders
    WHERE user_id = o.user_id
);

子查询里的 WHERE user_id = o.user_id 用到了外层 orders 的别名 o，所以它和外层「绑」在了一起。语义上是「对当前这笔订单的用户，算他的平均，再比一比」。

EXISTS 与 NOT EXISTS

EXISTS 不关心子查询返回什么内容，只关心「有没有行返回」。有行就为真，没行就为假。写法上习惯在子查询里写 SELECT 1，因为内容对结果无关紧要。

-- 找出下过单的用户（用 EXISTS）
SELECT u.username
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

-- 找出从没下过单的用户
SELECT u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

EXISTS 和 NOT EXISTS 特别适合「存在性判断」，而且不受 NULL 困扰——子查询里 user_id 哪怕是 NULL 也比不出匹配，但 NULL 不会让条件整体失效，比 NOT IN 安全得多。它常和关联条件（o.user_id = u.id）搭配，本质上也是一种相关子查询。

FROM 里的子查询（派生表）

子查询还能写在 FROM 后面，当一张临时表用，这种叫「派生表（derived table）」，必须起别名：

SELECT t.username, t.总额
FROM (
    SELECT u.username, SUM(o.amount) AS 总额
    FROM users u
    JOIN orders o ON o.user_id = u.id
    GROUP BY u.username
) t
WHERE t.总额 > 100;

这种写法把「先汇总、再过滤」分成了两层，逻辑清晰。等学了第 31 章的 CTE，这类嵌套还能写得更直白。

常见错误

1. 标量子查询不小心返回多行，报「more than one row returned by a subquery」。
2. NOT IN 子查询含 NULL，结果全空。
3. 相关子查询把外层别名拼错，导致它变成了独立子查询，算出的不是「按行」的值。
4. 在 WHERE 里用子查询做聚合却忘了分组语义，结果和预期不符。

IN 与 EXISTS 怎么选

两者都能表达「在某个集合里」，但适用场景不同。给个对照：

场景	推荐
子查询结果小、要的是具体值集合	IN
只判断「有没有」、关联条件复杂	EXISTS
子查询含 NULL、又想要「不在集合」	NOT EXISTS（避开 NOT IN 的坑）

一般来说，当外层表大、子查询用索引关联时，EXISTS 往往更优，因为它一匹配就返回，不必物化整个集合。

子查询也能用在 UPDATE / DELETE

子查询不只在 SELECT 里。下面用 orders 表演示：把「下过单的用户」那几笔订单的金额，刷新成该用户的历史平均金额。这里只是为了展示 UPDATE 配合子查询的语法，真实业务里改写金额要慎重。

UPDATE orders o
SET amount = (
    SELECT AVG(amount)
    FROM orders
    WHERE user_id = o.user_id
)
WHERE user_id IN (
    SELECT user_id FROM orders WHERE status = 'paid'
);

或者删除「没有任何订单的用户」：

DELETE FROM users
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

写的时候，UPDATE/DELETE 里的子查询如果引用了外层自己这张表，PostgreSQL 有些版本会有别名限制，必要时给外层表起个别名区分。

小结

子查询是把复杂问题拆成「先算一步、再算一步」的好工具。标量值用在比较里，IN 用在集合里，ANY/ALL 做范围比较，EXISTS 做存在判断，FROM 里的子查询当临时表。记住 NOT IN 怕 NULL、相关子查询要注意性能，等下章学了 CTE，有些嵌套还能写得更清爽。