SQL LATERAL JOIN Explained

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

What LATERAL Does

In a normal FROM clause, each table or subquery is independent. A subquery in FROM cannot see columns from other items in the same FROM list. LATERAL removes that restriction. It tells the engine: “Evaluate this subquery once per row from the left side, and give it access to that row's columns.”

Architecturally, this is the same shape as a correlated subquery, relocated into the FROM pipeline so it can contribute rows instead of scalars. A SELECT-clause correlated subquery collapses to one value per row. LATERAL decouples that limit: the parameterized subquery becomes a set-returning function of its input row, and the resulting fan-out feeds directly into downstream joins and aggregates.

Without LATERAL vs. With LATERAL

-- WITHOUT LATERAL: this fails
-- The subquery cannot reference d.id
SELECT d.name, recent.total
FROM departments d
JOIN (
  SELECT department_id, SUM(amount) AS total
  FROM orders
  WHERE department_id = d.id  -- ERROR: d is not visible
  GROUP BY department_id
) recent ON true;

-- WITH LATERAL: this works
SELECT d.name, recent.total
FROM departments d
CROSS JOIN LATERAL (
  SELECT SUM(amount) AS total
  FROM orders
  WHERE department_id = d.id
    AND order_date >= CURRENT_DATE - INTERVAL '30 days'
) recent;

PostgreSQL LATERAL Syntax

PostgreSQL uses the LATERAL keyword directly in the FROM clause. You can combine it with CROSS JOIN, LEFT JOIN, or INNER JOIN. The subquery gets an alias, and the join condition (if needed) goes in the ON clause.

-- CROSS JOIN LATERAL: exclude rows where subquery returns nothing
SELECT u.name, latest.order_id, latest.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) latest;

-- LEFT JOIN LATERAL: keep all users, even those with no orders
SELECT u.name, latest.order_id, latest.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) latest ON true;

SQL Server Equivalent: CROSS APPLY / OUTER APPLY

-- SQL Server: CROSS APPLY = CROSS JOIN LATERAL
SELECT u.name, latest.order_id, latest.amount
FROM users u
CROSS APPLY (
  SELECT TOP 3 order_id, amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
) latest;

-- SQL Server: OUTER APPLY = LEFT JOIN LATERAL
SELECT u.name, latest.order_id, latest.amount
FROM users u
OUTER APPLY (
  SELECT TOP 3 order_id, amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
) latest;

Use Case: Top-N Per Group

The top-N per group problem appears in interviews constantly. “Find the 3 most recent orders per customer.” “Get the 5 highest-paid employees per department.” Window functions (ROW_NUMBER + filter) solve this, but LATERAL can be more efficient when an index exists on the sort column.

Window Function Approach

-- Window function: scans all orders, then filters
WITH ranked AS (
  SELECT
    user_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT user_id, order_id, amount
FROM ranked
WHERE rn <= 3;

LATERAL Approach

-- LATERAL: uses index to fetch only 3 rows per user
SELECT u.id, latest.order_id, latest.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) latest;

The LATERAL version can be faster because the engine runs a targeted index scan per user, fetching only 3 rows each time. The window function version scans the entire orders table first, assigns row numbers, and then filters. For large tables with a covering index on (user_id, order_date DESC), LATERAL wins significantly on execution time.

Use Case: Unnesting Arrays and JSON

When a column stores an array or JSON array, LATERAL JOIN with a set-returning function (like unnest or json_array_elements) expands each element into its own row. This pattern is essential for normalizing semi-structured data stored in PostgreSQL arrays or JSONB columns.

-- Unnest tags array: one row per product-tag combination
SELECT p.id, p.name, t.tag
FROM products p
CROSS JOIN LATERAL unnest(p.tags) AS t(tag);

-- Unnest JSONB array
SELECT
  e.id,
  e.name,
  skill.value ->> 'name' AS skill_name,
  (skill.value ->> 'years')::INT AS years
FROM employees e
CROSS JOIN LATERAL jsonb_array_elements(e.skills) AS skill(value);

In PostgreSQL, unnest() and jsonb_array_elements() implicitly use LATERAL when called in the FROM clause. But writing LATERAL explicitly makes the intent clear to readers and to interviewers.

Parameterized Table Functions

LATERAL also enables calling table-valued functions with parameters from the left side. This includes generate_series() with dynamic bounds, custom functions that return sets, and foreign data wrapper queries parameterized per row.

-- Generate a row per month for each subscription's active period
SELECT
  s.id,
  s.plan,
  month.dt AS active_month
FROM subscriptions s
CROSS JOIN LATERAL generate_series(
  s.start_date,
  COALESCE(s.end_date, CURRENT_DATE),
  INTERVAL '1 month'
) AS month(dt);

LATERAL vs. Correlated Subquery vs. Window Function

3 LATERAL JOIN Interview Questions

LATERAL shows up in senior-level interviews and in any interview that asks top-N per group. Knowing the pattern gives you an alternative when the window function solution feels clunky.

LATERAL JOIN FAQ

Related Guides