Lateral Joins
SQL · Extra Concepts
Theory
A normal subquery in the FROM clause cannot see the other tables in that FROM clause. A LATERAL join removes that limit: the subquery runs once for each row of the table on its left and can use that row's columns. Think of it as a for-each loop: for each department d, run 'SELECT the highest-paid employee WHERE dept_id = d.id ORDER BY salary DESC LIMIT 1'.
This makes LATERAL the cleanest way to write top-N-per-group queries (top 3 orders per customer, latest login per user) and to call table-returning functions per row. PostgreSQL, MySQL, Oracle and Snowflake use the LATERAL keyword; SQL Server and Oracle also offer CROSS APPLY (like an inner lateral join) and OUTER APPLY (keeps left rows with no match, like a LEFT JOIN). SQLite has no LATERAL, but for a single value per row a correlated scalar subquery in the SELECT list gives the same result. For larger N, window functions with ROW_NUMBER() are the portable alternative.
Why we use it
Questions like 'top 2 orders for each customer' need a per-row subquery that can use ORDER BY and LIMIT. LATERAL lets that subquery reference the outer row, which a regular derived table cannot do.
When to use it
Use LATERAL or CROSS APPLY for top-N per group and per-row function calls. In databases without it, use ROW_NUMBER() OVER (PARTITION BY ...) and filter rn <= N, or a correlated subquery for a single value.
How it works
- Take the next row from the left table (a department)
- Run the lateral subquery using that row's columns
- Subquery sorts and limits: top earner for this department
- Subquery returned rows? Yes: Join them to the row. No: Drop (CROSS) or NULLs (LEFT/OUTER).
- More left rows? Yes: Repeat for the next department. No: Finish.
- Return the combined result
Syntax
FROM t1, LATERAL (
SELECT ... FROM t2 WHERE t2.col = t1.col
) subExample
SELECT d.name, top.name AS top_earner
FROM departments d,
LATERAL (
SELECT name FROM employees e WHERE e.department_id = d.id ORDER BY salary DESC LIMIT 1
) top;Key points
- Postgres/SQL Server/Oracle feature; SQLite has no LATERAL keyword
- Typically used for "top N per group" queries
- SQL Server spells it CROSS APPLY / OUTER APPLY
- The subquery runs per outer row, so index the join column
- Portable alternative: ROW_NUMBER() OVER (PARTITION BY ...) and filter rn <= N