10

If I have an expression like 

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

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

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.

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

wrschneider
  • 17,913
  • 16
  • 96
  • 176
  • 1
    *why does it not act like a Cartesian join?* Because, this is actually an implicit [LATERAL](http://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL) [JOIN](https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#LATERAL_JOIN) (which always happens, if you use functions in the `FROM` clause) – pozs Nov 04 '15 at 10:56

1 Answers1

9

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;

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:

When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

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

klin
  • 112,967
  • 15
  • 204
  • 232
  • Sorry I should have been more precise. I know `from a, b` is a join - my real question is why this does not act like a cartesian/cross join. May be some magic in how the table function works. – wrschneider Nov 04 '15 at 03:33
  • also useful "lateral subqueries" http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html" – wrschneider Nov 04 '15 at 14:51
  • related SO question/answer: http://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-and-a-subquery-in-postgresql – wrschneider Nov 04 '15 at 14:51