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

 

由于Postgres具有执行LATERAL连接的能力，因此我一直在阅读它，因为我目前正在为团队进行复杂的数据转储，导致大量低效的子查询使整个查询花费四分钟或更长时间。

我知道LATERAL加入可以帮助我，但即使在阅读Heap Analytics这样的文章之后，我仍然不太理解。

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

更像是一个相关的子查询

一个LATERAL连接（Postgres 9.3+）更像是一个相关的子查询，而不是一个简单的子查询。 像@Andomar指出的那样， LATERAL连接权限的函数或子查询通常需要多次评估 - 对于LATERAL连接的每一行，就像一个相关的子查询一次 - 而普通的子查询（表达式）是只评估一次。 （尽管如此，查询规划器可以为两者优化性能。）
这个相关的答案有并排的代码示例，解决了同样的问题：

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

    • 为了返回多个列， LATERAL连接通常更简单，更清洁和更快。 此外，请记住，相关子查询的等价物是LEFT JOIN LATERAL ... ON true ：

  • 使用数组参数多次调用set-returning函数

    • 阅读关于LATERAL的手册

    它比我们将要在这里得到答案的任何东西都更具权威性：

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

    • 子查询无法做到的事情

    有一些LATERAL连接可以做的事情，但（相关的）子查询不能（很容易）。 相关的子查询只能返回一个值，而不是多列而不是多行 - 除了裸函数调用（如果它们返回多行，则返回结果行）。 但是即使某些设置返回函数只能在FROM子句中使用。 就像Postgres 9.4中带有多个参数的新unnest()一样。 手册：

    这只能在FROM子句中使用;

    所以这个工作，但不能轻易地被一个子查询替换： 

    CREATE TABLE tbl (a1 int[], a2 int[]);

SELECT *
FROM   tbl t, unnest(t.a1, t.a2) u(elem1, elem2);  -- implicit LATERAL

    （ FROM子句中的逗号（ , ）是CROSS JOIN简称。
    LATERAL被自动假定为表格功能。）

    关于UNNEST( array_expression [, ... ] )这个问题的更多关于UNNEST( array_expression [, ... ] ) ：

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

    • 在SELECT列表中设置返回函数

    您也可以直接在SELECT列表中使用像unnest()这样的设置返回函数。 这用于展示令人惊讶的行为，在同一个SELECT列表中有不止一个实例，直到Postgres 9.6。 但它终于被Postgres 10消毒了，现在是一个有效的选择（即使不是标准的SQL）。
    基于上面的例子： 

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

    比较：

    dbfiddle for pg 9.6在这里
    这里是第10页的dbfiddle

    澄清错误信息

    该手册在此澄清误导性信息：

    对于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的代码示例是正确的（ CROSS JOIN不需要连接条件）并且@ Attila's是无效的。

    非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查询的一个例子，因为它与查询本身之外的行之间的关系。

    首先，横向和交叉应用是一回事。 因此，您也可以阅读有关交叉申请。 由于它已经在SQL Server中实现了很长时间，因此您可以在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

    在这个查询中，由于限制子句，你不能使用正常的连接。 当没有简单的连接条件时，可以使用横向或交叉应用。

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

    链接地址: http://www.djcxy.com/p/86211.html

    上一篇: What is the difference between LATERAL and a subquery in PostgreSQL?

    下一篇: How can I join on a stored procedure?