LATERAL JOIN & CROSS APPLY

Advanced SQL

The Power of LATERAL

LATERAL lets the right side of a JOIN reference columns from the left side — turning correlated subqueries into elegant, readable joins. It's the most powerful tool for top-N per group, dynamic lookups, and row-by-row processing.

CROSS JOIN LATERAL — Return rows from the right side per left row
LEFT JOIN LATERAL — Optional matches (returns NULL if no match)
CROSS APPLY — SQL Server equivalent of CROSS JOIN LATERAL
Geospatial & JSON — Unnest arrays and find nearest neighbors

LATERAL turns "for each department, get the top 3 earners" from an awkward subquery into a clean, optimized join.

What Is LATERAL?

LATERAL allows the right side of a join to reference columns from the left side, creating a correlated join. Without LATERAL, subqueries in the FROM clause are evaluated independently.

Execution Semantics

Given tables (left) and (right):

where is the subquery evaluated with 's columns as parameters.

This differs from a regular JOIN where is evaluated once and then joined to .

"LATERAL is to JOINs what a foreach loop is to arrays — it evaluates the right side once per left row, with access to that row's values." — PostgreSQL Documentation

LATERAL vs Regular JOIN — Visual

LATERAL JOIN Fundamentals

Top-N Per Group (The Classic Use Case)

-- Get top 3 earners per department
SELECT
  d.department_name,
  e.*
FROM departments d
CROSS JOIN LATERAL (
  SELECT employee_id, name, salary, hire_date
  FROM employees
  WHERE department_id = d.department_id  -- references d.department_id!
  ORDER BY salary DESC
  LIMIT 3
) e;

SQL Server Equivalent (CROSS APPLY)

-- SQL Server: CROSS APPLY is the equivalent of LATERAL
SELECT
  d.department_name,
  e.*
FROM departments d
CROSS APPLY (
  SELECT TOP 3
    employee_id, name, salary
  FROM employees
  WHERE department_id = d.department_id
  ORDER BY salary DESC
) e;

ℹ️

Cross-Platform: PostgreSQL uses CROSS JOIN LATERAL, SQL Server uses CROSS APPLY, MySQL 8.0+ uses CROSS JOIN LATERAL. The semantics are identical.

LEFT JOIN LATERAL — Optional Matches

-- Get most recent order per customer (including those with no orders)
SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.order_date,
  o.total_amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_id, order_date, total_amount
  FROM orders
  WHERE customer_id = c.customer_id
  ORDER BY order_date DESC
  LIMIT 1
) o ON true;  -- Required for LEFT JOIN LATERAL!

⚠️

Syntax Requirement: When using LEFT JOIN LATERAL, always include ON true or ON false. Without it, PostgreSQL throws a syntax error. ON true returns all rows; ON false returns the lateral subquery result as a single NULL-padded row.

LATERAL for JSON/Array Processing

-- Unnest JSON array per row
SELECT
  order_id,
  item.product_name,
  item.quantity,
  item.price
FROM orders
CROSS JOIN LATERAL (
  SELECT jsonb_array_elements(items) AS item_data
) raw
CROSS JOIN LATERAL (
  SELECT
    item_data->>'name' AS product_name,
    (item_data->>'quantity')::INT AS quantity,
    (item_data->>'price')::DECIMAL AS price
) item;

Multiple LATERAL Joins — Chained Lookups

-- Chain multiple lateral joins for complex lookups
SELECT
  o.order_id,
  c.customer_name,
  p.product_name,
  s.shipping_address
FROM orders o
CROSS JOIN LATERAL (
  SELECT customer_name FROM customers WHERE customer_id = o.customer_id
) c
CROSS JOIN LATERAL (
  SELECT product_name FROM products WHERE product_id = o.product_id
) p
CROSS JOIN LATERAL (
  SELECT shipping_address FROM shipments
  WHERE order_id = o.order_id
  ORDER BY shipped_date DESC LIMIT 1
) s;

LATERAL for Geospatial Queries

-- Find 5 nearest points of interest to each user
SELECT
  u.user_id,
  u.user_name,
  poi.poi_name,
  poi.distance_meters
FROM users u
CROSS JOIN LATERAL (
  SELECT
    p.name AS poi_name,
    ST_Distance(
      ST_SetSRID(ST_MakePoint(u.longitude, u.latitude), 4326)::geography,
      ST_SetSRID(ST_MakePoint(p.longitude, p.latitude), 4326)::geography
    ) AS distance_meters
  FROM points_of_interest p
  ORDER BY ST_Distance(
    ST_SetSRID(ST_MakePoint(u.longitude, u.latitude), 4326)::geography,
    ST_SetSRID(ST_MakePoint(p.longitude, p.latitude), 4326)::geography
  )
  LIMIT 5
) poi;

ℹ️

Optimization Tip: For geospatial LATERAL joins, create a GiST index on the geography column to avoid sequential scans. The index dramatically improves performance for distance calculations.

Performance: LATERAL vs Alternatives

Execution Strategy Comparison

Method	How It Works	Best For
LATERAL JOIN	Scans right table per left row	Top-N, nearest neighbor
Correlated Subquery	Similar to LATERAL but in SELECT	Single scalar values
Window Function	Full table scan + partition	All rows per group
CTE + LATERAL	Pre-filter left side	Large left tables

Rule of thumb: Use LATERAL when you need LIMIT on the right side per left row. Use window functions when you need all rows per group.

Quiz: Test Your Knowledge

Follow-Up Questions

When would you use LATERAL JOIN instead of a correlated subquery?
How does LATERAL JOIN interact with query optimizers differently than regular JOINs?
What's the difference between CROSS JOIN LATERAL and LEFT JOIN LATERAL?
How would you optimize a LATERAL join that's processing millions of rows?
Can you use LATERAL JOIN with window functions? What are the limitations?

Key Takeaways

LATERAL = correlated join — right side can reference left side columns
CROSS JOIN LATERAL returns only matched rows; LEFT JOIN LATERAL includes unmatched left rows
Always include ON true with LEFT JOIN LATERAL (PostgreSQL syntax requirement)
Best for Top-N per group — use LIMIT in the lateral subquery for efficient scanning
Geospatial queries — LATERAL + ORDER BY distance + LIMIT = nearest neighbor
Performance tip — add GiST indexes for geospatial LATERAL joins

LATERAL JOIN & CROSS APPLY — Correlated Joins, Row-by-Row Processing