Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- 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.

SQL Top 2 books per author with ROW_NUMBER() ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Latest order per customer with a correlated subquery ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL LATERAL fails in SQLite (fails on purpose) ยท database: bookstore

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

SQL
-- 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.
SQL
-- 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 LIMIT or TOP per 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 TRUE keeps 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 = ... plus ORDER 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.