A subquery in FROM that can reference columns from preceding tables.
FROM <table1>, LATERAL (<subquery>) AS <alias>## Overview A LATERAL subquery is a subquery in the FROM clause that can reference columns from tables that appear earlier in the same FROM clause. Without LATERAL, subqueries in FROM are evaluated independently and cannot see columns from sibling tables. LATERAL enables row-by-row dependent evaluation, making it possible to express top-N-per-group queries, per-row aggregations, and table-valued function calls that depend on the current row. ## Execution Order LATERAL subqueries are evaluated as part of the FROM clause. For each row produced by the tables to the left of the LATERAL keyword, the lateral subquery is evaluated with the current row's column values substituted into its references. The result is then joined with the current outer row. This is conceptually similar to a correlated subquery, but LATERAL produces a derived table (potentially multiple rows and columns) rather than a scalar value. ## Behavior - **Column references**: The LATERAL subquery may reference any column from a table that appears before it in the FROM clause. It cannot reference tables that appear after it. - **Cross join semantics**: `FROM t1, LATERAL (subquery) AS alias` uses implicit cross join semantics. For each row of t1, the subquery is evaluated, and the outer row is paired with each row the subquery returns. If the subquery returns zero rows, the outer row is excluded from the result. - **LEFT JOIN LATERAL**: To retain outer rows even when the lateral subquery returns zero rows, use `LEFT JOIN LATERAL (subquery) AS alias ON true`. The ON true condition is required syntactically but does not filter; the join preserves all left-side rows with NULLs for the lateral columns when no match exists. - **Top-N-per-group**: LATERAL is particularly effective for top-N-per-group queries. By placing ORDER BY and LIMIT inside the lateral subquery, you retrieve the top N related rows for each outer row without the complexity of window function approaches. - **Table-valued functions**: LATERAL enables calling table-valued functions (such as UNNEST for array expansion) that depend on the current row's values. - **Chained LATERAL**: Multiple LATERAL subqueries can appear in a single FROM clause. Each one can reference columns from all preceding tables and earlier LATERAL subqueries, enabling multi-step dependent computations. - **Optimization**: The optimizer may convert LATERAL subqueries into correlated joins. When the subquery is simple enough, it may be decorrelated and executed as a hash join with a runtime filter, avoiding per-row evaluation. - **NULL handling**: If a referenced outer column is NULL, the lateral subquery receives that NULL value in its predicates. Standard NULL comparison rules apply within the subquery. ## Compatibility LATERAL subqueries conform to the SQL:1999 standard. LEFT JOIN LATERAL with ON true follows PostgreSQL syntax conventions. The ability to reference preceding FROM items is the defining characteristic of LATERAL and distinguishes it from ordinary derived tables.
-- Top 3 recent orders per customer
SELECT c.name, recent.amount, recent.order_date
FROM customers c,
LATERAL (
SELECT amount, order_date
FROM orders
WHERE customer_id = c.id
ORDER BY order_date DESC
LIMIT 3
) recent;-- LATERAL with LEFT JOIN to include customers with no orders
SELECT c.name, recent.amount
FROM customers c
LEFT JOIN LATERAL (
SELECT amount
FROM orders
WHERE customer_id = c.id
ORDER BY order_date DESC
LIMIT 1
) recent ON true;-- Compute per-row statistics from a related table
SELECT d.department_name, stats.avg_salary, stats.headcount
FROM departments d,
LATERAL (
SELECT AVG(salary) AS avg_salary, COUNT(*) AS headcount
FROM employees
WHERE dept_id = d.id
) stats;-- LATERAL for top-N-per-group without window functions
SELECT p.category, top.product_name, top.revenue
FROM (
SELECT DISTINCT category FROM products
) p,
LATERAL (
SELECT product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM products pr
JOIN order_items oi ON pr.id = oi.product_id
WHERE pr.category = p.category
GROUP BY product_name
ORDER BY revenue DESC
LIMIT 5
) top;-- LATERAL with a table-valued function (UNNEST)
SELECT o.id, tag.value AS tag
FROM orders o,
LATERAL UNNEST(o.tags) AS tag(value);-- Chained LATERAL references
SELECT c.name, last_order.id, items.product_name
FROM customers c,
LATERAL (
SELECT id FROM orders WHERE customer_id = c.id
ORDER BY order_date DESC LIMIT 1
) last_order,
LATERAL (
SELECT p.product_name
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = last_order.id
) items;