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