PostgreSQL 中的 LATERAL JOIN 和子查询有什么区别？

Question

由于 PostgreSQL 能够执行 LATERAL 连接，因此我一直在阅读它，因为我目前为我的团队进行复杂的数据转储，其中包含许多低效的子查询，这使得整个查询需要四分钟或更多的。

我知道 LATERAL 连接可能会对我有所帮助，但即使在阅读 了 来自 Heap Analytics 的类似文章之后，我仍然不太了解。

LATERAL 加入的用例是什么？ LATERAL 连接和子查询有什么区别？

原文由 jdotjdot 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

什么 是 LATERAL 加入？

该功能是在 PostgreSQL 9.3 中引入的。 手册：

出现在 FROM 中的子查询可以在关键字 LATERAL 前面。这允许他们引用前面的 FROM 项目提供的列。 （没有 LATERAL ，每个子查询都是独立评估的，因此不能交叉引用任何其他 FROM 项。）

出现在 FROM 中的表函数也可以在关键字 LATERAL 之前，但对于函数，关键字是可选的；在任何情况下，函数的参数都可以包含对前面 FROM 项提供的列的引用。

那里给出了基本的代码示例。

更像是一个 相关 的子查询

LATERAL 连接更像是 相关子查询，而不是普通子查询，因为 LATERAL 连接右侧的表达式对其左侧的每一行进行一次评估 - 就像 相关 子查询 - 而普通子查询（表表达式）只评估 _一次_。 （不过，查询规划器有办法优化两者的性能。）

相关答案与并排的代码示例，解决了相同的问题：

• 优化 GROUP BY 查询以检索每个用户的最新行

对于返回 _多于一列_， LATERAL 连接通常更简单、更干净、更快。

另外，请记住，相关子查询的等价物是 LEFT JOIN LATERAL ... ON true ：

• 多次调用带有数组参数的集合返回函数

子查询不能做的事情

有些 事情是 LATERAL join 可以做的，但（相关的）子查询不能（容易）。相关子查询只能返回单个值，不能返回多列，也不能返回多行——除了裸函数调用（如果返回多行，则将结果行相乘）。但即使是某些集合返回函数也只允许在 FROM 子句中使用。像 unnest() 在 Postgres 9.4 或更高版本中具有多个参数。 手册：

这仅在 FROM 子句中允许；

所以这可行，但不能（容易）用子查询替换：

 CREATE TABLE tbl (a1 int[], a2 int[]);
SELECT * FROM tbl, unnest(a1, a2) u(elem1, elem2);  -- implicit LATERAL

FROM 子句中的逗号（ , ）是 --- 的 CROSS JOIN 。

LATERAL 自动假定为表函数。

关于 UNNEST( array_expression [, ... ] ) 的特殊情况：

• 你如何声明一个 set-returning-function 只允许在 FROM 子句中使用？

在 SELECT 列表中设置返回函数

您还可以直接在 SELECT 列表中使用设置返回函数，如 unnest() 。这曾经在相同的 SELECT 列表中表现出令人惊讶的行为，直到Postgres 9.6。 但它终于用 Postgres 10 进行了清理， 现在是一个有效的替代方案（即使不是标准 SQL）。看：

• SELECT 子句中多个集合返回函数的预期行为是什么？

基于上面的例子：

 SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2
FROM   tbl;

比较：

_第 9.6 页的 dbfiddle 在这里_

_第 10 页的 dbfiddle 在这里_

澄清错误信息

手册：

For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL , ON _join_condition_ , or USING ( _join_column_ [, …])。含义见下文。

对于 CROSS JOIN ，这些子句都不能出现。

所以这两个查询是有效的（即使不是特别有用）：

 SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON TRUE;

SELECT *
FROM   tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

虽然这不是：

 SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

这就是为什么 Andomar 的 代码示例是正确的（ CROSS JOIN 不需要连接条件）而 Attila 的不是。

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

数据库表

具有以下 blog 数据库表存储我们平台托管的博客：

而且，我们目前托管了两个博客：

ID创建于标题网址1 2013-09-30 Vlad Mihalcea 的博客https://vladmihalcea.com 2 2017-01-22过敏症https://hypersistence.io

在不使用 SQL LATERAL JOIN 的情况下获取我们的报告

我们需要构建一个报告，从 blog 表中提取以下数据：

• 博客 ID
• 博客时代，以年为单位
• 下一个博客周年纪念日
• 距离下一个周年纪念日的剩余天数。

如果您使用的是 PostgreSQL，那么您必须执行以下 SQL 查询：

 SELECT
  b.id as blog_id,
  extract(
    YEAR FROM age(now(), b.created_on)
  ) AS age_in_years,
  date(
    created_on + (
      extract(YEAR FROM age(now(), b.created_on)) + 1
    ) * interval '1 year'
  ) AS next_anniversary,
  date(
    created_on + (
      extract(YEAR FROM age(now(), b.created_on)) + 1
    ) * interval '1 year'
  ) - date(now()) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id

如您所见，必须定义 age_in_years 三次，因为在计算 next_anniversary 和 days_to_next_anniversary 值时需要它。

而且，这正是 LATERAL JOIN 可以帮助我们的地方。

使用 SQL LATERAL JOIN 获取报告

以下关系数据库系统支持 LATERAL JOIN 语法：

• 自 12c 以来的甲骨文
• 从 9.3 开始的 PostgreSQL
• MySQL 自 8.0.14 起

SQL Server 可以模拟 LATERAL JOIN 使用 CROSS APPLY 和 OUTER APPLY 。

LATERAL JOIN 允许我们重用 age_in_years 值并在计算 next_anniversary 和 days_to_next_anniversary 值时进一步传递它。

可以重写前面的查询以使用 LATERAL JOIN，如下所示：

 SELECT
  b.id as blog_id,
  age_in_years,
  date(
    created_on + (age_in_years + 1) * interval '1 year'
  ) AS next_anniversary,
  date(
    created_on + (age_in_years + 1) * interval '1 year'
  ) - date(now()) AS days_to_next_anniversary
FROM blog b
CROSS JOIN LATERAL (
  SELECT
    cast(
      extract(YEAR FROM age(now(), b.created_on)) AS int
    ) AS age_in_years
) AS t
ORDER BY blog_id

并且，可以计算出 age_in_years 值并重新用于 next_anniversary 和 days_to_next_anniversary 计算：

blog_id年龄_in_years next_anniversary days_to_next_anniversary 1 7 2021-09-30 295 2 3 2021-01-22 44

好多了，对吧？

为 age_in_years blog 表的每条记录计算 —。因此，它的工作方式类似于关联子查询，但子查询记录与主表连接，因此，我们可以引用子查询生成的列。

原文由 Vlad Mihalcea 发布，翻译遵循 CC BY-SA 4.0 许可协议