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

什么是横向连接？

该功能是在 PostgreSQL 9.3 中引入的。 The manual：

出现在 FROM 中的子查询前面可以有关键字 LATERAL。这允许他们引用前面的 FROM 项提供的列。 （没有 LATERAL，每个子查询都是独立评估的，因此不能交叉引用任何其他 FROM 项。）出现在 FROM 中的表函数也可以以关键字 LATERAL 开头，但对于函数，关键字是可选的；在任何情况下，函数的参数都可以包含对前面 FROM 项提供的列的引用。

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

更像是一个相关的子查询

LATERAL 连接更像是 correlated subquery，而不是普通的子查询，因为 LATERAL 连接右侧的表达式对其左侧的每一行都进行一次评估 - 就像 correlated 子查询 - 一个普通的子查询（表表达式）只被评估一次。 （不过，查询规划器有办法优化两者的性能。）
相关答案与并排的代码示例，解决了相同的问题：

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

对于返回多列，LATERAL 连接通常更简单、更简洁和更快。
另外，请记住，相关子查询的等价物是 LEFT JOIN LATERAL ... ON true:

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

子查询不能做的事情

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

这仅在 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() 等集合返回函数。在 Postgres 9.6 之前的同一 SELECT 列表中，这曾经表现出令人惊讶的行为，其中包含多个此类函数。 But it has finally been sanitized with Postgres 10 现在是一个有效的替代方案（即使不是标准 SQL）。看：

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

基于上面的例子：

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

比较：

第 9.6 页的 dbfiddle here
第 10 页的 dbfiddle here

澄清错误信息

The manual:

对于 INNER 和 OUTER 连接类型，必须指定连接条件，即 NATURAL、ON join_condition 或 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's 代码示例是正确的（CROSS JOIN 不需要连接条件）而 Attila's is 不正确。

---

非 lateral 和 lateral 连接之间的区别在于您是否可以查看左侧表格的行。例如：

select  *
from    table1 t1
cross join lateral
        (
        select  *
        from    t2
        where   t1.col1 = t2.col1 -- Only allowed because of lateral
        ) sub

这种“外向型”意味着必须多次评估子查询。毕竟，t1.col1 可以假设许多值。

相比之下，非 lateral 连接后的子查询可以计算一次：

select  *
from    table1 t1
cross join
        (
        select  *
        from    t2
        where   t2.col1 = 42 -- No reference to outer query
        ) sub

正如没有 lateral 所要求的那样，内部查询不以任何方式依赖于外部查询。 lateral 查询是 correlated 查询的一个示例，因为它与查询本身之外的行有关系。

---

数据库表

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

https://i.stack.imgur.com/5MZLr.png

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

id created_on 标题 url 1 2013-09-30 Vlad Mihalcea 的博客 https://vladmihalcea.com 2 2017-01-22 Hypersistence 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 必须定义 3 次，因为在计算 next_anniversary 和 days_to_next_anniversary 值时需要它。

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

使用 SQL LATERAL JOIN 获取报告

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

自 12c 以来的甲骨文

从 9.3 开始的 PostgreSQL

MySQL 自 8.0.14 起

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

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 age_in_years next_anniversary days_to_next_anniversary 1 7 2021-09-30 295 2 3 2021-01-22 44

好多了，对吧？

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

---

首先，Lateral and Cross Apply is same thing。因此，您还可以阅读有关 Cross Apply 的信息。由于它在 SQL Server 中实现了很长时间，因此您将找到有关它的更多信息，然后是横向。

其次，根据我的理解，使用子查询代替横向查询没有什么不能做的。但：

考虑以下查询。

Select A.*
, (Select B.Column1 from B where B.Fk1 = A.PK and Limit 1)
, (Select B.Column2 from B where B.Fk1 = A.PK and Limit 1)
FROM A 

在这种情况下，您可以使用横向。

Select A.*
, x.Column1
, x.Column2
FROM A LEFT JOIN LATERAL (
  Select B.Column1,B.Column2,B.Fk1 from B  Limit 1
) x ON X.Fk1 = A.PK

在此查询中，由于限制子句，您不能使用普通连接。可以使用横向或交叉应用when there is not simple join condition。

横向或交叉应用有更多用法，但这是我发现的最常见的一种。

---

没有人指出的一件事是，您可以使用 LATERAL 查询在每个选定的行上应用用户定义的函数。

例如：

CREATE OR REPLACE FUNCTION delete_company(companyId varchar(255))
RETURNS void AS $$
    BEGIN
        DELETE FROM company_settings WHERE "company_id"=company_id;
        DELETE FROM users WHERE "company_id"=companyId;
        DELETE FROM companies WHERE id=companyId;
    END; 
$$ LANGUAGE plpgsql;

SELECT * FROM (
    SELECT id, name, created_at FROM companies WHERE created_at < '2018-01-01'
) c, LATERAL delete_company(c.id);

这是我知道如何在 PostgreSQL 中做这种事情的唯一方法。

---