MySQL LATERAL 派生表怎么引用前面的表

MySQL 派生表要引用同一 FROM 子句中位于它前面的表，需要在派生表前写 LATERAL，并把被引用表放在左侧。最常见的形式是 LEFT JOIN LATERAL (...) AS alias ON TRUE：左侧表提供当前行的列，右侧派生表据此计算一组相关结果，再把这些列交给外层查询。

例如“为每个门店取最新一笔已支付订单”，普通派生表里的 WHERE o.shop_id = s.id 会因为看不到 s.id 而报错；改成 LATERAL 派生表后，这个相关引用才合法。MySQL 从 8.0.14 开始支持这种写法，8.4 手册继续保留了相同的核心规则。

MySQL 8.4 官方文档：https://dev.mysql.com/doc/refman/8.4/en/lateral-derived-tables.html

先明确 LATERAL 解决什么

普通派生表通常应当独立于同一层 FROM 中的其他表。下面这个查询看似自然，但派生表 recent_order 不能直接识别外层的 s.id：

SELECT s.id, s.name, recent_order.order_id
FROM shops AS s
LEFT JOIN (
    SELECT o.id AS order_id
    FROM orders AS o
    -- 普通派生表不能引用同层 FROM 中前面的 s.id
    WHERE o.shop_id = s.id
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 1
) AS recent_order ON TRUE;

这类写法会出现 Unknown column 's.id' in 'where clause'。问题不在别名拼错，而在作用域：普通派生表不被允许依赖左侧表。LATERAL 的作用就是明确声明“这个派生表依赖它左边已经出现的表”。

用最小查询引用前面的表

下面使用 shops 和 orders 两张表。目标是返回所有门店，并附带每个门店最新一笔已支付订单；没有订单的门店仍要保留。

SELECT
    s.id,
    s.name,
    recent_order.order_id,
    recent_order.created_at,
    recent_order.total_amount
FROM shops AS s
LEFT JOIN LATERAL (
    SELECT
        o.id AS order_id,
        o.created_at,
        o.total_amount
    FROM orders AS o
    -- 这里可以引用左侧 shops 当前行的 s.id
    WHERE o.shop_id = s.id
      AND o.status = 'paid'
    -- id 作为并列时间的稳定兜底排序键
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 1
) AS recent_order ON TRUE;

语法里有三个不能省略的角色：

• shops AS s 必须先出现在左侧，后面的派生表才能引用 s.id；
• LATERAL 放在左括号前，授权派生表建立这个相关引用；
• AS recent_order 为派生表提供别名，外层才能选择它返回的列。

ON TRUE 不是说查询没有条件。真正的相关条件已经写在派生表内部，ON TRUE 只是完成 LEFT JOIN 的连接语法。若业务还有外层连接条件，也可以替换为明确条件，但不要把会过滤空结果的条件随意挪到外层 WHERE。

选择 JOIN 类型并保护外层行

LATERAL 决定“能否引用左侧列”，JOIN 类型决定“派生表没有返回行时怎么办”。这两个选择应分开考虑。

• JOIN LATERAL 或 INNER JOIN LATERAL：只有派生表能返回匹配行时，外层行才保留；
• CROSS JOIN LATERAL：同样适合必须有派生结果的场景；
• LEFT JOIN LATERAL：即使派生表无结果，也保留左侧行，派生列显示为 NULL。

门店列表通常需要显示“尚无订单”的门店，因此更适合 LEFT JOIN LATERAL。如果随后又写了下面的外层过滤，LEFT JOIN 的保留效果会被破坏：

SELECT s.id, s.name, recent_order.order_id
FROM shops AS s
LEFT JOIN LATERAL (
    SELECT o.id AS order_id, o.total_amount
    FROM orders AS o
    -- 相关条件留在派生表内部
    WHERE o.shop_id = s.id
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 1
) AS recent_order ON TRUE
-- 这个 WHERE 会排除 recent_order 为空的门店
WHERE recent_order.total_amount > 0;

如果确实要保留无订单门店，应把业务条件放入派生表，或者显式允许 recent_order.order_id IS NULL。上线前先写清楚结果基数：每个门店必须一行、最多一行，还是只返回有匹配订单的门店。

把排序和索引配成一组

LATERAL 让 SQL 能表达“每个门店各取一行”，但它不会自动让查询变快。派生表依赖左侧当前行，左侧行数越多，相关查找就越需要合适索引。对于上面的条件和排序，可以考虑以下复合索引：

-- 前两列服务等值过滤，后两列服务稳定的倒序取最新行
CREATE INDEX idx_orders_shop_status_created_id
    ON orders (shop_id, status, created_at DESC, id DESC);

索引顺序不是固定模板，它必须和真实查询条件一致。这里先按 shop_id、status 做等值过滤，再按 created_at DESC, id DESC 取第一行，所以这组顺序比较自然。若状态条件选择性、数据分布或查询形态不同，应以实际执行计划为准。

稳定排序尤其重要。只按 created_at DESC 排序时，时间相同的多条订单没有确定先后；再加入唯一的 id DESC，每个门店的“最新一笔”才有稳定定义。生产查询不要用 SELECT *，只返回外层真正需要的列，减少派生结果宽度和回表成本。

上线前可以用 EXPLAIN 查看计划，但不要只看到“用了索引”就结束评审：

-- 只分析计划，不把示例输出冒充真实环境结果
EXPLAIN FORMAT=TREE
SELECT s.id, recent_order.order_id
FROM shops AS s
LEFT JOIN LATERAL (
    SELECT o.id AS order_id
    FROM orders AS o
    -- 检查相关过滤是否利用复合索引前缀
    WHERE o.shop_id = s.id
      AND o.status = 'paid'
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 1
) AS recent_order ON TRUE;

重点关注外层预计行数、相关部分的访问方式、每次查找读取的行数、是否出现额外排序，以及左侧过滤能否尽早缩小门店集合。若左侧一次返回几十万行，即使单次相关查找很快，总成本也可能明显。

处理语法限制和特殊对象

MySQL 对 LATERAL 派生表有明确限制。把这些限制当作作用域和 JOIN 方向规则，会比死记语法更容易排错。

只能引用已经位于允许方向的表

当 LATERAL 派生表位于 JOIN 右侧并引用左侧时，连接必须是 INNER JOIN、CROSS JOIN 或 LEFT JOIN。如果把依赖关系写反，表还没有进入可见范围，就不能被引用。最直观的写法是始终把提供相关列的表放左边。

派生表必须有别名

LATERAL (SELECT ...) 后仍然要写别名。外层引用的不是内部表别名 o，而是派生表别名，例如 recent_order.order_id。

聚合有额外作用域限制

官方手册指出，如果 LATERAL 派生表引用聚合函数，该聚合所属查询不能正好就是拥有该 FROM 子句的查询块。遇到复杂聚合时，先画清查询块层级，再决定聚合应放在派生表内还是外层。

JSON_TABLE 不要再写 LATERAL

MySQL 按 SQL 标准把与 JSON_TABLE() 的连接视为隐式 LATERAL。它本来就能引用前置表的 JSON 列，因此不允许在 JSON_TABLE() 前再显式写 LATERAL。

SELECT p.id, jt.sku
FROM products AS p
JOIN JSON_TABLE(
    p.variants,
    '$[*]' COLUMNS (
        -- JSON_TABLE 隐式具备 LATERAL 行为
        sku VARCHAR(64) PATH '$.sku'
    )
) AS jt ON TRUE;

上线前检查查询边界

把 LATERAL 查询放进生产环境前，可以按下面的清单逐项确认：

1. 版本：服务器必须是 MySQL 8.0.14 或更高版本；部署环境和本地语法支持要一致。
2. 引用方向：被引用表位于 LATERAL 派生表左侧，所有外层列都有明确别名。
3. 结果基数：是否必须保留无匹配的外层行，JOIN 类型与产品需求一致。
4. 稳定取行：ORDER BY 包含唯一兜底键，LIMIT 的含义可重复。
5. 索引：相关过滤列和排序列有匹配的复合索引，并用实际执行计划确认。
6. 外层规模：先限制左侧集合，避免对过多外层行做相关查找。
7. 观测：上线后关注查询耗时、扫描行数、返回行数和调用频率，而不是只比较单次样例。

常见问题

LATERAL 必须和 LEFT JOIN 一起用吗？

不是。它可以出现在逗号分隔的表列表，也可以和 JOIN、INNER JOIN、CROSS JOIN、LEFT JOIN 或符合方向限制的 RIGHT JOIN 组合。选择依据是无匹配结果时是否保留外层行。

为什么加了别名还是 Unknown column？

先检查被引用表是否位于 LATERAL 派生表之前，再检查是否真的写了 LATERAL。别名只负责命名，不能突破普通派生表的作用域限制。

LATERAL 一定比窗口函数快吗？

不一定。少量外层行配合“相关键 + 排序列”的索引时，LATERAL 往往很直接；需要一次处理大范围数据时，窗口函数或先聚合再 JOIN 可能更合适。最终要比较真实执行计划和数据分布。

每个外层行取前 3 条怎么写？

保持相关条件和稳定排序，把派生表内的 LIMIT 1 改为 LIMIT 3。此时每个外层行最多展开三行，调用方必须接受结果基数变化。

把相关依赖写在 SQL 结构里

LATERAL 最有价值的地方，不是少写几个字符，而是把“右侧结果依赖左侧当前行”明确写进 FROM 结构。只要被引用表放在左侧、派生表加上 LATERAL、JOIN 类型符合保留需求，再配合稳定排序和相关索引，就能把按行取最新记录、Top N 或多列相关结果写得清楚而可控。