The Lateral Pivot

I spent years thinking window functions were the "final boss" of SQL. I’d reach for ROW_NUMBER() OVER (PARTITION BY...) every time I needed to grab the latest order for a customer or the top three posts in a category. But there was always this nagging annoyance: window functions are calculated *after* the WHERE clause. To filter by rank, you’re forced to wrap the whole mess in a subquery or a CTE. It works, but it feels like writing code in a straightjacket.

Then I discovered LATERAL joins. It’s the closest thing SQL has to a foreach loop, and once you see how it handles the "Top-N-per-group" problem, you’ll never want to go back to nested subqueries again.

The Problem: The "Top-N" Struggle

Let’s say you have a table of categories and a table of products. You want to list every category alongside its three most expensive products.

Using a window function, you’d do something like this:

SELECT category_name, product_name, price
FROM (
  SELECT 
    c.name AS category_name,
    p.name AS product_name,
    p.price,
    ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) as rank
  FROM categories c
  JOIN products p ON c.id = p.category_id
) ranked_products
WHERE rank <= 3;

It gets the job done. But it’s cognitively heavy. You’re essentially telling Postgres to rank *every single product in the database* before you finally discard 90% of them in the outer wrapper. If you have millions of rows, that’s a lot of wasted CPU cycles.

Enter the LATERAL Join

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding items in the FROM list. In plain English: it lets you run a subquery for every single row of the parent table.

Here is that same "Top 3" query using LATERAL:

SELECT c.name AS category_name, p.name AS product_name, p.price
FROM categories c
CROSS JOIN LATERAL (
  SELECT name, price
  FROM products
  WHERE category_id = c.id
  ORDER BY price DESC
  LIMIT 3
) p;

See what happened there? Inside the subquery, we’re referencing c.id from the outer categories table. Postgres treats this like a loop:
1. Pick a category.
2. Run the subquery to find its top 3 products.
3. Move to the next category.

The intent is crystal clear. You aren't "ranking and filtering"; you are "fetching specifically what you need."

Why This Actually Matters

Aside from being more readable, LATERAL is often a performance powerhouse.

When you use the window function approach, the database usually has to perform a full scan (or a large index scan) of the products table to calculate those ranks. With LATERAL, if you have an index on (category_id, price DESC), Postgres can perform a high-speed index seek for each category.

It’s the difference between scanning a whole library to find the best books in each genre versus just walking directly to the "History" shelf and grabbing the top three.

Handling the "Empty Category" Edge Case

In the example above, I used CROSS JOIN LATERAL. In SQL-speak, CROSS JOIN acts like an INNER JOIN. If a category has zero products, that category will vanish from your results entirely.

If you want to keep the category in the list even if it has no products (an LEFT JOIN behavior), you just change the keyword:

SELECT c.name AS category_name, p.name AS product_name, p.price
FROM categories c
LEFT JOIN LATERAL (
  SELECT name, price
  FROM products
  WHERE category_id = c.id
  ORDER BY price DESC
  LIMIT 3
) p ON true; -- 'ON true' is the magic handshake here

The ON true looks a bit weird, I know. Since the join logic is already handled inside the WHERE clause of the subquery, we just tell Postgres "join this to the row no matter what."

More Than Just Top-N

The "Lateral Pivot" isn't just for e-commerce rankings. It's incredibly useful for any "per-row" calculation that returns multiple columns.

Imagine you have a JSONB column containing a list of objects, and you want to expand those objects into rows while keeping them linked to the parent ID:

SELECT u.email, tags.tag_name
FROM users u
CROSS JOIN LATERAL jsonb_array_elements_text(u.settings->'tags') AS tags(tag_name);

Or maybe you need to find the "time since the last login" for every user, but that data lives in a massive audit_logs table. A LATERAL join allows you to reach into that log table, grab exactly one row (the most recent one), and pull it into your main user list without the overhead of a massive join-and-group-by.

The Catch

Is there a downside? Sometimes.

If your "outer" table (categories) has a massive number of rows (e.g., 100,000+), and your subquery isn't perfectly indexed, Postgres might end up doing 100,000 slow index lookups. In that specific scenario, a window function might actually be faster because it can process the data in a single bulk operation.

But for the vast majority of web app use cases—admin dashboards, feed generation, reporting—LATERAL is the cleaner, more intuitive, and often faster tool for the job.

Stop wrestling with subquery wrappers. Start thinking laterally.