SQL Reference ยท Joins and Subqueries
SQL LATERAL joins
Reference for LATERAL joins: let a subquery in FROM use columns of earlier tables, with PostgreSQL, MySQL and Oracle LATERAL, SQL Server APPLY and SQLite equivalents.
Normally a subquery in the FROM clause is evaluated on its own and cannot see the other tables of the same FROM list. A lateral join removes that rule: the subquery may refer to columns of tables listed before it, so it is evaluated once for each row on its left. It is a join with a correlated subquery on the right-hand side.
The classic use is "top N per group": for each author, fetch their two most expensive books. SQL Server and Oracle call the feature CROSS APPLY and OUTER APPLY. SQLite has no lateral joins, but the same results come from window functions or correlated subqueries, as shown below.
-- PostgreSQL, MySQL 8.0.14+, Oracle 12c+ SELECT a.name, t.title FROM authors AS a CROSS JOIN LATERAL ( SELECT title FROM books b WHERE b.author_id = a.id ORDER BY price DESC LIMIT 2 ) AS t;
Try it
SQLite cannot run LATERAL, so these examples use runnable equivalents.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
SQLite reports Error: near "SELECT": syntax error, because it reads LATERAL as a table alias and then does not expect a subquery.
Syntax
-- Standard / PostgreSQL / MySQL / Oracle FROM left_table [CROSS | INNER] JOIN LATERAL (subquery) AS alias [ON TRUE] FROM left_table LEFT JOIN LATERAL (subquery) AS alias ON TRUE -- SQL Server / Oracle FROM left_table CROSS APPLY (subquery) AS alias FROM left_table OUTER APPLY (subquery) AS alias
| Part | Description |
|---|---|
LATERAL |
Allows the following subquery or function call to reference columns of tables to its left in the same FROM. |
CROSS JOIN LATERAL / CROSS APPLY |
Rows on the left with no result from the subquery disappear (like an inner join). |
LEFT JOIN LATERAL ... ON TRUE / OUTER APPLY |
Rows on the left are kept, with NULLs for the subquery columns when it returns nothing. |
Dialect differences
| Database | Support |
|---|---|
| SQLite | None. Use ROW_NUMBER(), correlated scalar subqueries or a join to an aggregated subquery. |
| PostgreSQL | LATERAL since 9.3; also implicit for set-returning functions in FROM. |
| MySQL | LATERAL derived tables since 8.0.14. |
| SQL Server | CROSS APPLY and OUTER APPLY since 2005; no LATERAL keyword. |
| Oracle | LATERAL, CROSS APPLY and OUTER APPLY since 12c. |
-- PostgreSQL: keep authors without books too SELECT a.name, t.title FROM authors AS a LEFT JOIN LATERAL ( SELECT title FROM books AS b WHERE b.author_id = a.id ORDER BY price DESC LIMIT 2 ) AS t ON TRUE; -- SQL Server SELECT a.name, t.title FROM authors AS a CROSS APPLY ( SELECT TOP 2 title FROM books AS b WHERE b.author_id = a.id ORDER BY price DESC ) AS t;
Usage notes
- Why not a plain join? A normal join cannot apply
LIMITorTOPper left row. A lateral subquery can, because it is run per row. - Choose the join type.
CROSS/inner drops left rows with no result,LEFT ... ON TRUEkeeps them. - Performance. A lateral subquery runs once per left row, so it is fast when the left side is small or the subquery uses an index (
WHERE author_id = ...plusORDER BY ... LIMIT). With a large left side, a window function is often faster. - Reuse of expressions. Lateral subqueries can compute a value once and expose it under a name for later parts of the query.
- Set-returning functions. In PostgreSQL,
FROM table, unnest(table.array_col)is implicitly lateral. - Portability. If you must support SQLite, MySQL below 8.0.14 or older SQL Server syntax, prefer window functions.