PostgreSQL / 系统课时

LATERAL 与复杂连接

LATERAL 与复杂连接

当右侧子查询必须引用左侧当前行,或你只关心“是否存在匹配”而不是把两边所有行拼出来时,普通连接往往会让 SQL 变得绕。LATERAL、EXISTS 与反连接正是为这些关联语义准备的工具。

学习目标

  • 使用 LATERAL 为左表每一行执行相关子查询
  • 用 EXISTS 与 NOT EXISTS 表达半连接和反连接
  • 根据结果基数与索引访问方式选择更合适的关联写法

LATERAL 解决什么问题

LATERAL 允许 FROM 中右侧子查询引用左侧已经出现的表。可以把它理解为:先取左侧一行,再把这一行的列作为参数交给右侧子查询。右侧可以返回零行、一行或多行,因此它特别适合“每个客户最近一单”“每个分组前 N 条”这类逐行派生结果。

LEFT JOIN LATERAL ... ON true 会保留没有匹配结果的左侧行;CROSS JOIN LATERAL 则只保留右侧实际返回了行的组合。

LATERAL、半连接与反连接的关系

用一组数据验证三种关联

下面脚本先建立临时表,再分别查询“每位客户最近订单”“存在大额订单的客户”和“从未下单的客户”。临时表只在当前会话存在,适合直接复制到测试库验证。

sql
-- 临时表让示例可在独立测试会话中直接运行。
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;

预期结果

text
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 可以对每个客户快速定位第一条记录,而不是反复扫描整个订单表。

sql
-- 索引顺序同时匹配相关过滤条件与“最近一条”的排序。
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;

预期结果

text
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 直接表达这个语义,通常比事后用去重和额外过滤修补结果更可靠。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

LATERAL 与复杂连接

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通