行子查询（Row Subquery）

本节讲解行子查询（Row Subquery）与行构造器（Row Constructor）的配合用法、多列比较语义、IN 与 = 的行比较差异，以及在分页、复合主键查询中的实际应用。学完本节，你将能用一行表达式比较多个列。

前置知识

阅读本节前，建议先了解：标量子查询（Scalar Subquery）

基础概念

行子查询（Row Subquery） 指返回单行多列的子查询，它通常与行构造器（Row Constructor） 配合使用，实现"多列同时比较"。这是 SQL 标准中一个容易被忽略却极其有用的特性。

概念	术语	说明
行构造器	Row Constructor	用 ROW(a, b) 或 (a, b) 构造的复合值
行子查询	Row Subquery	返回单行多列的子查询
行比较	Row Comparison	两个行值按字典序逐列比较
字典序	Lexicographic Order	先比第一列，相等再比第二列，依次类推
复合主键	Composite Key	由多列共同构成的主键
元组比较	Tuple Comparison	行比较的另一种叫法
扩展行比较	Extended Row Comparison	行与行之间用 >, <, >=, <= 比较

行比较的核心规则是字典序：

(a, b) < (c, d)  ⇒  a < c  OR  (a = c AND b < d)
(a, b) = (c, d)  ⇒  a = c  AND  b = d

用表格直观理解：

左行	运算符	右行	结果	原因
(1, 5)	<	(2, 1)	true	首列 1 < 2，不再看第二列
(1, 5)	<	(1, 9)	true	首列相等，第二列 5 < 9
(1, 5)	=	(1, 5)	true	两列都相等
(1, NULL)	=	(1, NULL)	NULL	含 NULL 的比较结果为 NULL
(1, 5)	<	(1, NULL)	NULL	第二列比较为 NULL

含 NULL 的行比较遵循三值逻辑，这是最容易踩坑的地方。

语法与用法

行子查询的三种基本形态：

-- 形态 1：行 = 行子查询（子查询返回单行多列）
SELECT ... FROM 表名 WHERE (列1, 列2) = (SELECT 列1, 列2 FROM 表名 WHERE ...);

-- 形态 2：行 IN 子查询（子查询返回多行多列）
SELECT ... FROM 表名 WHERE (列1, 列2) IN (SELECT 列1, 列2 FROM 表名 WHERE ...);

-- 形态 3：行 与 行构造器比较（不涉及子查询）
SELECT ... FROM 表名 WHERE (列1, 列2) > (值1, 值2);

-- 显式 ROW 关键字（两者等价，但 ROW 形式在某些表达式中必须显式写出）
SELECT ... FROM 表名 WHERE ROW(列1, 列2) < ROW(值1, 值2);

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

-- 订单表：复合业务键 (user_id, created_at, id)
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
);

-- 订单明细：复合主键 (order_id, product_id)
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

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

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,    490.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', 1,    607.00, 'paid',    DATE '2026-01-05'),   -- 与 SO-1001 同 (user_id, created_at, amount)
  ('SO-1007', 3,    350.00, 'pending', DATE '2026-03-11');

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

INSERT INTO order_items (order_id, product_id, qty, price) VALUES
  (1, 1, 1, 349.00),
  (1, 2, 2, 129.00),
  (2, 3, 1, 199.00),
  (3, 1, 1, 349.00),
  (3, 3, 1, 199.00),
  (6, 1, 1, 349.00);

ANALYZE orders, order_items, products;

详细说明

行等值比较与 IN

行 IN 是行子查询最常用的形式，用于"复合键属于某个集合"：

-- 找出与订单 1 商品组合相同的订单（同一 product_id + qty 组合）
SELECT DISTINCT o.order_no
FROM orders      AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE (i.order_id, i.product_id, i.qty) IN (
  SELECT order_id, product_id, qty FROM order_items WHERE order_id = 1
)
ORDER BY o.order_no;

 order_no
----------
 SO-1001
 SO-1003

-- 行 IN 与多个单列 IN 的区别：行 IN 是"组合匹配"，单列 IN 是"各自匹配"
-- 行 IN：product_id 与 qty 必须来自同一行
SELECT order_id, product_id, qty
FROM order_items
WHERE (product_id, qty) IN ((1, 1), (3, 1))
ORDER BY order_id, product_id;

 order_id | product_id | qty
----------+------------+-----
        1 |          1 |   1
        2 |          3 |   1
        3 |          1 |   1
        3 |          3 |   1
        6 |          1 |   1
(5 rows)

-- 单列 IN 的等价写法（需要 AND 组合，容易写错）
SELECT order_id, product_id, qty
FROM order_items
WHERE product_id IN (1, 3) AND qty = 1
ORDER BY order_id, product_id;
-- 本例结果相同，但当匹配组合是 (1,1) 和 (3,2) 时两种写法结果不同

用表格说明这种差异：

需求	行 IN 写法	单列 IN 写法	是否等价
匹配 (1,1) 或 (3,1)	(a, b) IN ((1,1),(3,1))	a IN (1,3) AND b = 1	✅ 巧合等价
匹配 (1,1) 或 (3,2)	(a, b) IN ((1,1),(3,2))	❌ 无法用简单 AND 表达	❌ 不等价
匹配任意组合	(a, b) IN (...)	a IN (...) AND b IN (...)	❌ 语义不同

行比较与键集分页

行比较最实用的场景是键集分页（Keyset Pagination）。传统的 OFFSET 分页在大偏移量时性能急剧下降，而行比较可以走索引直接定位：

-- 传统 OFFSET 分页：偏移越大越慢，因为要扫描并丢弃前 N 行
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, created_at, id
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 10;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit  (cost=... rows=10 width=...) (actual time=12.442..12.448 rows=10 loops=1)
   ->  Index Scan Backward using idx_orders_created_id on orders  (cost=... )
         (actual time=0.021..11.882 rows=100010 loops=1)     -- 实际读了 100010 行
 Execution Time: 12.510 ms

-- 键集分页：用行比较直接跳到目标位置
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, created_at, id
FROM orders
WHERE (created_at, id) < (DATE '2026-02-09', 100000)     -- 上一页最后一行
ORDER BY created_at DESC, id DESC
LIMIT 10;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit  (cost=... rows=10 width=...) (actual time=0.030..0.038 rows=10 loops=1)
   ->  Index Scan Backward using idx_orders_created_id on orders  (cost=... )
         (actual time=0.019..0.033 rows=10 loops=1)         -- 只读了 10 行
         Index Cond: (ROW(created_at, id) < ROW('2026-02-09'::date, 100000))
 Execution Time: 0.061 ms

注意计划中的 Index Cond: (ROW(created_at, id) < ROW(...)) —— 行比较被完整下推到索引条件，这正是键集分页高效的原因。两种分页的对比：

对比项	OFFSET 分页	键集分页（行比较）
定位方式	扫描并丢弃前 N 行	索引直接定位
复杂度	O(OFFSET + LIMIT)	O(LIMIT)
页码跳转	✅ 支持任意页	❌ 只支持顺序翻页
数据变动	可能重复/漏行	稳定
索引需求	排序列索引	复合排序列索引

复合主键下的行操作

行构造器在复合主键表上可以直接表达"整行定位"：

-- 复合主键的精确查找：与拆成两个条件等价
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM order_items WHERE (order_id, product_id) = (1, 2);

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using order_items_pkey on order_items  (cost=0.15..8.17 rows=1 width=24)
                                                   (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: ((order_id = 1) AND (product_id = 2))     -- 自动拆解为两列条件

-- 与显式两列条件完全等价，但行写法更简洁
SELECT * FROM order_items WHERE order_id = 1 AND product_id = 2;

 order_id | product_id | qty | price
----------+------------+-----+--------
        1 |          2 |   2 | 129.00
(1 row)

-- 批量 UPSERT 中也可用行比较（PostgreSQL 14+ 起支持在 WHERE 中用行构造器）
INSERT INTO order_items (order_id, product_id, qty, price)
VALUES (1, 2, 3, 99.00)
ON CONFLICT (order_id, product_id)
DO UPDATE SET qty = EXCLUDED.qty, price = EXCLUDED.price
WHERE (order_items.qty, order_items.price) IS DISTINCT FROM (EXCLUDED.qty, EXCLUDED.price);
-- PostgreSQL 14+ 的 IS DISTINCT FROM 支持行构造器

实战示例

下面用完整流程做一份"订单去重 + 分页"的实际报表。

-- 步骤 1：找出"同一用户同一天下了完全相同的订单"的重复记录
SELECT
  o1.order_no AS dup_a,
  o2.order_no AS dup_b,
  o1.user_id,
  o1.created_at,
  o1.amount
FROM orders AS o1
JOIN orders AS o2
  ON (o2.user_id, o2.created_at, o2.amount) = (o1.user_id, o1.created_at, o1.amount)
 AND o2.id > o1.id                      -- 只保留 id 大的一侧，避免成对重复
ORDER BY o1.order_no, o2.order_no;

 dup_a  | dup_b  | user_id | created_at | amount
--------+--------+---------+------------+--------
 SO-1001| SO-1006|       1 | 2026-01-05 | 607.00
(1 row)

-- 步骤 2：用行 IN 找出"完全匹配已知组合"的商品行
SELECT i.order_id, p.name, i.qty, i.price
FROM order_items AS i
JOIN products    AS p ON p.id = i.product_id
WHERE (i.product_id, i.qty) IN ((1, 1), (3, 1))
ORDER BY i.order_id, i.product_id;

 order_id |    name     | qty | price
----------+-------------+-----+--------
        1 | 机械键盘     |   1 | 349.00
        2 | USB-C 扩展坞 |   1 | 199.00
        3 | 机械键盘     |   1 | 349.00
        3 | USB-C 扩展坞 |   1 | 199.00

        6 | 机械键盘     |   1 | 349.00
(5 rows)

-- 步骤 3：第一页（键集分页）
SELECT order_no, created_at, id
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 3;

 order_no | created_at |  id
----------+------------+------
 SO-1007  | 2026-03-11 |    7
 SO-1005  | 2026-02-15 |    5
 SO-1003  | 2026-02-09 |    3
(3 rows)

-- 步骤 4：第二页 —— 用上一页最后一行的 (created_at, id) 作为游标
SELECT order_no, created_at, id
FROM orders
WHERE (created_at, id) < (DATE '2026-02-09', 3)
ORDER BY created_at DESC, id DESC
LIMIT 3;

 order_no | created_at |  id
----------+------------+------
 SO-1004  | 2026-02-02 |    4
 SO-1002  | 2026-01-08 |    2
 SO-1006  | 2026-01-05 |    6
(3 rows)

-- 步骤 5：第三页 —— 游标为 (2026-01-05, 6)
SELECT order_no, created_at, id
FROM orders
WHERE (created_at, id) < (DATE '2026-01-05', 6)
ORDER BY created_at DESC, id DESC
LIMIT 3;

 order_no | created_at |  id
----------+------------+------
 SO-1001  | 2026-01-05 |    1
(1 row)

三页拼接恰好覆盖全部 7 行，无重复无遗漏。

-- 步骤 6：验证行比较的 NULL 陷阱
SELECT (1, NULL) = (1, NULL) AS eq_null,
       (1, 5)   = (1, 5)   AS eq_ok,
       (1, 5)   < (1, 9)   AS lt_ok,
       (1, 5)   < (1, NULL) AS lt_null;

 eq_null | eq_ok | lt_ok | lt_null
---------+-------+-------+---------
         | t     | t     |
(1 row)

含 NULL 的两个比较结果都是 NULL（空显示），在 WHERE 中会被当作"不满足"。

-- 步骤 7：用 IS NOT DISTINCT FROM 处理 NULL 的行比较
SELECT (1, NULL) IS NOT DISTINCT FROM (1, NULL) AS safe_eq;

 safe_eq
---------
 t

注意事项

• 行比较中的 NULL 会让整个比较变成 NULL。 需要 NULL 安全时用 IS DISTINCT FROM / IS NOT DISTINCT FROM。

-- 错误！任一列为 NULL 时结果都不符合预期
SELECT * FROM t WHERE (a, b) = (1, 2);

-- 正确：NULL 安全比较
SELECT * FROM t WHERE (a, b) IS NOT DISTINCT FROM (1, 2);

• 单个元素的"行"与标量行为不同。 (1) 会被解析为普通括号表达式，而不是行。

-- 这里的 (1) 就是整数 1，不是行
SELECT (1) = 1;         -- t

-- 真正的单元素行需要显式 ROW
SELECT ROW(1) = ROW(1); -- t

• IN 与 = ANY 在行子查询下语义有微妙差别。 IN 对 NULL 的处理更宽松，= ANY 在含 NULL 时更严格。推荐统一使用 IN。

-- 推荐写法
SELECT * FROM t WHERE (a, b) IN (SELECT a, b FROM s);

-- 等价但更严格
SELECT * FROM t WHERE (a, b) = ANY (SELECT a, b FROM s);

• 行子查询的列数必须与左侧行完全一致。 列数不匹配会直接报错。

-- 错误！unequal number of columns
SELECT * FROM orders WHERE (user_id, amount) IN (SELECT user_id FROM orders);

ERROR:  number of columns does not match number of columns in row expression

• 不要用行比较替代 JOIN。 行 IN 在大集合上可能被计划为低效的扫描，连接通常更优。
• OFFSET 分页不要用于深分页。 翻到第 1 万页时它会扫描 10 万行，必须改用键集分页。
• 行比较要求操作数类型可比较。 混合类型会触发隐式转换，影响索引使用。

最佳实践

1. 复合主键/唯一键的定位统一用行构造器写法，可读性更好。

SELECT * FROM order_items WHERE (order_id, product_id) = (1, 2);

2. 深分页一律用键集分页（行比较 + 复合索引）。

CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

-- 游标携带上一页最后一行的完整排序键
SELECT order_no, created_at, id
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size;

3. 排序键必须唯一，否则键集分页会漏行。 在排序键末尾补上主键保证唯一性。

-- 不推荐：created_at 可能重复，翻页会漏行
ORDER BY created_at DESC
-- 推荐：补上唯一列
ORDER BY created_at DESC, id DESC

4. 行 IN 的右侧尽量用常量列表。 常量列表会被解析为高效的 OR 展开，而子查询需要额外扫描。

WHERE (product_id, qty) IN ((1, 1), (3, 1))    -- 常量列表，计划简单

5. NULL 安全比较用 IS NOT DISTINCT FROM。

-- 查找与目标行"完全相同"的行（含 NULL 列）
SELECT * FROM t WHERE (a, b) IS NOT DISTINCT FROM (SELECT a, b FROM t WHERE id = 1);

6. 用 EXPLAIN 确认行比较被下推为 Index Cond。 若只显示为 Filter，说明索引设计不支持该行比较。

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE (created_at, id) < (DATE '2026-02-09', 3) ORDER BY created_at DESC, id DESC LIMIT 10;

7. 列数必须严格对应。 在 ORM 或动态 SQL 中拼接行比较时，注意两侧列顺序也要一致 —— 顺序不同语义完全不同。

-- (created_at, id) 与 (id, created_at) 语义不同，索引也必须按相同顺序建立

下一节

继续学习：表子查询（派生表）

参考链接

• Row Constructors
• Subquery Expressions
• Comparison Operators
• SELECT