LATERAL Join — Per-Row Subquery

-- 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;