LATERAL Joins in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Correlated Subqueries, Derived Tables (Subqueries in FROM)
Builds toward Choosing Between Subqueries, CTEs, and Joins
What are LATERAL Joins in SQL?
A LATERAL join is a subquery in FROM that can see the current row from the table to its left. Without LATERAL, every subquery in FROM runs independently — it has no access to the other tables being joined. LATERAL lifts that restriction.
The classic use case: you want the most recent order per customer — with the full order data attached. A correlated subquery in SELECT is limited to one scalar value per outer row. LATERAL lifts that restriction:
SELECT c.name AS customer_name, recent.ordered_at::date, recent.total_amount FROM customers c CROSS JOIN LATERAL ( SELECT ordered_at, total_amount FROM orders o WHERE o.customer_id = c.id ORDER BY ordered_at DESC LIMIT 1 ) recent LIMIT 10
For each customer, the LATERAL subquery filters orders to that customer, sorts by recency, and returns the most recent one. The c.id reference inside the subquery is what requires LATERAL: without it, PostgreSQL rejects the query.
How do you get the top N rows per group with LATERAL?
The key advantage of LATERAL is that it can return multiple rows per outer row. When the top-N pattern returns more than one result per outer row, LATERAL is the right tool:
SELECT u.user_id, u.name, recent.event_type, recent.event_time
FROM users u
CROSS JOIN LATERAL (
SELECT event_type, event_time
FROM events e
WHERE e.user_id = u.user_id
ORDER BY event_time DESC
LIMIT 3
) recentYou can do it with ROW_NUMBER and a filter, but LATERAL is often more efficient because LIMIT runs inside the subquery — PostgreSQL can stop scanning each user's events after finding 3, rather than computing ROW_NUMBER across the entire table first.
What is the difference between CROSS JOIN LATERAL and LEFT JOIN LATERAL?
CROSS JOIN LATERAL vs LEFT JOIN LATERAL
The join type controls what happens when the subquery returns no rows for an outer row.
CROSS JOIN LATERAL drops the outer row if the subquery returns nothing — the user with no events disappears from the result, like an inner join.
LEFT JOIN LATERAL keeps the outer row with NULLs in the subquery columns:
FROM users u
LEFT JOIN LATERAL (
SELECT event_type, event_time
FROM events e
WHERE e.user_id = u.user_id
ORDER BY event_time DESC
LIMIT 3
) recent ON trueA LEFT JOIN always needs an ON clause, and LATERAL has already expressed the correlation inside the subquery — so there is no condition left to write. ON true is the convention for satisfying the syntax; any always-true condition works the same way, and ON 1=1 is identical. CROSS JOIN LATERAL needs no ON clause at all. The condition is always satisfied; the LEFT JOIN semantics handle the NULL case.
When should you use LATERAL instead of a correlated subquery?
The one thing that trips people up
The mental model for LATERAL is "correlated subquery that can return multiple rows and columns." When the per-row computation needs to return one scalar value, use a correlated subquery in SELECT. When it needs to return multiple rows or multiple columns, use LATERAL. LATERAL is the right tool specifically because it isn't limited to a single value per outer row.
Why does generate_series often need LATERAL?
Where LATERAL appears beyond joins
LATERAL also comes up when a set-returning function needs to reference an outer column. PostgreSQL's unnest() and generate_series() are often used with LATERAL in this way — for example, generating a date range per entity based on that entity's own start and end dates. If you see LATERAL in a FROM clause next to a function call rather than a subquery, this is why: the function's arguments reference the outer row.
Practice LATERAL Joins in SQL
Brightlane's CRM team is building a customer engagement dashboard and needs order volume and revenue figures for every customer in a single result set.
Write a query to return every customer's ID, name, total number of orders placed, and total amount spent across every order.
Assumptions:
- A customer's order count is the number of orders linked to that
customer_id. The total spend is the combinedtotal_amountacross those orders. - Every customer must appear in the result. Customers with no orders on record should show an order count of
0and a missing total spend.
Output:
- One row per customer, with columns
id,name,order_count, andtotal_spent.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 LATERAL Joins practice problems
Write a query to return every customer's ID, name, total number of orders placed, and total amount spent across every order.
Write a query to return one row per order line item, showing the order ID, customer ID, product ID, and unit price for that item.
Write a query to return every product's ID, name, total times ordered, and total quantity sold across every order.
Write a query to return one row per order with at least one line item, showing the customer ID, customer name, order ID, and the count of line items in that order.
Write a query to return every customer's ID and name alongside the ID and total amount of each order they have placed. Customers with no orders on record should still appear with missing values in the order columns.
Write a query to return one row per session that contains at least one event, showing the user's ID, name, the session ID, and the total event count for that session.
Write a query to return every employee's ID, name, total number of salary records on file, and highest salary amount on record.
Write a query to return one row per customer-product pair on record, showing the customer ID, customer name, product ID, and the number of times that customer has purchased that product.
Write a query to return every high-spending customer's ID, name, total order count, and highest single-order amount on their account.
Start learning to practice all 9 LATERAL Joins problems, with instant grading and mastery tracking.
Common questions about LATERAL Joins
Do you write LATERAL with a comma or with JOIN?
Either works. A comma before LATERAL behaves like a cross join and drops outer rows whose subquery returns nothing. Writing LEFT JOIN LATERAL keeps them, and that form needs ON true, which reads oddly but is required syntax.
Can a LATERAL subquery see a table listed after it?
No. It sees only what comes before it in the FROM clause, so referencing a later table fails with a missing FROM-clause entry. Order the FROM clause so the tables a LATERAL depends on are already named.
When is a correlated subquery enough instead of LATERAL?
When one value per outer row is all you need. A correlated subquery in the SELECT list returns a single value; LATERAL is what you reach for when the per-row answer is several columns, or several rows, such as a customer and their three most recent orders.
