LATERAL join

Join where the right-hand subquery can reference columns from earlier FROM-clause items, evaluated once per outer row.

A LATERAL subquery in the FROM clause may reference columns from preceding tables in the same FROM. Without LATERAL, a subquery in FROM is evaluated independently and doesn't know about the rows next to it.

SELECT u.id, recent.title
  FROM users u
  LEFT JOIN LATERAL (
    SELECT title FROM posts p
     WHERE p.user_id = u.id
     ORDER BY p.created_at DESC
     LIMIT 1
  ) AS recent ON true;

This is the cleanest way to express "for each row in A, fetch the top-N rows from B" — a problem that is awkward in plain SQL and slow with window functions on large tables. Internally, PostgreSQL plans it as a Nested Loop where the inner side is the lateral subquery.

LATERAL also makes set-returning functions composable: SELECT u.id, t.* FROM users u, LATERAL unnest(u.tags) AS t(tag).

Related terms