What is a LATERAL
join?
该功能是在 PostgreSQL 9.3 中引入的。手册 https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL:
子查询出现在FROM
前面可以有关键字LATERAL
。这允许他们引用前面提供的列FROM
项目。 (没有LATERAL
,评估每个子查询
独立,因此不能交叉引用任何其他FROM
item.)
表函数出现在FROM
也可以在键之前
单词LATERAL
,但对于函数来说,关键字是可选的;这
函数的参数可以包含对以下提供的列的引用
前FROM
任何情况下的物品。
那里给出了基本的代码示例。
更像是一个相关的子查询
A LATERAL
join 更像是相关子查询 https://en.wikipedia.org/wiki/Correlated_subquery,不是一个普通的子查询,在 a 右侧的表达式中LATERAL
join 对其左侧的每一行进行一次评估 - 就像相关的子查询 - 计算普通子查询(表表达式)时once仅有的。 (不过,查询规划器有办法优化两者的性能。)
相关答案与并排的代码示例,解决相同的问题:
- 优化 GROUP BY 查询以检索每个用户的最新行 https://stackoverflow.com/questions/25536422/optimize-group-by-query-to-retrieve-latest-record-per-user/25536748#25536748
对于返回超过一列, a LATERAL
join 通常更简单、更干净、更快。
另外,请记住,相关子查询的等效项是LEFT JOIN LATERAL ... ON true
:
- 使用数组参数多次调用设置返回函数 https://stackoverflow.com/questions/26107915/call-a-set-returning-function-with-an-array-argument-multiple-times/26514968#26514968
子查询不能做的事情
There are的事情LATERAL
join 可以,但(相关的)子查询不能(轻松地)。相关子查询只能返回单个值,不能返回多列,也不能返回多行 - 裸函数调用除外(如果返回多行,则将结果行相乘)。但即使某些返回集合的函数也只允许在FROM
条款。喜欢unnest()
Postgres 9.4 或更高版本中具有多个参数。手册: https://www.postgresql.org/docs/current/functions-array.html#ARRAY-FUNCTIONS-TABLE
这只允许在FROM
clause;
所以这可行,但不能(轻易)用子查询替换:
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 子句中? https://dba.stackexchange.com/a/160310/3684
中的集合返回函数SELECT
list
您还可以使用设置返回函数,例如unnest()
in the SELECT
直接列出来。这曾经在同一个函数中表现出令人惊讶的行为,并且有多个这样的函数SELECT
列出 Postgres 9.6 之前的版本。但它最终被 Postgres 10 清理掉了 https://www.postgresql.org/docs/10/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET现在是一个有效的替代方案(即使不是标准 SQL)。看:
- SELECT 子句中多个返回集合的函数的预期行为是什么? https://stackoverflow.com/questions/39863505/what-is-the-expected-behaviour-for-multiple-set-returning-functions-in-select-cl/39864815#39864815
基于上面的例子:
SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2
FROM tbl;
比较:
9.6 页的小提琴 https://dbfiddle.uk/asUCZoT5
第 10 页的小提琴 https://dbfiddle.uk/C42oryLC
To note:中的集合返回函数(的组合)SELECT
产生的列表no rows消除该行。在内部它翻译为CROSS JOIN LATERAL ROWS FROM ...
,不至于LEFT JOIN LATERAL ... ON true
!
第 16 页的小提琴 https://dbfiddle.uk/AeiIKq95展示差异。
澄清错误信息
手册: https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM
For the INNER
and OUTER
连接类型,连接条件必须是
指定,即恰好之一NATURAL
, ON
加入条件,
or USING
(加入列[,...])。含义见下文。
For 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;
罢工>
这就是为什么安多玛尔的 https://stackoverflow.com/a/28551339/939860代码示例是正确的(CROSS JOIN
不需要连接条件)并且Attila's https://stackoverflow.com/a/28550962/939860是不是。