PHP小码农

Appearance

LATERAL 基础 ​

窗口函数解决了「同一张表内跨行取值」的问题，但它有个前提：所有数据都在一个 FROM 项里。可现实中常遇到另一类需求——左表的每一行都需要去右表查一次，而且右表要按左行的值动态取子集。比如「每个用户最近的 3 笔订单」，用窗口函数能做但要绕一层，用 LATERAL 则能直接表达。

LATERAL 允许 FROM 里的子查询引用它左侧的表，相当于把「相关子查询」搬到了 FROM 中，并且能一次返回多行多列。学完本节你能做到：

  • 说清 LATERAL 与普通 FROM 子查询的本质区别；
  • 熟练使用 CROSS JOIN LATERAL、LEFT JOIN LATERAL ... ON TRUE 两种形态并知道何时用哪个；
  • 理解为什么 unnest、generate_series 这类集合返回函数在 FROM 里无需写 LATERAL 也能引用左表。

前置知识

阅读本节前，建议先了解：窗口函数使用场景

基础概念 ​

先对齐术语，LATERAL 涉及的几个词在中文语境里容易混：

概念术语说明
侧向关联LATERAL关键字，声明右侧子查询可以引用左侧 FROM 项
FROM 项FROM ItemFROM 子句里的一个表、子查询或函数调用
左表 / 右表Left / Right Table语句书写顺序上的前后两项，非严格意义上的外连接
逐行驱动Row-Driven左表每出一行，就调用一次右侧子查询
集合返回函数Set-Returning Function（SRF）一行输入、多行输出，如 unnest、generate_series
隐式侧向Implicit LATERALFROM t, func(t.col) 中函数的侧向引用，无需写 LATERAL
相关子查询Correlated Subquery子查询引用外层查询的列，LATERAL 是它的 FROM 版本
去关联Decorrelation优化器把相关子查询改写为连接等高效率的形态

核心区别只有一句话：

不带 LATERAL 时，FROM 里的子查询是独立的，彼此看不见；带 LATERAL 时，子查询能看见它左边所有的 FROM 项。

sql
-- 不带 LATERAL：子查询看不见 users，会报错
SELECT u.id, s.cnt
FROM users u, (SELECT count(*) AS cnt FROM orders WHERE user_id = u.id) s;
text
ERROR:  invalid reference to FROM-clause entry for table "u"
LINE 2: FROM users u, (SELECT count(*) AS cnt FROM orders WHERE user_id = u.id) s;
                                                ^
HINT:  There is an entry for table "u", but it cannot be referenced from this part of the query.

加上 LATERAL 就合法了：

sql
-- 加 LATERAL 后子查询可见 users
SELECT u.id, s.cnt
FROM users u
CROSS JOIN LATERAL (
  SELECT count(*) AS cnt FROM orders WHERE user_id = u.id
) s;

与普通子查询、聚合的对比 ​

写法能否引用左表返回行数返回列数典型用途
FROM (SELECT ...) 独立子查询否任意任意预聚合、数据准备
SELECT (SELECT ... WHERE t.x = ...)是只能 1 行只能 1 列取单个标量值
JOIN ... ON 普通连接否任意任意等值/不等值关联
LATERAL (SELECT ...)是任意任意逐行取子集、Top-N

语法与用法 ​

LATERAL 可以出现在 FROM 列表的任意位置，也可以写成显式的连接形式：

sql
-- 形态 1：逗号连接 + LATERAL（等价于 CROSS JOIN LATERAL）
SELECT u.id, o.order_no, o.amount
FROM users u,
     LATERAL (SELECT order_no, amount
              FROM orders
              WHERE user_id = u.id
              ORDER BY amount DESC LIMIT 3) AS o;

-- 形态 2：显式 CROSS JOIN LATERAL
SELECT u.id, o.order_no, o.amount
FROM users u
CROSS JOIN LATERAL (SELECT order_no, amount
                    FROM orders
                    WHERE user_id = u.id
                    ORDER BY amount DESC LIMIT 3) AS o;

-- 形态 3：LEFT JOIN LATERAL，保留没有匹配行的左表行
SELECT u.id, u.username, o.order_no, o.amount
FROM users u
LEFT JOIN LATERAL (SELECT order_no, amount
                   FROM orders
                   WHERE user_id = u.id
                   ORDER BY amount DESC LIMIT 3) AS o
ON TRUE;

最小可运行示例（形态 1 与形态 2 结果完全相同）：

sql
-- 准备演示表
CREATE TABLE demo_lat_users (
  id       integer PRIMARY KEY,
  username text    NOT NULL
);

CREATE TABLE demo_lat_orders (
  id      bigserial PRIMARY KEY,
  user_id integer       NOT NULL,
  order_no text         NOT NULL,
  amount  numeric(10,2) NOT NULL
);

INSERT INTO demo_lat_users (id, username) VALUES
  (1, 'alice'),
  (2, 'bob'),
  (3, 'carol');

INSERT INTO demo_lat_orders (user_id, order_no, amount) VALUES
  (1, 'A-001', 500.00),
  (1, 'A-002', 300.00),
  (1, 'A-003', 200.00),
  (1, 'A-004', 100.00),
  (2, 'B-001', 400.00),
  (2, 'B-002', 400.00);
-- carol 没有订单

-- 每个用户金额最高的 3 笔订单
SELECT u.id, u.username, o.order_no, o.amount
FROM demo_lat_users u
CROSS JOIN LATERAL (
  SELECT order_no, amount
  FROM demo_lat_orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC, o.id
  LIMIT 3
) AS o
ORDER BY u.id, o.amount DESC;
text
 id | username | order_no | amount
----+----------+----------+--------
  1 | alice    | A-001    | 500.00
  1 | alice    | A-002    | 300.00
  1 | alice    | A-003    | 200.00
  2 | bob      | B-001    | 400.00
  2 | bob      | B-002    | 400.00

注意 carol 没有出现——CROSS JOIN LATERAL 会丢弃右侧无匹配行的左表行。

详细说明 ​

LATERAL 的位置规则 ​

LATERAL 的引用范围有明确边界：

  1. 只能引用它左侧的 FROM 项。右边的表它还看不见：

    sql
    -- 错误！s 在 u 左边，看不到 u
SELECT *
FROM LATERAL (SELECT * FROM demo_lat_orders WHERE user_id = u.id) s,
     demo_lat_users u;
    text
    ERROR:  invalid reference to FROM-clause entry for table "u"

  2. 可以引用多个左侧表，只要它们都排在 LATERAL 之前。下面用本节的演示表，把 users 与 orders 一起带进右侧子查询：

    sql
    -- 同时引用 u 与 o 两个左侧 FROM 项
SELECT u.username, o.order_no, o.amount
FROM demo_lat_users u
JOIN demo_lat_orders o ON o.user_id = u.id
CROSS JOIN LATERAL (
  SELECT u.username AS 归属人, o.order_no AS 关联单号
) AS info;

    这只演示语法层面「可见多张左表」。实际业务中更常见的是配合聚合，例如「取该用户订单数是否超过阈值」。

  3. 可以嵌套：内层 LATERAL 能看到外层 LATERAL 的列。

  4. LATERAL 不能用于 WHERE 或 SELECT 列表，它只是 FROM 子句的修饰词。

CROSS JOIN LATERAL 与 LEFT JOIN LATERAL 的关键差异 ​

这是新手最容易踩的坑，两者对「左表行没有匹配」的处理完全不同：

写法左表无匹配行时等价于
CROSS JOIN LATERAL (...)丢弃该左表行INNER JOIN 语义
, LATERAL (...)丢弃该左表行同上，两者完全等价
LEFT JOIN LATERAL (...) ON TRUE保留该行，右侧列填 NULLLEFT JOIN 语义

上图示例里 carol 被丢弃，就是 CROSS JOIN LATERAL 的行为。如果业务要求「即使用户没有订单也要列出来」，必须改用 LEFT JOIN LATERAL：

sql
-- 保留无订单用户，用 LEFT JOIN LATERAL
SELECT u.id, u.username, o.order_no, o.amount
FROM demo_lat_users u
LEFT JOIN LATERAL (
  SELECT order_no, amount
  FROM demo_lat_orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC, o.id
  LIMIT 3
) AS o ON TRUE
ORDER BY u.id, o.amount DESC NULLS LAST;
text
 id | username | order_no | amount
----+----------+----------+--------
  1 | alice    | A-001    | 500.00
  1 | alice    | A-002    | 300.00
  1 | alice    | A-003    | 200.00
  2 | bob      | B-001    | 400.00
  2 | bob      | B-002    | 400.00
  3 | carol    |          |

ON TRUE 看似多余，但它是 LEFT JOIN 的语法要求——LEFT JOIN 必须有 ON 条件，而 LATERAL 子查询本身已经完成了关联，所以写一个恒真的 ON TRUE 即可。

忘了写 ON TRUE 会报错

LEFT JOIN LATERAL (...) ; 缺少 ON 子句会直接语法报错：

text
ERROR:  syntax error at or near "ORDER"

必须写成 LEFT JOIN LATERAL (...) AS o ON TRUE。

集合返回函数在 FROM 中是隐式 LATERAL ​

unnest、generate_series、regexp_split_to_table 这类集合返回函数（SRF），当它们的参数引用了左侧表的列时，PostgreSQL 自动按 LATERAL 处理，不需要显式写 LATERAL：

sql
-- 数组示例表
CREATE TABLE demo_lat_tags (
  id   integer PRIMARY KEY,
  name text    NOT NULL,
  tags text[]
);

INSERT INTO demo_lat_tags (id, name, tags) VALUES
  (1, 'doc-1', ARRAY['sql', 'database', 'postgresql']),
  (2, 'doc-2', ARRAY['sql', 'performance']),
  (3, 'doc-3', ARRAY[]::text[]);

-- 隐式 LATERAL：unnest(t.tags) 引用了 t.tags
SELECT t.id, t.name, tag
FROM demo_lat_tags t,
     unnest(t.tags) AS tag
ORDER BY t.id, tag;
text
 id | name  |    tag
----+-------+------------
  1 | doc-1 | database
  1 | doc-1 | postgresql
  1 | doc-1 | sql
  2 | doc-2 | performance
  2 | doc-2 | sql

等价于显式写法：

sql
-- 显式 LATERAL，结果完全一致
SELECT t.id, t.name, tag
FROM demo_lat_tags t
CROSS JOIN LATERAL unnest(t.tags) AS tag
ORDER BY t.id, tag;

generate_series 同理，可以引用左表的数值列来动态生成序列：

sql
-- 为每个用户生成 1..3 的序号
SELECT u.id, u.username, n
FROM demo_lat_users u,
     generate_series(1, 3) AS n
ORDER BY u.id, n;
text
 id | username | n
----+----------+---
  1 | alice    | 1
  1 | alice    | 2
  1 | alice    | 3
  2 | bob      | 1
  2 | bob      | 2
  2 | bob      | 3
  3 | carol    | 1
  3 | carol    | 2
  3 | carol    | 3

注意区分：参数不含左表引用时，函数是独立的，不构成 LATERAL。generate_series(1, 3) 在这里因为常量参数，本质是与左表做笛卡尔积，只是写法上允许。

用 WITH ORDINALITY 保留元素顺序 ​

unnest 展开数组后不保证顺序，若需要保留数组下标，用 WITH ORDINALITY：

sql
-- WITH ORDINALITY 给展开结果附加序号
SELECT t.id, tag, ord
FROM demo_lat_tags t,
     unnest(t.tags) WITH ORDINALITY AS x(tag, ord)
WHERE t.id = 1
ORDER BY ord;
text
 id |    tag     | ord
----+------------+-----
  1 | sql        |   1
  1 | database   |   2
  1 | postgresql |   3

WITH ORDINALITY 会给结果追加一个从 1 开始的 bigint 序号列，配合 AS x(列名, 序号名) 重命名。

实战示例 ​

步骤 1：建表与造数 ​

sql
-- psql -d demo -f lateral_demo.sql

DROP TABLE IF EXISTS demo_orders;
DROP TABLE IF EXISTS demo_users;

CREATE TABLE demo_users (
  id       integer PRIMARY KEY,
  username text    NOT NULL,
  city     text    NOT NULL
);

CREATE TABLE demo_orders (
  id         bigserial     PRIMARY KEY,
  user_id    integer       NOT NULL,
  order_no   text          NOT NULL,
  amount     numeric(10,2) NOT NULL,
  created_at timestamptz   NOT NULL
);

INSERT INTO demo_users (id, username, city) VALUES
  (1, 'alice', 'beijing'),
  (2, 'bob',   'beijing'),
  (3, 'carol', 'shanghai'),
  (4, 'dave',  'shanghai');

INSERT INTO demo_orders (user_id, order_no, amount, created_at) VALUES
  (1, 'A-001', 500.00, '2026-06-01 10:00:00+08'),
  (1, 'A-002', 300.00, '2026-06-05 11:00:00+08'),
  (1, 'A-003', 200.00, '2026-06-10 09:00:00+08'),
  (1, 'A-004', 100.00, '2026-06-15 14:00:00+08'),
  (2, 'B-001', 400.00, '2026-06-02 12:00:00+08'),
  (2, 'B-002', 400.00, '2026-06-03 12:00:00+08'),
  (3, 'C-001', 350.00, '2026-06-04 08:00:00+08');
-- dave 无订单

-- 为 Top-N 查询建复合索引
CREATE INDEX idx_demo_orders_user_amount
  ON demo_orders (user_id, amount DESC, id);

步骤 2：每个用户最近的 3 笔订单 ​

「最近」按 created_at 降序，与 Top-N 的「金额最高」不同，正好演示 LATERAL 的灵活性——想换排序维度只需改子查询里的 ORDER BY：

sql
-- 每个用户最近 3 笔订单（按时间）
SELECT
  u.id,
  u.username,
  o.order_no,
  o.amount,
  to_char(o.created_at, 'YYYY-MM-DD') AS 下单日期
FROM demo_users u
LEFT JOIN LATERAL (
  SELECT order_no, amount, created_at
  FROM demo_orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 3
) AS o ON TRUE
ORDER BY u.id, o.created_at DESC NULLS LAST;
text
 id | username | order_no | amount  |  下单日期
----+----------+----------+---------+------------
  1 | alice    | A-004    |  100.00 | 2026-06-15
  1 | alice    | A-003    |  200.00 | 2026-06-10
  1 | alice    | A-002    |  300.00 | 2026-06-05
  2 | bob      | B-002    |  400.00 | 2026-06-03
  2 | bob      | B-001    |  400.00 | 2026-06-02
  3 | carol    | C-001    |  350.00 | 2026-06-04
  4 | dave     |          |         |

alice 有 4 笔订单，只取最近 3 笔（A-004、A-003、A-002）；dave 无订单，因用了 LEFT JOIN LATERAL 被保留，各列为空。

步骤 3：观察执行计划 ​

用 EXPLAIN (ANALYZE, BUFFERS) 看 LATERAL 是否按左表行数循环执行：

bash
psql -d demo -c "EXPLAIN (ANALYZE, BUFFERS) SELECT u.id, o.order_no FROM demo_users u LEFT JOIN LATERAL (SELECT order_no FROM demo_orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC, o.id DESC LIMIT 3) o ON TRUE;"
text
 Nested Loop Left Join  (cost=0.28..62.14 rows=4 width=36) (actual time=0.031..0.086 rows=7 loops=1)
   ->  Seq Scan on demo_users u  (cost=0.00..1.04 rows=4 width=8) (actual time=0.008..0.011 rows=4 loops=1)
   ->  Limit  (cost=0.28..15.20 rows=1 width=44) (actual time=0.015..0.017 rows=3 loops=4)
         ->  Index Scan using idx_demo_orders_user_amount on demo_orders o  (...)
               Index Cond: (user_id = u.id)
 Planning Time: 0.312 ms
 Execution Time: 0.152 ms

关键看 loops=4：内层 Limit 节点执行了 4 次，正好等于左表 demo_users 的行数。这就是 LATERAL 的「逐行驱动」特征——内层索引扫描的 Index Cond: (user_id = u.id) 随左表每行变化。有索引时这是极高效的；没索引时会退化成 N 次全表扫描。

注意事项 ​

  1. 不带 LATERAL 的 FROM 子查询不能引用同级的其他表，这是最基本的规则：

    sql
    -- 错误！会报 invalid reference to FROM-clause entry
SELECT * FROM users u, (SELECT * FROM orders WHERE user_id = u.id) o;

  2. CROSS JOIN LATERAL 会丢弃无匹配的左表行。要保留左表全部行，必须用 LEFT JOIN LATERAL ... ON TRUE：

    sql
    -- 不推荐（会丢行）：只想要有订单的用户时可用
CROSS JOIN LATERAL (SELECT ... WHERE o.user_id = u.id LIMIT 3) o

-- 推荐（保留行）：用户列表场景
LEFT JOIN LATERAL (SELECT ... WHERE o.user_id = u.id LIMIT 3) o ON TRUE

  3. LEFT JOIN LATERAL 必须写 ON TRUE，漏掉会语法报错。

  4. LATERAL 只能引用左侧的 FROM 项，写成 FROM LATERAL(...) s, users u 会报错。

  5. WITH ORDINALITY 需要 PostgreSQL 9.4+，早于该版本请用 row_number() 替代。

  6. string_to_table 需要 PostgreSQL 14+，低版本用 regexp_split_to_table。

  7. LATERAL 子查询里若没有 LIMIT，可能返回大量行，与左表做笛卡尔积式放大，内存和结果行数都会暴涨。

  8. 内层没索引时 LATERAL 会变成 N 次全表扫描，大表上性能灾难。务必确认内层有可用索引。

最佳实践 ​

  1. 优先用 LEFT JOIN LATERAL ... ON TRUE，除非明确要「只保留有匹配的行」。这样默认不丢数据，更安全。

  2. LATERAL 子查询必须带 LIMIT，尤其是 Top-N 场景，否则失去 LATERAL 的意义：

    sql
    LEFT JOIN LATERAL (
  SELECT order_no, amount
  FROM demo_orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC, o.id
  LIMIT 3
) o ON TRUE

  3. 为内层查询建「关联列 + 排序键」的复合索引，让 LATERAL 走索引扫描而非全表扫：

    内层查询建议索引
    WHERE user_id = u.id ORDER BY amount DESC LIMIT 3(user_id, amount DESC, id)
    WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3(user_id, created_at DESC, id)
    WHERE user_id = u.id AND status = 'paid'(user_id, status)

  4. 在 LATERAL 之前先用 WHERE 把左表缩小，减少外层循环次数：

    sql
    -- 推荐：先过滤出目标用户，再逐行取订单
SELECT u.id, o.order_no
FROM demo_users u
LEFT JOIN LATERAL (
  SELECT order_no FROM demo_orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC LIMIT 3
) o ON TRUE
WHERE u.city = 'beijing';

  5. 集合返回函数直接写在 FROM 里，不用手写 LATERAL，PostgreSQL 会自动处理。

  6. 需要保留数组元素顺序时加 WITH ORDINALITY，并在 ORDER BY 里引用序号列。

  7. 用 EXPLAIN (ANALYZE, BUFFERS) 确认 loops 数，判断内层是否按左表行数循环执行：

    bash
    psql -d demo -c "EXPLAIN (ANALYZE, BUFFERS) SELECT u.id, o.order_no FROM demo_users u LEFT JOIN LATERAL (SELECT order_no FROM demo_orders o WHERE o.user_id = u.id ORDER BY o.amount DESC LIMIT 3) o ON TRUE;"

下一节

继续学习：LATERAL 与相关子查询

参考链接 ​