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

What is the difference between LATERAL and a subquery in PostgreSQL?

提问by jdotjdot

Since Postgres came out with the ability to do LATERALjoins, I've been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more.

自从 Postgres 具有执行LATERAL连接的能力以来，我一直在阅读它，因为我目前为我的团队执行复杂的数据转储，其中包含许多低效的子查询，这使得整个查询需要四分钟或更长时间。

I understand that LATERALjoins may be able to help me, but even after reading articles like this onefrom Heap Analytics, I still don't quite follow.

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

What is the use case for a LATERALjoin? What is the difference between a LATERALjoin and a subquery?

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

回答by Erwin Brandstetter

More like a correlatedsubquery

更像是相关子查询

A LATERALjoin (Postgres 9.3 or later) is more like a correlated subquery, not a plain subquery. Like Andomar pointed out, a function or subquery to the right of a LATERALjoin has to be evaluated once for each row left of it - just like a correlatedsubquery - while a plain subquery (table expression) is evaluated onceonly. (The query planner has ways to optimize performance for either, though.)
This related answer has code examples for both side by side, solving the same problem:

甲LATERAL加入（Postgres的9.3或更高版本）更像是一个相关子查询，而不是一个简单的子查询。就像Andomar 指出的那样，LATERAL连接右侧的函数或子查询必须为其左侧的每一行评估一次 - 就像相关子查询一样 - 而普通子查询（表表达式）仅评估一次。（不过，查询规划器有办法优化两者的性能。）
这个相关的答案有两个并排的代码示例，解决了同样的问题：

• Optimize GROUP BY query to retrieve latest row per user

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

For returning more than one column, a LATERALjoin is typically simpler, cleaner and faster.
Also, remember that the equivalent of a correlated subquery is LEFT JOIN LATERAL ... ON true:

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

• Call a set-returning function with an array argument multiple times

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

Read the manual on LATERAL

阅读手册 LATERAL

It is more authoritative than anything we are going to put into answers here:

它比我们要在这里给出的任何答案都更具权威性：

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

Things a subquery can't do

子查询不能做的事情

There arethings that a LATERALjoin can do, but a (correlated) subquery cannot (easily). A correlated subquery can only return a single value, not multiple columns and not multiple rows - with the exception of bare function calls (which multiply result rows if they return multiple rows). But even certain set?returning functions are only allowed in the FROMclause. Like unnest()with multiple parameters in Postgres 9.4 or later. The manual:

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

This is only allowed in the FROMclause;

这仅在FROM子句中允许；

So this works, but cannot easily be replaced with a subquery:

所以这有效，但不能轻易用子查询替换：

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

The comma (,) in the FROMclause is short notation for CROSS JOIN.
LATERALis assumed automatically for table functions.
More about the special case of UNNEST( array_expression [, ... ] ):

子句中的逗号 ( ,)FROM是 的缩写CROSS JOIN。
LATERAL为表函数自动假定。
有关以下特殊情况的更多信息UNNEST( array_expression [, ... ] )：

• How do you declare a set-returning-function to only be allowed in the FROM clause?

• 你如何声明一个只允许在 FROM 子句中的集合返回函数？

Set-returning functions in the SELECTlist

SELECT列表中的设置返回函数

You can also use set-returning functions like unnest()in the SELECTlist directly. This used to exhibit surprising behavior with more than one such function in the same SELECTlist up to Postgres 9.6. But it has finally been sanitized with Postgres 10and is a valid alternative now (even if not standard SQL). See:

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

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

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

Building on above example:

基于上面的例子：

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

Comparison:

比较：

dbfiddle for pg 9.6 here
dbfiddle for pg 10 here

dbfiddle为PG 9.6这里
dbfiddle为第10页这里

Clarify misinformation

澄清错误信息

The manual:

手册：

For the INNERand OUTERjoin types, a join condition must be specified, namely exactly one of NATURAL, ONjoin_condition, or USING(join_column[, ...]). See below for the meaning.
For CROSS JOIN, none of these clauses can appear.

对于INNER和OUTER连接类型，必须指定连接条件，即恰好是NATURAL、ONjoin_condition或USING( join_column[, ...]) 之一。其含义见下文。
对于CROSS JOIN，这些子句都不能出现。

So these two queries are valid (even if not particularly useful):

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

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;

While this one is not:

虽然这不是：

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

That's why @Andomar'scode example is correct (the CROSS JOINdoes not require a join condition) and @Attila'siswas invalid.

这就是为什么@ Andomar的代码示例是正确的（在CROSS JOIN不需要连接条件），并@阿提拉就是无效。

回答by Andomar

The difference between a non-lateraland a lateraljoin lies in whether you can look to the left hand table's row. For example:

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

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

This "outward looking" means that the subquery has to be evaluated more than once. After all, t1.col1can assume many values.

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

By contrast, the subquery after a non-lateraljoin can be evaluated once:

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

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

As is required without lateral, the inner query does not depend in any way on the outer query. A lateralquery is an example of a correlatedquery, because of its relation with rows outside the query itself.

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

回答by Atilla Ozgur

First, Lateral and Cross Apply is same thing. Therefore you may also read about Cross Apply. Since it was implemented in SQL Server for ages, you will find more information about it then Lateral.

首先，横向和交叉应用是一回事。因此，您还可以阅读交叉申请。由于它在 SQL Server 中实现了很长时间，因此您将找到更多关于它的信息，然后是横向。

Second, according to my understanding, there is nothing you can not do using subquery instead of using lateral. But:

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

Consider following query.

考虑以下查询。

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 

You can use lateral in this condition.

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

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

In this query you can not use normal join, due to limit clause. Lateral or Cross Apply can be used when there is not simple join condition.

在此查询中，由于限制子句，您不能使用普通连接。当没有简单的连接条件时，可以使用横向或交叉应用。

There are more usages for lateral or cross apply but this is most common one I found.

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

回答by Theodore R. Smith

One thing no one has pointed out is that you can use LATERALqueries to apply a user-defined function on every selected row.

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

For instance:

例如：

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);

That's the only way I know how to do this sort of thing in PostgreSQL.

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