LATERAL Joins — Per-Row Subqueries and Top-N Without Window Functions

A LATERAL subquery can reference columns from tables to its left in the FROM clause — it re-executes for every row it is joined to. This enables patterns that are impossible or severely awkward with standard joins.

When to use LATERAL

• Top-N rows per group (without window functions)
• Per-row computation that depends on the outer row
• Calling set-returning functions with per-row arguments
• Unpacking complex transformations row-by-row

-- Top-3 most liked tweets per user — LATERAL vs ROW_NUMBER comparison

-- Method A: ROW_NUMBER (common but reads all tweets then filters)
SELECT username, content, like_count
FROM (
    SELECT u.username, t.content, t.like_count,
           ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY t.like_count DESC) AS rn
    FROM tw_users u
    JOIN tw_tweets t ON t.user_id = u.id
    WHERE u.verified = TRUE
) ranked
WHERE rn <= 3;

-- Method B: LATERAL (executes the subquery once per user — often faster for small N)
SELECT u.username, top.content, top.like_count
FROM tw_users u
CROSS JOIN LATERAL (
    SELECT t.content, t.like_count
    FROM tw_tweets t
    WHERE t.user_id = u.id          -- references outer row
    ORDER BY t.like_count DESC
    LIMIT 3
) top
WHERE u.verified = TRUE;