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

Question

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

sqlpostgresqljoinsubquerylateral-join

301

自从PostgreSQL出现了执行LATERAL连接的能力，为了给我的团队生成大量数据转储，我一直在阅读这方面的内容。由于使用了许多低效子查询，导致整个查询需要四分钟或更长时间。

我知道LATERAL连接也许可以帮助我，但即使阅读像Heap Analytics的这篇文章这样的文章之后，我还是不太理解。

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

- jdotjdot

3

请翻译以下关于编程的内容，从英语翻译成中文。只返回翻译后的文本内容：http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ 和 http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ （SQL Server的“apply”与SQL标准中的“lateral”相同） - user330315

LATERAL 关键字属于其后面的“派生表”（子查询），即它不是一种 JOIN 类型。 - jarlh

5个回答

135

非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，内部查询不会以任何方式依赖于外部查询。一种称为 correlated 查询的示例是 lateral 查询，因为它与查询本身之外的行存在关系。

- Andomar

4

select * from table1 left join t2 using (col1) 与使用 on 条件的连接有何不同？我不确定在何种情况下使用 lateral 更为合适。 - No_name

1

简洁明了。易于阅读。谢谢！ - vhs

47

数据库表

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

目前，我们有两个托管的博客：

id	created_on	title	url
1	2013-09-30	Vlad Mihalcea's Blog	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需要定义三次，因为在计算next_anniversary和days_to_next_anniversary值时需要用到它。

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

使用SQL LATERAL JOIN获取报告

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

Oracle 12c及以上版本
PostgreSQL 9.3及以上版本
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：

博客ID	年龄（岁）	下一周年纪念日日期	距离下一周年纪念日天数
1	7	2021-09-30	295
2	3	2021-01-22	44

是不是好多了？

age_in_years是为blog表的每个记录计算的。因此，它就像一个相关子查询，但是子查询记录与主表连接，并且因此我们可以引用由子查询生成的列。

- Vlad Mihalcea

很棒的例子。但这如何解释使用LATERAL呢？ - andy

2

谢谢，但我认为你错过了“age_in_years是针对blog表的每条记录计算的。因此，它的工作方式类似于相关子查询，但子查询记录与主表连接，并且出于这个原因，我们可以引用子查询生成的列”的部分，强调了使用LATERAL JOIN的重要性。 - Vlad Mihalcea

1

啊，我又做了一些研究，现在我想我明白了。不能使用CROSS JOIN，因为你不能使用关联（即引用源表），这就是LATERAL关键字的作用。 - andy

20

没有人指出的一件事是，您可以使用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中知道的唯一方法。

- Theodore R. Smith

19

首先，Lateral和Cross Apply是一回事。因此，您也可以阅读关于Cross Apply的内容。由于它在SQL Server中已经实现了很长时间，因此您将找到比Lateral更多的信息。

其次，根据我的理解，使用子查询而不是Lateral使用时没有什么你做不到的。但是：

考虑以下查询。

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

由于limit子句的限制，在此查询中无法使用普通连接。可以使用Lateral或Cross Apply（当没有简单的连接条件时）。

Lateral或Cross Apply还有更多用途，但这是我发现的最常见的用法。

- Atilla Ozgur

1

确切地说，我想知道为什么PostgreSQL使用lateral而不是apply。也许微软已经对这种语法进行了专利申请？ - Andomar

18

据我所知，“lateral”是SQL标准中的术语，但“apply”不是。 - mu is too short

2

LEFT JOIN需要一个连接条件。除非你想进行限制，否则将其设置为ON TRUE。 - Erwin Brandstetter

2

@Andomar：受到这些错误信息的激励，我添加了另一个答案来进行澄清。 - Erwin Brandstetter

1

更新后的查询再次不正确。您不能在X.Fk1上进行连接，因为它不在子查询的结果中。如果您在子查询中包含Fk1，则它将在形式上是正确的，但仍会表现出奇异的行为。它从B中选择任意一行，并仅在Fk1恰好匹配A.PK时将其连接到A。 - Erwin Brandstetter

显示剩余4条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

什么是LATERAL连接？

这个功能是在PostgreSQL 9.3版本中引入的。手册：

在FROM子句中出现的子查询可以在前面加上关键字LATERAL。这使得它们可以引用前面FROM项提供的列。（没有LATERAL，每个子查询都是独立评估的，因此不能相互引用任何其他FROM项。）

在FROM子句中出现的表函数也可以在前面加上关键字LATERAL，但对于函数来说，关键字是可选的；函数的参数可以包含对前面FROM项提供的列的引用。

手册中提供了基本的代码示例。

更像是相关子查询

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

相关问题的答案中有并排的代码示例，解决了同一个问题：

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

对于返回多个列，LATERAL连接通常更简单、更清晰、更快速。此外，请记住，相关子查询的等效形式是LEFT JOIN LATERAL ... ON true。

使用数组参数多次调用一个返回集合的函数

子查询无法做到的事情

有些事情一个（相关）子查询无法轻松做到，但是一个LATERAL连接可以。一个相关子查询只能返回单个值，而不能返回多列或多行 - 除非是裸露的函数调用（如果函数返回多行，则会产生多个结果行）。但是，即使是某些返回集合的函数也只允许在FROM子句中使用，比如在Postgres 9.4或更高版本中带有多个参数的unnest()函数。参考手册：

这只允许在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 [, ... ] )的特殊情况：

如何声明只允许在FROM子句中使用返回集的函数？

`SELECT`列表中的返回集函数

您也可以直接在SELECT列表中使用unnest()等返回集函数。在Postgres 9.6之前，当在同一个SELECT列表中使用多个这样的函数时，可能会出现令人惊讶的行为。但在Postgres 10中已经得到了改进，现在是一个有效的替代方法（即使不是标准SQL）。请参阅：

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

在上面的示例基础上构建：

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

比较：

pg 9.6的示例
 pg 10的示例

需要注意：在SELECT列表中使用返回集合的函数（组合）产生没有行的情况会消除该行。在内部，它被翻译为CROSS JOIN LATERAL ROWS FROM ...，而不是LEFT JOIN LATERAL ... ON true！

演示了pg 16的示例中的差异。

澄清错误信息

手册：

对于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 <b>ON TRUE</b>;

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的是不正确的。

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

什么是LATERAL连接？

更像是相关子查询

子查询无法做到的事情

SELECT列表中的返回集函数

澄清错误信息

数据库表

不使用 SQL LATERAL JOIN 获取报告

`SELECT`列表中的返回集函数