What is LATERAL join and when would you use it?

LATERAL allows subquery in FROM clause to reference columns from preceding tables. Executes subquery for each outer row. Use for top-N per group, row-by-row calculations, or when you need correlated operations in FROM clause. PostgreSQL uses LATERAL, SQL Server uses CROSS APPLY.

Get the 3 most expensive products for each category using LATERAL.

SELECT c.category_name, p.product_name, p.price

FROM categories c,

LATERAL (

SELECT product_name, price

FROM products

WHERE category_id = c.category_id

ORDER BY price DESC

LIMIT 3

) p;

LATERAL subquery references category_id from outer query, executing once per category.