postgresql lateral joins subqueries advanced-sql analytics

LATERAL joins and correlated subqueries

Maria Garcia Feb 2026

2 tabs

-- Basic LATERAL join
SELECT
  u.username,
  recent.order_id,
  recent.total,
  recent.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT id AS order_id, total, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent;
-- Returns up to 3 most recent orders per user

-- LEFT JOIN LATERAL (include users with no orders)
SELECT
  u.username,
  recent.order_id,
  recent.total
FROM users u
LEFT JOIN LATERAL (
  SELECT id AS order_id, total
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent ON true;

-- Compare to traditional approach (slower)
SELECT
  u.username,
  o.id AS order_id,
  o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IN (
  SELECT id
  FROM orders o2
  WHERE o2.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
);

-- Top N per group
SELECT
  c.name AS category,
  p.name AS product,
  p.price
FROM categories c
CROSS JOIN LATERAL (
  SELECT name, price
  FROM products
  WHERE category_id = c.id
  ORDER BY price DESC
  LIMIT 5
) p;

-- Multiple LATERAL joins
SELECT
  u.username,
  recent_order.total AS last_order_total,
  favorite_product.name AS favorite_product
FROM users u
LEFT JOIN LATERAL (
  SELECT total
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) recent_order ON true
LEFT JOIN LATERAL (
  SELECT p.name
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id
  WHERE o.user_id = u.id
  GROUP BY p.id, p.name
  ORDER BY COUNT(*) DESC
  LIMIT 1
) favorite_product ON true;

-- LATERAL with aggregates
SELECT
  u.username,
  stats.order_count,
  stats.total_spent,
  stats.avg_order
FROM users u
CROSS JOIN LATERAL (
  SELECT
    COUNT(*) AS order_count,
    SUM(total) AS total_spent,
    AVG(total) AS avg_order
  FROM orders
  WHERE user_id = u.id
) stats
WHERE stats.order_count > 0;

-- Set-returning function in LATERAL
SELECT
  u.username,
  day.date,
  day.order_count
FROM users u
CROSS JOIN LATERAL (
  SELECT
    DATE(created_at) AS date,
    COUNT(*) AS order_count
  FROM orders
  WHERE user_id = u.id
    AND created_at >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY DATE(created_at)
) day;

-- Generate series in LATERAL
SELECT
  p.name,
  date.day,
  COALESCE(sales.daily_sales, 0) AS sales
FROM products p
CROSS JOIN LATERAL
  generate_series(
    CURRENT_DATE - INTERVAL '30 days',
    CURRENT_DATE,
    INTERVAL '1 day'
  ) AS date(day)
LEFT JOIN LATERAL (
  SELECT SUM(oi.quantity * oi.price) AS daily_sales
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  WHERE oi.product_id = p.id
    AND DATE(o.created_at) = date.day
) sales ON true
WHERE p.id = 1
ORDER BY date.day;

-- Window function alternative comparison

-- Using LATERAL (more flexible)
SELECT
  category_id,
  product_name,
  price,
  price_rank
FROM categories c
CROSS JOIN LATERAL (
  SELECT name AS product_name, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
  FROM products
  WHERE category_id = c.id
) p
WHERE p.price_rank <= 3;

-- Using window function (more efficient if no filtering)
SELECT category_id, name, price, price_rank
FROM (
  SELECT
    category_id,
    name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
  FROM products
) ranked
WHERE price_rank <= 3;

-- Complex LATERAL: User cohort analysis
SELECT
  cohort.signup_month,
  cohort.user_count,
  retention.month_number,
  retention.active_users,
  ROUND(100.0 * retention.active_users / cohort.user_count, 2) AS retention_pct
FROM (
  SELECT
    DATE_TRUNC('month', created_at) AS signup_month,
    COUNT(*) AS user_count
  FROM users
  GROUP BY DATE_TRUNC('month', created_at)
) cohort
CROSS JOIN LATERAL (
  SELECT
    EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at), cohort.signup_month)) AS month_number,
    COUNT(DISTINCT u.id) AS active_users
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE DATE_TRUNC('month', u.created_at) = cohort.signup_month
    AND o.created_at >= cohort.signup_month
    AND o.created_at < cohort.signup_month + INTERVAL '6 months'
  GROUP BY EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at), cohort.signup_month))
) retention
ORDER BY cohort.signup_month, retention.month_number;

-- LATERAL with jsonb_array_elements
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  tags JSONB
);

INSERT INTO posts VALUES
(1, 'SQL Guide', '["database", "sql", "postgresql"]'),
(2, 'Web Dev', '["javascript", "html", "css"]');

SELECT
  p.title,
  tags.tag
FROM posts p
CROSS JOIN LATERAL jsonb_array_elements_text(p.tags) AS tags(tag)
WHERE tags.tag IN ('sql', 'database');

-- Recursive + LATERAL combination
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  oc.name,
  oc.level,
  reports.direct_reports,
  reports.total_salary
FROM org_chart oc
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) AS direct_reports,
    SUM(salary) AS total_salary
  FROM employees
  WHERE manager_id = oc.id
) reports ON true
ORDER BY oc.level, oc.name;

-- LATERAL for pagination with cursor
SELECT
  p.id,
  p.name,
  p.price,
  next_page.next_cursor
FROM products p
CROSS JOIN LATERAL (
  SELECT MIN(id) AS next_cursor
  FROM products
  WHERE id > p.id
  LIMIT 1
) next_page
WHERE p.id > 100  -- Current cursor
ORDER BY p.id
LIMIT 20;

-- Distance calculation with LATERAL
CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  lat DECIMAL(10, 7),
  lon DECIMAL(10, 7)
);

-- Find nearest locations to each location
SELECT
  l1.name AS location,
  nearby.name AS nearby_location,
  nearby.distance_km
FROM locations l1
CROSS JOIN LATERAL (
  SELECT
    l2.name,
    ROUND(
      6371 * acos(
        cos(radians(l1.lat)) * cos(radians(l2.lat)) *
        cos(radians(l2.lon) - radians(l1.lon)) +
        sin(radians(l1.lat)) * sin(radians(l2.lat))
      )::numeric,
      2
    ) AS distance_km
  FROM locations l2
  WHERE l2.id != l1.id
  ORDER BY
    6371 * acos(
      cos(radians(l1.lat)) * cos(radians(l2.lat)) *
      cos(radians(l2.lon) - radians(l1.lon)) +
      sin(radians(l1.lat)) * sin(radians(l2.lat))
    )
  LIMIT 5
) nearby;

-- LATERAL for running calculations
SELECT
  date,
  revenue,
  running.total_to_date,
  running.avg_last_7_days
FROM daily_revenue dr
CROSS JOIN LATERAL (
  SELECT
    SUM(revenue) AS total_to_date,
    AVG(revenue) AS avg_last_7_days
  FROM daily_revenue
  WHERE date >= dr.date - INTERVAL '7 days'
    AND date <= dr.date
) running
ORDER BY date;

2 files · sql Explain with highlit

LATERAL joins enable correlated subqueries in FROM clause. Each row can reference previous table columns. I use LATERAL for top-N-per-group queries. CROSS JOIN LATERAL iterates for each row. LEFT JOIN LATERAL includes rows without matches. Understanding LATERAL unlocks powerful query patterns. Function calls in LATERAL receive row values. Set-returning functions expand to multiple rows. LATERAL replaces complex subqueries with clearer syntax. Proper use improves both performance and readability. Essential for complex analytics, related records, rankings. LATERAL is PostgreSQL's secret weapon for advanced queries.