FROM子句中的PostgreSQL json_array_elements-为什么这不是笛卡尔联接?

问题描述

如果我有类似

SELECT t.json_column->>'x',
   nested->>'y'
FROM my_table t,
   json_array_elements(t->'nested') nested

为什么我不需要加入?更确切地说，为什么它不像笛卡尔CROSS JOIN那样起作用?

Why don't I need a JOIN? More precisely, why does this not act like a Cartesian CROSS JOIN?

通过引用json_array_elements调用中的表别名t，看起来隐式地发生了联接.但是我不熟悉带有表函数的隐式联接语法.

It looks like a join happens implicitly by referencing the table alias t in the json_array_elements call. But the implicit-join syntax with a table function is unfamiliar to me.

在PostgreSQL或其他数据库中是否还有其他类似SQL语法的示例?

Are there other examples of similar SQL syntax in PostgreSQL or other databases?

推荐答案

实际上，这是CROSS JOIN的老式语法.形式对等:

In fact this is old-fashioned syntax for CROSS JOIN. Formal equivalent:

SELECT
    t.json_column->>'x',
    nested->>'y'
FROM 
    my_table t
CROSS JOIN
    json_array_elements(t.json_column->'nested') nested;

该查询不会产生笛卡尔积，而是像内部联接一样起作用.这是因为它在连接的两个部分之间具有一个 hidden 引用，在本例中为别名t.这种连接称为LATERAL JOIN.对于文档:

The query does not produce cartesian product but acts rather like an inner join. This is because it has a hidden reference between two parts of join, in this case alias t. This kind of join is known as LATERAL JOIN. For the documentation:

如果联接的一部分是函数，则默认情况下会将其视为横向函数.

If one of parts of a join is a function it is treated as lateral by default.

这篇关于FROM子句中的PostgreSQL json_array_elements-为什么这不是笛卡尔联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！