标量子查询（Scalar Subquery）

本节讲解标量子查询（Scalar Subquery）的定义与三大使用位置、单值约束、NULL 传播规则，以及它与 JOIN 的等价改写。学完本节，你将能安全地在 SELECT、WHERE、HAVING 中嵌入返回单值的子查询。

前置知识

阅读本节前，建议先了解：连接查询优化

基础概念

标量子查询（Scalar Subquery） 指返回恰好一列一行的子查询，其值可以像普通常量一样出现在表达式中。它与返回集合的子查询（用于 IN、EXISTS、FROM）形成对照。

概念	术语	说明
标量子查询	Scalar Subquery	返回单行单列的子查询
单值约束	Single-Value Constraint	子查询结果必须最多一行，否则运行时报错
相关子查询	Correlated Subquery	子查询内部引用了外层的列
独立子查询	Uncorrelated Subquery	子查询与外层无关，可被独立求值一次
派生表	Derived Table	出现在 FROM 中的子查询
表达式子查询	Expression Subquery	标量子查询在表达式中的位置
结果缓存	Result Caching	独立子查询只执行一次，结果复用

标量子查询可以出现在四类位置，各自的语义不同：

位置	语法形态	语义	是否允许多行
SELECT 列表	SELECT (SELECT ...)	每行返回一个值	❌ 多行报错
WHERE 比较	WHERE x = (SELECT ...)	与标量比较	❌ 多行报错
HAVING	HAVING count(*) > (SELECT ...)	与聚合值比较	❌ 多行报错
FROM	FROM (SELECT ...) AS t	派生表，本质是表	✅ 允许
ORDER BY / GROUP BY	ORDER BY (SELECT ...)	排序/分组表达式	❌ 多行报错

注意 FROM 中的子查询不是标量子查询，它是派生表（Derived Table），可以返回任意行列。二者的区别是初学者最容易混淆的地方。

语法与用法

标量子查询的标准写法是用括号包裹一条完整的 SELECT：

-- 独立标量子查询：与外层无关
SELECT 列列表, (SELECT 聚合或单值 FROM ... WHERE ...) AS 别名
FROM 表名;

-- 相关标量子查询：内部引用外层列
SELECT 列列表, (SELECT ... FROM 内表 WHERE 内表.列 = 外表.列) AS 别名
FROM 外表;

-- WHERE 中的标量比较
SELECT ... FROM 表名 WHERE 列 运算符 (SELECT 单值 FROM ...);

准备本节贯穿使用的示例表：

-- 用户表
CREATE TABLE users (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT    NOT NULL,
  city     TEXT    NOT NULL,
  vip      BOOLEAN NOT NULL DEFAULT false
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 商品表
CREATE TABLE products (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      TEXT    NOT NULL UNIQUE,
  name     TEXT    NOT NULL,
  category TEXT    NOT NULL,
  price    NUMERIC(10, 2) NOT NULL
);

INSERT INTO users (name, city, vip) VALUES
  ('alice', 'beijing',  true),
  ('bob',   'shanghai', false),
  ('carol', 'shenzhen', true),
  ('dave',  'chengdu',  false),
  ('erin',  'beijing',  false);        -- 无订单用户

INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES
  ('SO-1001', 1,    607.00, 'paid',    DATE '2026-01-05'),
  ('SO-1002', 2,    199.00, 'paid',    DATE '2026-01-08'),
  ('SO-1003', 1,    518.00, 'paid',    DATE '2026-02-09'),
  ('SO-1004', 3,    195.00, 'pending', DATE '2026-02-02'),
  ('SO-1005', 2,   1200.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', NULL,  89.00, 'paid',    DATE '2026-03-01'),
  ('SO-1007', 3,    350.00, 'pending', DATE '2026-03-11');

INSERT INTO products (sku, name, category, price) VALUES
  ('SKU-1001', '机械键盘',     'peripherals', 349.00),
  ('SKU-1002', '无线鼠标',     'peripherals', 129.00),
  ('SKU-1003', 'USB-C 扩展坞', 'accessories', 199.00);

ANALYZE users, orders, products;

详细说明

独立标量子查询与结果缓存

独立子查询（不与外层关联）在 PostgreSQL 中会被提升为初始化计划（InitPlan），整个查询只执行一次，结果被缓存复用：

-- 计算每个订单金额与全站平均金额的差值
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  o.order_no,
  o.amount,
  o.amount - (SELECT avg(amount) FROM orders) AS diff_from_avg
FROM orders AS o
ORDER BY o.order_no;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost=25.17..25.19 rows=7 width=52) (actual time=0.088..0.090 rows=7 loops=1)
   Sort Key: o.order_no
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=17.15..17.16 rows=1 width=32) (actual time=0.048..0.048 rows=1 loops=1)
           ->  Seq Scan on orders  (cost=0.00..13.70 rows=370 width=32) (actual time=0.010..0.024 rows=7 loops=1)
   ->  Seq Scan on orders o  (cost=0.00..13.70 rows=370 width=20) (actual time=0.010..0.021 rows=7 loops=1)
 Planning Time: 0.318 ms
 Execution Time: 0.126 ms

 order_no | amount  | diff_from_avg
----------+---------+---------------
 SO-1001  |  607.00 |        128.43
 SO-1002  |  199.00 |       -279.57
 SO-1003  |  518.00 |         39.43
 SO-1004  |  195.00 |       -283.57
 SO-1005  | 1200.00 |        721.43
 SO-1006  |   89.00 |       -389.57
 SO-1007  |  350.00 |       -128.57
(7 rows)

计划中的 InitPlan 1 (returns $0) 只出现一次且 loops=1，说明聚合只算了一遍，这正是独立子查询的高效之处。

相关标量子查询

相关子查询（Correlated Subquery）内部引用了外层列，外层每行都要执行一次内层查询：

-- 每个用户的订单总额（无订单用户显示 NULL）
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.name,
  (SELECT sum(o.amount) FROM orders AS o WHERE o.user_id = u.id) AS total_amount
FROM users AS u
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost=24.08..24.13 rows=370 width=68) (actual time=0.104..0.106 rows=5 loops=1)
   Sort Key: u.name
   ->  Seq Scan on users u  (cost=0.00..12.70 rows=370 width=36) (actual time=0.011..0.024 rows=5 loops=1)
         SubPlan 2
           ->  Aggregate  (cost=17.17..17.18 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=5)
                 ->  Seq Scan on orders o  (cost=0.00..17.15 rows=3 width=32) (actual time=0.005..0.007 rows=2 loops=5)
                       Filter: (user_id = u.id)
                       Rows Removed by Filter: 5

 name  | total_amount
-------+--------------
 alice |      1125.00
 bob   |       199.00
 carol |       545.00
 dave  |             ← NULL：无订单
 erin  |             ← NULL：无订单
(5 rows)

SubPlan 2 的 loops=5 说明它被执行了 5 次（一次一行外层数据），每次内层都做一遍全表扫描。当外层行数很大时这是性能陷阱。

关键区别：InitPlan = 只算一次；SubPlan 的 loops > 1 = 每行算一次。

-- 相关子查询改写为聚合 + 外连接（推荐）
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, sum(o.amount) AS total_amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 GroupAggregate  (cost=27.30..27.37 rows=5 width=68) (actual time=0.096..0.104 rows=5 loops=1)
   Group Key: u.id, u.name
   ->  Sort  (cost=27.30..27.31 rows=5 width=40) (actual time=0.082..0.084 rows=7 loops=1)
         Sort Key: u.id, u.name
         ->  Hash Left Join  (cost=... ) (actual time=... rows=7 loops=1)
               Hash Cond: (o.user_id = u.id)
               ...
 Execution Time: 0.188 ms

结果完全一致，但代价与行数无关，不会随外层行数线性放大。

NULL 传播与单值约束

标量子查询有两个必须记住的边界行为。

行为一：子查询无结果时返回 NULL，而不是报错。

-- 用户 99 不存在，子查询返回 0 行 → 结果为 NULL
SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE order_no = 'SO-1006');

 name
------
(0 rows)

SO-1006 的 user_id 是 NULL，id = NULL 恒为 NULL（三值逻辑），所以没有行被返回。

行为二：子查询返回多行时运行时报错。

-- 错误！the subquery returns more than one row
SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE status = 'paid');

ERROR:  more than one row returned by a subquery used as an expression

正确写法应按需求换成 IN、ANY 或 EXISTS：

-- 正确：id 属于集合
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

-- 正确：比较任意一个值
SELECT * FROM users WHERE id = ANY (SELECT user_id FROM orders WHERE status = 'paid');

-- 正确：比较最大值
SELECT * FROM users WHERE id = (SELECT max(user_id) FROM orders WHERE status = 'paid');

三者的语义对照：

写法	多行时行为	适用语义
= (SELECT ...)	报错	确知只有一行
IN (SELECT ...)	逐个比较	属于集合
= ANY (SELECT ...)	逐个比较	等价于 IN
>= ALL (SELECT ...)	逐个比较	大于等于所有值
EXISTS (SELECT ...)	不比较值	只判断存在

实战示例

下面用完整流程做一份"用户订单画像"报表。

-- 步骤 1：为每个用户附上全站平均订单金额
SELECT
  u.name,
  u.city,
  (SELECT round(avg(amount), 2) FROM orders WHERE status = 'paid') AS avg_paid
FROM users AS u
ORDER BY u.name;

 name  |   city   | avg_paid
-------+----------+----------
 alice | beijing  |   631.00
 bob   | shanghai |   631.00
 carol | shenzhen |   631.00
 dave  | chengdu  |   631.00
 erin  | beijing  |   631.00
(5 rows)

-- 步骤 2：为每个用户附上其最近一笔订单金额（相关子查询 + LIMIT 1）
SELECT
  u.name,
  (SELECT o.amount
   FROM orders AS o
   WHERE o.user_id = u.id
   ORDER BY o.created_at DESC, o.id DESC
   LIMIT 1) AS last_amount
FROM users AS u
ORDER BY u.name;

 name  | last_amount
-------+-------------
 alice |      518.00
 bob   |     1200.00
 carol |      350.00
 dave  |
 erin  |
(5 rows)

-- 步骤 3：标量子查询用于 HAVING —— 筛选订单总额高于全站平均的用户
SELECT u.name, sum(o.amount) AS total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING sum(o.amount) > (SELECT avg(amount) FROM orders)
ORDER BY total DESC;

 name  |  total
-------+---------
 alice | 1125.00
 carol |  545.00

这里子查询没有引用 groups 中的列，属于独立子查询，只执行一次。

-- 步骤 4：验证改写等价性 —— 标量子查询与 JOIN 版本结果完全一致
WITH scalar_version AS (
  SELECT u.name,
         (SELECT sum(o.amount) FROM orders AS o WHERE o.user_id = u.id) AS total
  FROM users AS u
),
join_version AS (
  SELECT u.name, sum(o.amount) AS total
  FROM users AS u
  LEFT JOIN orders AS o ON o.user_id = u.id
  GROUP BY u.id, u.name
)
SELECT
  s.name,
  s.total                    AS scalar_total,
  j.total                    AS join_total,
  s.total IS NOT DISTINCT FROM j.total AS same
FROM scalar_version AS s
JOIN join_version   AS j ON j.name = s.name
ORDER BY s.name;

 name  | scalar_total | join_total | same
-------+--------------+------------+------
 alice |      1125.00 |    1125.00 | t
 bob   |       199.00 |     199.00 | t
 carol |       545.00 |     545.00 | t
 dave  |              |            | t
 erin  |              |            | t
(5 rows)

-- 步骤 5：对比两种写法的执行计划（外层行数放大后差异明显）
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, (SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id)
FROM users u;
-- 输出中 SubPlan 的 loops=5，随 users 行数增长

-- 步骤 6：用 InitPlan 复用独立子查询结果，避免重复计算
SELECT
  p.name,
  p.price,
  (SELECT round(avg(price), 2) FROM products) AS avg_price,
  p.price - (SELECT round(avg(price), 2) FROM products) AS diff
FROM products AS p
ORDER BY p.price DESC;

    name     | price  | avg_price |  diff
-------------+--------+-----------+--------
 机械键盘    | 349.00 |    225.67 | 123.33
 USB-C 扩展坞| 199.00 |    225.67 | -26.67
 无线鼠标    | 129.00 |    225.67 | -96.67
(3 rows)

PostgreSQL 会自动识别两个结构相同的独立子查询并只执行一次。

注意事项

• 不要在 SELECT 列表里用返回多行的子查询。 一旦数据变化导致返回多于一行，查询会直接报错。

-- 错误！假设了 user_id 唯一
SELECT name, (SELECT order_no FROM orders WHERE user_id = users.id) FROM users;

-- 正确：明确限定一行
SELECT name, (SELECT order_no FROM orders WHERE user_id = users.id ORDER BY id LIMIT 1) FROM users;

• 不要用相关子查询做"逐行累加"。 外层行数 N、内层全表扫描 M 时复杂度是 O(N × M)。

-- 错误！外层每行都做一次聚合
SELECT u.name, (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS cnt FROM users u;

-- 正确：先聚合再连接
SELECT u.name, count(o.id) AS cnt
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

• 不要忽略 NULL 比较的后果。 WHERE x = (SELECT ...) 在子查询返回 NULL 时结果为空集，容易误判为"没有数据"。

-- 子查询返回 NULL 时，以下查询返回 0 行
SELECT * FROM orders WHERE amount = (SELECT amount FROM orders WHERE order_no = 'SO-XXXX');

• 不要把派生表误认为标量子查询。 FROM (SELECT ...) 可以返回任意多行，加上 LIMIT 1 也不会变成"标量"，它仍然是表。
• 不要在多行子查询上直接用 =。 应改用 IN 或 ANY。
• 不要在相关子查询中依赖外层 ORDER BY。 子查询内部的排序与外层无关，需要取"最新一条"必须自己在子查询里排序加 LIMIT 1。
• 不要忘记：SELECT 列表中的标量子查询对每行输出一个值，GROUP BY 时它是分组后的表达式，行为需要按分组思考。

最佳实践

1. 优先把独立子查询写在 SELECT/WHERE 中。 PostgreSQL 会把它转成 InitPlan，只执行一次，代价极低。

-- 独立子查询用于阈值比较
SELECT order_no, amount FROM orders WHERE amount > (SELECT avg(amount) FROM orders);

2. 相关子查询能改写为聚合 + 连接时就改写。

-- 不推荐：相关子查询
SELECT u.name, (SELECT max(o.amount) FROM orders o WHERE o.user_id = u.id) AS max_amt
FROM users u;

-- 推荐：一次连接完成分组聚合
SELECT u.name, max(o.amount) AS max_amt
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

3. 取"每组最新一条"用 DISTINCT ON 或窗口函数，而非相关子查询。

-- DISTINCT ON 比相关子查询更高效
SELECT DISTINCT ON (user_id) user_id, order_no, amount, created_at
FROM orders
WHERE user_id IS NOT NULL
ORDER BY user_id, created_at DESC, id DESC;

4. SELECT 列表中的标量子查询尽量依赖索引列。 相关子查询的过滤列必须有索引，否则退化为逐行全表扫描。

CREATE INDEX idx_orders_user_id ON orders (user_id);

5. 用 EXPLAIN 区分 InitPlan 与 SubPlan。 出现 SubPlan 且 loops 远大于 1 时，评估是否可改写。

6. 比较聚合结果用 HAVING，不要用 WHERE。

-- 正确：HAVING 作用于分组后
SELECT user_id, sum(amount) FROM orders GROUP BY user_id HAVING sum(amount) > (SELECT avg(amount) FROM orders);

7. 改写验证时用 IS NOT DISTINCT FROM 对比 NULL 结果，避免 = 因 NULL 而漏判。

SELECT a.total IS NOT DISTINCT FROM b.total AS same FROM (...) a JOIN (...) b ON ...;

下一节

继续学习：行子查询

参考链接

• Scalar Subqueries
• Subquery Expressions
• SELECT
• Using EXPLAIN