Postgres's lateral joins allow for quite the good eDSL

Published on: 2026-03-15

By Ben

Postgres's lateral joins allow for quite the good eDSL

Lateral joins are quite neat and you can build a query eDSL with them.

Postgres (and a few other databases(?)) has a lesser known or used join type known as the lateral join. They allow columns from preceding FROM clauses to be used in subqueries that are being joined.

As a (bad) example, take this pretty standard query joining two tables:

SELECT * FROM users u INNER JOIN posts p ON u . id = p . user_id

This can be rewritten as a lateral join with:

SELECT * FROM users u CROSS JOIN LATERAL ( select * from posts p where u . id = p . user_id ) p2

Notice that the join type changed to CROSS , normally this would result in a cartesian product, but the filter inside the subquery means each post is still paired up only with its user.

... continue reading