The primary feature of LATERAL JOIN is to enable access elements of a main query in a subquery which can be very powerful.
Several common uses of LATERAL are to:
denormalize arrays into parent child tables
aggregation across several tables
row or action generation.
Note, however, that the subquery will execute for each main query row since the values used in the subquery
will change. This might make a slower query.
SELECT <target list>
(<subquery using table.column>) as foo;
Here are three examples of using LATERAL. Obviously there are many more.:
In the Normalization example we have a table (denorm) containing ids and an array of other ids. We want to to flatten the arrays, creating