LATERAL Joins in SQL
LATERAL allows a subquery in the FROM clause to reference columns from tables that appear earlier in the same FROM clause. Without LATERAL, each subquery in FROM is evaluated independently, with no access to the rows being produced by other FROM items. LATERAL lifts that restriction.
Before this Correlated Subqueries, Derived Tables (Subqueries in FROM)
Builds toward Choosing Between Subqueries, CTEs, and Joins
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:
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.
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.
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 trueThe ON true is required syntax for LEFT JOIN LATERAL. The condition is always satisfied; the LEFT JOIN semantics handle the NULL case.
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.
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.
9 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.
These problems are part of the LATERAL Joins lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.