The lateral keyword allows us to access columns after the FROM statement, and reference these columns "earlier" in the query ("earlier" meaning "written higher in the query").

The difference between a non- lateral and a lateral join lies in whether you can look to the left hand table's row.

The comma ( , ) in the FROM clause is short notation for CROSS JOIN . LATERAL is assumed automatically for table functions.

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

This allows them to reference columns provided by preceding FROM items.

It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them.

The query to the right of the Lateral would be evaluated for every row of the left table.

SELECT lateral_subquery.* FROM posts JOIN LATERAL ( SELECT comments.* FROM comments WHERE (comments.post_id = posts.id) LIMIT 3 ) lateral_subquery ON true WHERE posts.id

You want the front page to show a few hundred posts along with the top three comments on each post. You’re planning on being very popular, so the front page will need to be very fast. How do you fetch that data efficiently from postgresql using Activerecord?

Making one Comment query per Post is too expensive; it’s N+1 queries (one to fetch the posts, N to fetch the comments). You could use includes to preload all the comments for all the posts, but that requires hydrating hundreds of thousands of records, even though you only need a few hundred for your front page. What you want is some kind of GROUP BY with a LIMIT on each group — but that doesn’t exist, either in Activerecord nor even in postgres. Postgres has a different solution for this problem: the LATERAL JOIN.

join = Arel::Nodes::NamedFunction.new('json_b_array_elements', [Arel::Nodes::SqlLiteral.new("subscriptions")]) .as(Arel::Nodes::NamedFunction.new('sd', [Arel::Nodes::SqlLiteral.new("subscription_data")]).to_sql) p = e.project( Arel::Nodes::SqlLiteral.new( Arel::Nodes::Grouping.new( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'id'"))).to_sql) << '::uuid' ).where( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'type'").eq( Arel::Nodes::SqlLiteral.new("'Company'") ) ).and(e[:slug].eq(event_slug))) p.join_sources << Arel::Nodes::StringJoin.new( Arel::Nodes::SqlLiteral.new('CROSS JOIN LATERAL')) << join

lateral (select pledged / fx_rate as pledged_usd) pu, lateral (select pledged_usd / backers_count as avg_pledge_usd) apu, lateral (select goal / fx_rate as goal_usd) gu, lateral (select goal_usd - pledged_usd as usd_from_goal) ufg, lateral (select (deadline - launched_at)/86400.00 as duration) dr

The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children. To exclude those, change to a [INNER] JOIN LATERAL