SQL LATERAL

What does SQL LATERAL do?

LATERAL lets a subquery or set-returning function in the FROM clause reference columns from tables that appear earlier in that same FROM list.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LATERAL: PostgreSQL 9.3+, MariaDB 10.11+, MySQL 8.0+, Oracle 12c+, SQLite 3.39+. SQL Server does not support the keyword but offers CROSS/OUTER APPLY with identical semantics.

SQL LATERAL Full Explanation

LATERAL turns an otherwise isolated derived table into a correlated one. When you prefix a subquery, table function, or VALUES list with LATERAL inside the FROM clause, the SQL engine evaluates it once per row produced by the tables that appear to its left. Inside the LATERAL subquery you can therefore use columns from those preceding tables, just like in a correlated subquery in the WHERE clause, but with the power to return a full result set that can be joined, filtered, or aggregated.Key behaviors:- Scope: Only columns from tables that come before the LATERAL item are visible; later tables are not.- Execution order: For each row of the left-hand table(s), the LATERAL subquery is executed, similar to a nested-loops join.- Join types: You can pair LATERAL with CROSS JOIN, INNER JOIN, LEFT JOIN, or simply place it as a comma-separated item (which is equivalent to CROSS JOIN LATERAL).- Performance: Since the subquery runs per row, heavy logic can be expensive. Indexing and limiting rows on the left side are important.- ANSI SQL: LATERAL is part of SQL:2011. SQL Server and older MySQL versions lack the reserved word but offer equivalent APPLY or correlated derived tables.

SQL LATERAL Syntax

FROM left_table
CROSS JOIN LATERAL (
    SELECT ...
    FROM right_table
    WHERE right_table.foreign_id = left_table.id
) AS alias

SQL LATERAL Parameters

Example Queries Using SQL LATERAL

-- 1. Expand JSON objects stored in a column
SELECT u.id,
       j.order_id,
       j.total
FROM   users u
CROSS JOIN LATERAL jsonb_to_recordset(u.orders_json) AS j(order_id int, total numeric);

-- 2. Get the two most recent orders per customer
SELECT c.customer_id,
       o.*
FROM   customers c
LEFT JOIN LATERAL (
    SELECT *
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    ORDER  BY o.created_at DESC
    LIMIT  2
) o ON true;

-- 3. Compare with SQL Server APPLY equivalent
-- SELECT *
-- FROM customers c
-- CROSS APPLY (
--     SELECT TOP 2 *
--     FROM orders o
--     WHERE o.customer_id = c.customer_id
--     ORDER BY o.created_at DESC
-- ) o;

Expected Output Using SQL LATERAL

Each query returns a result set where the rows produced by the LATERAL subquery are joined to the current row of the left-hand table
Example 2 yields up to two order rows per customer, even when no orders exist due to the LEFT JOIN

Use Cases with SQL LATERAL

Unnesting or expanding arrays/JSON per row
Limiting or ordering child rows per parent (top-N per group)
Calling set-returning functions that depend on the current row
Replacing multiple correlated subqueries with a single derived table for readability

Common Mistakes with SQL LATERAL

Forgetting to alias the LATERAL subquery, leading to syntax errors
Referencing columns from tables that appear to the right of the LATERAL item
Assuming the subquery runs only once; it runs per row and can be slow if not optimized
Using CROSS JOIN LATERAL when a LEFT JOIN LATERAL is required, causing rows to drop when the subquery returns no rows

First Introduced In

PostgreSQL 9.3, SQL:2011 standard

Frequently Asked Questions

What problems does LATERAL solve?

It lets you join each row to a custom, possibly multi-row result generated by a subquery or function that can see that row's values. This eliminates workarounds like self joins or repeated correlated subqueries.

How is CROSS JOIN LATERAL different from INNER JOIN LATERAL?

CROSS JOIN LATERAL (or simply placing the LATERAL item without an ON clause) keeps only rows that produce at least one match. INNER JOIN LATERAL lets you add an ON filter. LEFT JOIN LATERAL preserves all left-hand rows even when the subquery returns no rows.

Do I always need the word LATERAL?

Yes, unless your database infers it by default (PostgreSQL requires the keyword). In SQL Server you use APPLY instead; in Oracle you can omit LATERAL with table functions.

Can I chain multiple LATERAL subqueries?

Yes. Each LATERAL subquery can see columns from all tables before it, so you can build step-by-step derived tables while keeping the query readable.