LATERAL Joins: The Hidden Feature

The Problem: Top-N Per Group

I needed the top 3 orders for every customer. A correlated subquery was messy. `LATERAL` was the elegant solution.

The Syntax (PostgreSQL)

SELECT c.name, top_orders.*

FROM customers c

CROSS JOIN LATERAL (

SELECT order_id, amount

FROM orders o

WHERE o.customer_id = c.id

ORDER BY amount DESC

LIMIT 3

) AS top_orders;

How It Works

The `LATERAL` subquery can reference columns from the preceding table (like a correlated subquery), but it's treated as a table join.

Pro Tip

This is the efficient way to solve "Top N per Group" problems without Window Functions.

*Day 59: Data Validation with Subqueries.*