SQL

LATERAL Join — Per-Row Subquery

by @admin ADMIN

1h ago

May 31, 2026

Public

0 0 up · 0 down Sign in to vote

PostgreSQL `LATERAL` lets a join's right-hand side reference the left-hand row — like a correlated subquery, but supplying multiple columns or rows. Perfect for "top-N per group".

SQL

Raw

-- Each user + their 3 most recent orders, one row per order
SELECT u.id, u.name,
       o.id          AS order_id,
       o.amount,
       o.created_at
FROM   users u
JOIN   LATERAL (
    SELECT id, amount, created_at
    FROM   orders
    WHERE  user_id = u.id
    ORDER  BY created_at DESC
    LIMIT  3
) o ON TRUE
ORDER  BY u.id, o.created_at DESC;

-- LEFT JOIN LATERAL preserves users with no orders
SELECT u.id, u.name, o.amount
FROM   users u
LEFT   JOIN LATERAL (
    SELECT amount FROM orders
    WHERE  user_id = u.id
    ORDER  BY created_at DESC
    LIMIT 1
) o ON TRUE;

Save your own code snippets

Create a free account and build your private vault. Share publicly whenever you want.

Get Started Free Sign In