PostgreSQL / 系统课时
LATERAL 与复杂连接
LATERAL 与复杂连接
当右侧子查询必须引用左侧当前行,或你只关心“是否存在匹配”而不是把两边所有行拼出来时,普通连接往往会让 SQL 变得绕。LATERAL、EXISTS 与反连接正是为这些关联语义准备的工具。
学习目标
- 使用
LATERAL为左表每一行执行相关子查询 - 用
EXISTS与NOT EXISTS表达半连接和反连接 - 根据结果基数与索引访问方式选择更合适的关联写法
LATERAL 解决什么问题
LATERAL 允许 FROM 中右侧子查询引用左侧已经出现的表。可以把它理解为:先取左侧一行,再把这一行的列作为参数交给右侧子查询。右侧可以返回零行、一行或多行,因此它特别适合“每个客户最近一单”“每个分组前 N 条”这类逐行派生结果。
LEFT JOIN LATERAL ... ON true 会保留没有匹配结果的左侧行;CROSS JOIN LATERAL 则只保留右侧实际返回了行的组合。

用一组数据验证三种关联
下面脚本先建立临时表,再分别查询“每位客户最近订单”“存在大额订单的客户”和“从未下单的客户”。临时表只在当前会话存在,适合直接复制到测试库验证。
-- 临时表让示例可在独立测试会话中直接运行。
CREATE TEMP TABLE customers (
id bigint PRIMARY KEY,
name text NOT NULL
);
CREATE TEMP TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
ordered_at timestamp NOT NULL,
total numeric(10, 2) NOT NULL
);
-- 准备三位客户,其中 Grace 没有订单。
INSERT INTO customers (id, name) VALUES
(1, 'Ada'),
(2, 'Linus'),
(3, 'Grace');
INSERT INTO orders (id, customer_id, ordered_at, total) VALUES
(101, 1, '2026-09-01 10:00:00', 80.00),
(102, 1, '2026-09-02 09:00:00', 120.00),
(201, 2, '2026-09-02 15:00:00', 60.00);
-- LATERAL:为每位客户取最近一笔订单。
SELECT
c.id,
c.name,
latest.order_id,
latest.ordered_at,
latest.total
FROM customers AS c
LEFT JOIN LATERAL (
SELECT
o.id AS order_id,
o.ordered_at,
o.total
FROM orders AS o
WHERE o.customer_id = c.id
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 1
) AS latest ON true
ORDER BY c.id;
-- EXISTS:只判断是否至少存在一笔大额订单。
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
AND o.total >= 100
)
ORDER BY c.id;
-- NOT EXISTS:筛出完全没有订单的客户。
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
)
ORDER BY c.id;预期结果
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
id | name | order_id | ordered_at | total
----+-------+----------+-------------------------+--------
1 | Ada | 102 | 2026-09-02 09:00:00 | 120.00
2 | Linus | 201 | 2026-09-02 15:00:00 | 60.00
3 | Grace | NULL | NULL | NULL
id | name
----+------
1 | Ada
id | name
----+-------
3 | Grace第一个查询中,Grace 没有订单但仍被 LEFT JOIN LATERAL 保留下来;第二个查询只返回至少存在一笔 total >= 100 订单的客户;第三个查询只返回完全没有订单的客户。
半连接与反连接的语义
EXISTS 对应半连接语义:右侧只负责证明“至少存在一个匹配”,最终只输出左侧行。即使右侧匹配十行,左侧客户也只出现一次。NOT EXISTS 则是反连接语义:只有右侧一个匹配都没有时,左侧行才保留。
这两种写法通常比“先普通 JOIN 再 DISTINCT”更贴近业务意图,也避免因为右侧多行匹配而意外放大结果集。对于反连接,NOT EXISTS 还比 NOT IN (subquery) 更容易正确处理右侧可能出现 NULL 的情况。
让关联方式匹配索引
“每个客户最近一单”的 LATERAL 子查询最好有与过滤和排序方向匹配的索引,例如 (customer_id, ordered_at DESC, id DESC)。这样 PostgreSQL 可以对每个客户快速定位第一条记录,而不是反复扫描整个订单表。
-- 索引顺序同时匹配相关过滤条件与“最近一条”的排序。
CREATE INDEX orders_customer_latest_idx
ON orders (customer_id, ordered_at DESC, id DESC);
EXPLAIN
SELECT
c.id,
latest.order_id
FROM customers AS c
LEFT JOIN LATERAL (
SELECT o.id AS order_id
FROM orders AS o
WHERE o.customer_id = c.id
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 1
) AS latest ON true;预期结果
CREATE INDEX
Nested Loop Left Join
-> Seq Scan on customers c
-> Limit
-> <INDEX_OR_SORT_PLAN> on orders o测试数据很小时,规划器仍可能选择顺序扫描,因此 <INDEX_OR_SORT_PLAN> 的具体节点会不同;在更大数据集上,匹配索引更容易体现价值。选择语法时先确定需要的是“拼接右侧列”“只判断存在”“只找不存在”还是“对每个左侧行执行 Top-N 子查询”,再让 SQL 直接表达这个语义,通常比事后用去重和额外过滤修补结果更可靠。