LATERAL Joins and Set Operations

A LATERAL join allows a subquery in the FROM clause to reference columns from tables listed earlier in the same FROM clause — effectively a correlated subquery that can return multiple rows. This is useful for applying a top-N subquery per row, such as fetching the three most recent orders for each user. Set operations combine the results of two SELECT statements: UNION removes duplicates (like DISTINCT), UNION ALL keeps duplicates and is faster, INTERSECT returns only rows present in both results, and EXCEPT returns rows from the first result that are not in the second. All set operations require the same number of columns with compatible types in both SELECT statements.

Example

-- LATERAL JOIN — top 3 orders per user
SELECT u.name, o.id AS order_id, o.total, o.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) o;

-- LATERAL with LEFT JOIN — include users with no orders
SELECT u.name, COALESCE(o.total, 0) AS latest_order_total
FROM users u
LEFT JOIN LATERAL (
  SELECT total FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) o ON TRUE;

-- UNION ALL — combine two result sets (keeps duplicates)
SELECT id, name, 'user'  AS source FROM users
UNION ALL
SELECT id, name, 'admin' AS source FROM admins;

-- EXCEPT — products never ordered
SELECT id, name FROM products
EXCEPT
SELECT DISTINCT p.id, p.name
FROM products p
JOIN order_items oi ON oi.product_id = p.id;

-- INTERSECT — users in both tables
SELECT email FROM newsletter_subscribers
INTERSECT
SELECT email FROM users WHERE is_active = TRUE;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned