LATERALQuery Techniques
Buy Me A Coffee

Allows a subquery in the FROM clause to reference columns from tables to its left, enabling advanced row-by-row operations.

A join where the right side can reference columns from the left side row-by-row, enabling per-row computation. Particularly powerful for top-N per group, row-level calculations, and converting arrays to rows. Can be expensive -- test on large datasets.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0.14 Supported as of 8.0.14; use LATERAL keyword.
PostgreSQL ✓ Supported 9.3 Full support for LATERAL.
SQL Server ✓ Supported 2005 Use CROSS APPLY/OUTER APPLY instead of LATERAL.
Oracle ✓ Supported 12c Supported as of 12c; use LATERAL keyword.
SQLite ✗ Not Supported — No support for LATERAL or APPLY.

Details

LATERAL enables correlated subqueries in the FROM clause, making it possible to join each row to a derived table that depends on that row. In SQL Server, use CROSS APPLY or OUTER APPLY for similar functionality.

Standard Syntax

SELECT t.id, x.val FROM my_table t, LATERAL (SELECT val FROM other_table WHERE other_table.ref_id = t.id) x;

Version Support

MySQL: Since 8.0.14 PostgreSQL: Since 9.3 SQL Server: Since 2005 Oracle: Since 12c SQLite: Not supported

Per-Database Syntax & Notes