SQL CROSS JOIN LATERAL：把它理解成「左边每一行，都执行一次右边查询」

第一次看到 PostgreSQL 的：

CROSS JOIN LATERAL

很多人会觉得很复杂：CROSS JOIN 已经不太直觉，为什么后面还要加一个 LATERAL？

其实先记住一句话就够了：

LATERAL 允许右边的子查询，使用左边「当前这一行」的数据。

如果你是程序员，可以把它先想成 JavaScript 的 for each。

---

1. 从一个简单例子开始

假设有 customers：

id | name
---+-------
1  | Apple
2  | Google
3  | OpenAI

以及 orders：

id | customer_id | order_date | amount
---+-------------+------------+-------
10 | 1           | 2026-01-01 | 100
11 | 1           | 2026-03-01 | 200
12 | 2           | 2026-02-01 | 500

现在需求是：

每一个 Customer，只拿他最新的一张 Order。

可以写：

SELECT
    c.id,
    c.name,
    o.id AS order_id,
    o.order_date,
    o.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT
        id,
        order_date,
        amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
    LIMIT 1
) o;

最关键的是这一行：

WHERE o.customer_id = c.id

c.id 来自外面的 customers c。

也就是说，右边这个 subquery 可以看到左边当前 Customer 的 id。

这就是 LATERAL 的核心能力。

---

2. 用程序员思维理解

不要一开始把它当成复杂 JOIN。

先想成：

for (const customer of customers) {
    const order = orders
        .filter(o => o.customer_id === customer.id)
        .sort(/* latest first */)
        .first();

    output(customer, order);
}

对应 SQL：

FROM customers c

CROSS JOIN LATERAL (
    ...
    WHERE o.customer_id = c.id
    ...
) o

逻辑上可以这样读：

拿 Customer 1
    ↓
执行右边 Subquery
    ↓
找 Customer 1 最新 Order

拿 Customer 2
    ↓
执行右边 Subquery
    ↓
找 Customer 2 最新 Order

所以以后看到 LATERAL，脑里面可以先翻译成：

For each left row, evaluate the right-side query using that row.

中文就是：

针对左边每一行，用这一行的数据执行右边查询。

---

3. 为什么普通 Subquery 不够？

如果写成：

SELECT *
FROM customers c
CROSS JOIN (
    SELECT *
    FROM orders o
    WHERE o.customer_id = c.id
) o;

问题在于右边普通 FROM subquery 通常不能直接引用外层的 c.id。

加上：

LATERAL

以后：

CROSS JOIN LATERAL (
    SELECT *
    FROM orders o
    WHERE o.customer_id = c.id
) o

就是明确告诉 PostgreSQL：

这个右边查询不是完全独立的，它可以依赖前面已经出现的 table。

所以可以把：

LATERAL

记成：

允许右边依赖左边。

---

4. 那 CROSS JOIN 又负责什么？

这里其实有两个不同概念：

CROSS JOIN
+
LATERAL

LATERAL 负责：

右边能不能读取左边当前 row

CROSS JOIN 负责：

右边返回的 rows 如何与左边组合

假设左边当前是：

Customer A

右边 LATERAL query 返回三行：

Order 1
Order 2
Order 3

最终会展开成：

Customer A | Order 1
Customer A | Order 2
Customer A | Order 3

所以右边返回多少行，左边当前这一行就可能扩展成多少行。

---

5. 最重要的区别：右边返回 0 行怎么办？

这是理解 CROSS JOIN LATERAL 最关键的一步。

假设有：

Apple
Google
OpenAI

但是 OpenAI 没有任何 Order。

如果使用：

FROM customers c
CROSS JOIN LATERAL (
    SELECT *
    FROM orders o
    WHERE o.customer_id = c.id
    LIMIT 1
) o

对于 OpenAI，右边返回 0 rows。

结果是：

OpenAI 这一整行不会出现在最终结果。

因为这是 CROSS JOIN LATERAL。

如果你的业务需求是：

即使没有 Order，我还是要保留 Customer。

那么应该考虑：

LEFT JOIN LATERAL

例如：

SELECT
    c.id,
    c.name,
    o.id AS order_id,
    o.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT
        id,
        order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) o ON TRUE;

结果可以是：

Apple  | 11
Google | 12
OpenAI | NULL

所以最重要的区别是：

Syntax	右边可使用左边当前 row？	右边 0 rows 时左边保留？
CROSS JOIN	通常不可以	❌
CROSS JOIN LATERAL	✅	❌
LEFT JOIN LATERAL ... ON TRUE	✅	✅

在 ERP SQL 里面，可以先问自己：

如果找不到 related data，这张 document 还需要保留吗？

如果不需要，可以考虑 CROSS JOIN LATERAL。

如果需要，通常更适合 LEFT JOIN LATERAL ... ON TRUE。

---

6. ON TRUE 是什么意思？

你会经常看到：

LEFT JOIN LATERAL (
    ...
) x ON TRUE

为什么 ON TRUE？

因为真正的关联条件已经放进 LATERAL subquery 里面了，例如：

WHERE o.customer_id = c.id

所以：

ON TRUE

可以理解成：

右边 subquery 已经自己决定要返回哪些 row；它返回什么，我就接什么。

实际的 Customer ↔ Order 关系仍然是：

o.customer_id = c.id

只是这个条件写在右边 subquery 的 WHERE，而不是外层 JOIN 的 ON。

---

7. LATERAL 的经典用途：Top 1 per parent

这是我认为最值得掌握的场景。

例如 ERP 里面：

每一张 Sales Order，找到最新的一张 Delivery Order。

可以写：

SELECT
    so.document_no,
    latest_do.document_no AS latest_delivery_no
FROM sales_order so
LEFT JOIN LATERAL (
    SELECT
        d.document_no,
        d.date_trans
    FROM delivery_order d
    WHERE d.sales_order_id = so.id
    ORDER BY d.date_trans DESC
    LIMIT 1
) latest_do ON TRUE;

这里：

LIMIT 1

并不是整个 SQL 最终只拿一行。

而是：

SO-001 → 最新 DO 一行
SO-002 → 最新 DO 一行
SO-003 → 最新 DO 一行

也就是：

Top 1 for each parent row.

这个模式在 ERP 很常见：

• 找最新 Status
• 找最新 Delivery
• 找最近一次 Posting
• 找某个 document 最匹配的 child record
• 找每个 parent 的 Top 1 related transaction

---

8. 和普通 JOIN 有什么不同？

普通 JOIN 很自然地表达：

SELECT *
FROM customers c
JOIN orders o
    ON o.customer_id = c.id;

意思是：

customers ↔ orders
按照关系匹配

但是如果需求变成：

每个 Customer 只找最新一个 Order。

你需要表达：

针对当前 Customer
→ 找它的 orders
→ ORDER BY date DESC
→ LIMIT 1

这个 LIMIT 1 是针对每一个 Customer，而不是整个结果集。

这正是 LATERAL 特别自然的地方。

---

9. 和 Correlated Subquery 又有什么不同？

你当然也可以写：

SELECT
    c.id,
    c.name,
    (
        SELECT o.id
        FROM orders o
        WHERE o.customer_id = c.id
        ORDER BY order_date DESC
        LIMIT 1
    ) AS latest_order_id
FROM customers c;

这也是 correlated subquery。

但如果你要同时拿：

order_id
order_date
amount
status
currency

写很多独立 scalar subquery 会很难维护。

用 LATERAL：

LEFT JOIN LATERAL (
    SELECT
        o.id,
        o.order_date,
        o.amount,
        o.status,
        o.currency
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) latest_order ON TRUE

外层就可以自然选择多个 column。

---

10. 一个值得长期记住的 Mental Model

以后看到：

FROM A

CROSS JOIN LATERAL (
    SELECT ...
    FROM B
    WHERE B.xxx = A.xxx
) X

不要一次过把整句 SQL 吞进去。

拆成六步：

Step 1  从 A 拿一行
Step 2  把这一行的数据交给右边 query
Step 3  执行右边 query
Step 4  右边返回 0 / 1 / N 行
Step 5  与当前 A row 组合
Step 6  处理下一行 A

逻辑图就是：

A Row 1
   │
   └──▶ LATERAL query
           ├── Result 1
           └── Result 2

A Row 2
   │
   └──▶ LATERAL query
           └── Result 3

A Row 3
   │
   └──▶ LATERAL query
           └── no result

如果是 CROSS JOIN LATERAL，Row 3 消失。

如果是 LEFT JOIN LATERAL，Row 3 保留，右边 columns 是 NULL。

---

11. 为什么复杂 ERP SQL 很常看到 LATERAL？

因为很多 ERP 查询的本质都是：

对于当前 Document
→ 找它相关的某些东西

例如：

当前 document → 找 latest status
当前 document → 找 parent document
当前 document → 找 matching line
当前 document → 找一个 token
当前 document → 找最接近的 transaction
当前 document → 找 top 1 related record

这些逻辑都有共同特征：

当前这一行，会影响下一段查询。

因此非常适合 LATERAL。

---

12. Performance：不要看到 LATERAL 就自动认为慢

从逻辑上，我们常说：

每一行执行一次右边 query。

这个 mental model 很适合帮助理解语义，但 PostgreSQL Planner 实际执行时仍然可能进行优化。

真正需要关注的是：

• 左边会产生多少 rows？
• 右边查的是不是 huge table？
• 关联条件够不够 selective？
• 有没有合适的 index？
• 有没有 ORDER BY + LIMIT 1？
• Planner 最终选了什么 execution plan？

例如：

WHERE d.parent_id = current.id
ORDER BY d.date_trans DESC
LIMIT 1

如果 parent_id 和排序路径有合适的索引，可能非常有效率。

如果左边一百万行、右边又不断扫描大表，就可能很昂贵。

所以不要只看 SQL syntax 判断性能。

应该实际看：

EXPLAIN

以及在安全环境中：

EXPLAIN ANALYZE

---

13. 最后只记住两个问题

问题一

右边 Query 是否需要使用左边当前 row？

如果 Yes，想到：

LATERAL

问题二

如果右边找不到数据，左边这一行还要不要？

如果不要：

CROSS JOIN LATERAL

如果要：

LEFT JOIN LATERAL (...) x ON TRUE

---

一句话掌握它

CROSS JOIN LATERAL 可以先理解成：

For each row on the left, run the query on the right using that row, and return only rows where the right side produces a result.

简单中文：

左边拿一行，右边用这行资料去查；查到几行就出几行，完全查不到，这个左边 row 就没有了。

而 LEFT JOIN LATERAL 就是：

查不到也没关系，左边 row 还是保留，右边给 NULL。

真正掌握下面两句以后，CROSS JOIN LATERAL 就不再神秘：

LATERAL = 右边可以依赖左边当前 row

CROSS / LEFT = 右边找不到时，左边要不要留下