I am using a construct like this very often:
SELECT *
FROM
my_table,
my_srf(my_column)
for example here:
CREATE TABLE my_table (
my_col text[]
);
INSERT INTO my_table VALUES
('{a,b}'),
('{c,d,e}');
SELECT
*
FROM
my_table,
unnest(my_col);
The result is as expected:
my_col unnest
{a,b} a
{a,b} b
{c,d,e} c
{c,d,e} d
{c,d,e} e
Now I thought about this construct a little bit and got confused. The comma notation is the shortcut for as CROSS JOIN
. In fact, this works as well:
SELECT
*
FROM
my_table
CROSS JOIN
unnest(my_col)
But because this is a cross join, this result would look more logical to me:
my_col unnest
{a,b} a
{a,b} b
{a,b} c
{a,b} d
{a,b} e
{c,d,e} a
{c,d,e} b
{c,d,e} c
{c,d,e} d
{c,d,e} e
A cross join joins all records from one part with all records of the other.
Although I am using this SRF joins as described above, I am now not sure anymore why it works in detail. So, I think that I obviously did not understand the complete functionality of SRFs so far.
Because the documentation made it not clearer to me, I would like to ask you to explain the behaviour.
Best Answer
The piece that you are missing is, that this is not a regular cross join, but a "LATERAL" cross join.
So the fully written equivalent is:
The keyword LATERAL is implicitly assumed if the join is done against a set returning function that's why
cross join unnest(my_col)
or evenfrom my_table, unnest(my_col)
works as well.The
LATERAL
will change the cross join such that it takes one row frommy_table
and does a cross join of that (one) row with all rows returned from the set returning function. Then it does the same with the next row and so on.So it generates the cross join between the row containing array
{a,b}
and all its elements. And then another cross join with the row containing the array{c,d,e}
and all its elements.The manual explains it this way: