</>
sqladvanced

LATERAL Join for Correlated Subqueries

Use LATERAL join to reference columns from preceding tables in subqueries.

sql
-- Top 3 orders per customer using LATERAL
SELECT c.customer_id, c.name, recent.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_id, total, order_date
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.order_date DESC
  LIMIT 3
) AS recent;

-- Running calculations with LATERAL
SELECT d.date, d.revenue, calc.running_avg
FROM daily_revenue d
CROSS JOIN LATERAL (
  SELECT AVG(r.revenue) AS running_avg
  FROM daily_revenue r
  WHERE r.date BETWEEN d.date - INTERVAL '7 days' AND d.date
) AS calc
ORDER BY d.date;

Use Cases

  • Top-N per group queries
  • Running calculations
  • Correlated aggregations

Tags

#sql#lateral#join#correlated-subquery

Related Snippets

Similar patterns you can reuse in the same workflow.

sqladvanced

SQL LATERAL Join Examples

Use LATERAL joins to run correlated subqueries for top-N per group and row-dependent lookups.

Best for: Top-N per group queries efficiently

#sql#lateral
sqlbeginner

Join - Technique 19

Advanced join techniques

Best for: database operations

#sql#database
sqladvanced

Recursive CTE for Hierarchical Data

Query hierarchical data like org charts, categories, and file trees using recursive CTEs.

Best for: Querying organizational hierarchies

#sql#cte
sqlintermediate

SQL Pivot and Crosstab Queries

Transform row data into columnar reports using CASE expressions, FILTER, and crosstab patterns.

Best for: Creating monthly revenue reports

#sql#pivot