SQL LATERAL JOIN: Correlated Subqueries in FROM Explained

-- 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;

Interview note: If an interviewer asks what LATERAL means, start with this: 'It lets a subquery in FROM see columns from the tables listed before it. It runs once per row from the left side.' That is the complete mental model in two sentences.

-- 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: 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;

-- 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: 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;

Interview note: If you solve top-N with ROW_NUMBER, mention LATERAL as an alternative and explain the performance tradeoff. This shows depth. If you only know one approach, go with ROW_NUMBER since it works on all engines.

-- 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);

-- 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);