LATERAL

Query 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

Filter by Database
SQL LATERAL Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 8.0.14 Supported as of 8.0.14; use LATERAL keyword.
PostgreSQL Native 9.3 Full support for LATERAL.
SQL Server Supported via workaround - Workaround since 2005. No native LATERAL keyword. Use CROSS APPLY or OUTER APPLY for equivalent correlated FROM-clause behavior.
Oracle Native 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: Native since 8.0.14 PostgreSQL: Native since 9.3 SQL Server: Supported via workaround since 2005 Oracle: Native since 12c SQLite: Not supported

Per-Database Syntax & Notes

SQL Server Workaround

Use CROSS APPLY for inner lateral behavior or OUTER APPLY for left-lateral behavior. Since: 2005. No native LATERAL keyword. Use CROSS APPLY or OUTER APPLY for equivalent correlated FROM-clause behavior.

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