数据库表

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

我们现在有两个博客:

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

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

博客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允许我们重用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值可以计算为1，并重用为next_anniversary和days_to_next_anniversary计算:

好多了，对吧?

age_in_years是针对博客表的每条记录计算的。因此，它的工作方式类似于一个相关的子查询，但是子查询记录与主表连接，因此，我们可以引用子查询生成的列。

没有人指出的一点是，您可以使用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中如何做这类事情的唯一方法。

什么是横向连接?

该特性是在PostgreSQL 9.3中引入的。手册:

出现在FROM中的子查询前面可以加上关键字 侧。这允许他们引用前面提供的列 从项目。(如果没有LATERAL，每个子查询都会被求值 独立的，因此不能交叉引用任何其他FROM项。) 在FROM中出现的表函数前面也可以加上键 单词LATERAL，但对于函数，关键字是可选的;的 函数的实参可以包含对列的引用 在任何情况下，在FROM项之前。

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

更像是一个相关的子查询

LATERAL连接更像一个相关的子查询，而不是普通的子查询，因为LATERAL连接右边的表达式为它左边的每一行求值一次——就像相关的子查询一样——而普通子查询(表表达式)只求值一次。(不过，查询计划器有方法优化这两种情况的性能。) 相关的答案与代码示例为双方并排，解决相同的问题:

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

对于返回多个列，横向连接通常更简单、更干净、更快。 另外，记住相关子查询的等价是LEFT JOIN 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;

比较:

Dbfiddle for pg 9.6 这里是p10的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 * 来自tbl t (SELECT * FROM b WHERE b.t_id = t.t_id)

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

非横向连接和横向连接之间的区别在于是否可以查看左边表的行。例如:

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

这种“向外看”意味着必须对子查询求值不止一次。毕竟是t1。Col1可以假设很多值。

相比之下，非横向连接后的子查询只能求值一次:

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

在没有横向查询的情况下，内部查询不依赖于外部查询。横向查询是相关查询的一个例子，因为它与查询本身之外的行有关系。

数据库表

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

我们现在有两个博客:

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

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

博客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允许我们重用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值可以计算为1，并重用为next_anniversary和days_to_next_anniversary计算:

好多了，对吧?

age_in_years是针对博客表的每条记录计算的。因此，它的工作方式类似于一个相关的子查询，但是子查询记录与主表连接，因此，我们可以引用子查询生成的列。

首先，横向应用和交叉应用是一回事。因此你也可以读到Cross Apply。由于它在SQL Server中实现了很长时间，你会发现更多关于它的信息，然后横向。

第二，根据我的理解，用subquery代替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

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

横向应用和交叉应用还有很多用法，但我发现这是最常见的用法。