SQL - Lateral Join / CROSS APPLY | Application Architect

Key Insights

• Lateral joins (PostgreSQL) and CROSS APPLY (SQL Server) enable correlated subqueries in the FROM clause, allowing each row from the left table to pass parameters to the right-side table expression • These constructs eliminate the need for inefficient scalar subqueries in SELECT clauses and enable set-based operations that would otherwise require cursors or multiple queries • Performance gains are substantial when replacing row-by-row operations, particularly for top-N-per-group queries and complex aggregations that depend on values from outer rows

What Are Lateral Joins and CROSS APPLY

Lateral joins break the fundamental SQL rule that table expressions in the FROM clause cannot reference columns from other tables in the same FROM clause. In PostgreSQL, you use the LATERAL keyword. In SQL Server and Azure SQL, you use CROSS APPLY or OUTER APPLY.

Standard join behavior:

-- This fails - subquery cannot reference o.customer_id
SELECT o.order_id, recent.product_name
FROM orders o
JOIN (
    SELECT product_name 
    FROM order_items 
    WHERE order_id = o.order_id  -- ERROR: o not in scope
    LIMIT 1
) recent ON true;

With lateral join (PostgreSQL):

SELECT o.order_id, recent.product_name
FROM orders o
CROSS JOIN LATERAL (
    SELECT product_name 
    FROM order_items 
    WHERE order_id = o.order_id
    LIMIT 1
) recent;

With CROSS APPLY (SQL Server):

SELECT o.order_id, recent.product_name
FROM orders o
CROSS APPLY (
    SELECT TOP 1 product_name 
    FROM order_items 
    WHERE order_id = o.order_id
) recent;

Top-N Per Group Queries

The most common use case is retrieving the top N records for each group. Without lateral joins, you’d use window functions or self-joins, which are often less efficient.

Traditional approach with window functions:

-- PostgreSQL
WITH ranked AS (
    SELECT 
        e.department_id,
        e.employee_name,
        e.salary,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as rn
    FROM employees e
)
SELECT department_id, employee_name, salary
FROM ranked
WHERE rn <= 3;

With lateral join:

-- PostgreSQL
SELECT d.department_name, top_earners.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT employee_name, salary
    FROM employees e
    WHERE e.department_id = d.department_id
    ORDER BY salary DESC
    LIMIT 3
) top_earners;

SQL Server equivalent:

SELECT d.department_name, top_earners.*
FROM departments d
CROSS APPLY (
    SELECT TOP 3 employee_name, salary
    FROM employees e
    WHERE e.department_id = d.department_id
    ORDER BY salary DESC
) top_earners;

The lateral approach often performs better because it can use indexes more effectively and stops processing once it finds the required number of rows per group.

Replacing Scalar Subqueries

Scalar subqueries in the SELECT clause execute once per row, creating N+1 query problems. Lateral joins execute set-based operations instead.

Inefficient scalar subquery:

-- SQL Server
SELECT 
    c.customer_id,
    c.customer_name,
    (SELECT MAX(order_date) FROM orders WHERE customer_id = c.customer_id) as last_order,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) as order_count,
    (SELECT SUM(total_amount) FROM orders WHERE customer_id = c.customer_id) as total_spent
FROM customers c;

Optimized with CROSS APPLY:

-- SQL Server
SELECT 
    c.customer_id,
    c.customer_name,
    stats.last_order,
    stats.order_count,
    stats.total_spent
FROM customers c
CROSS APPLY (
    SELECT 
        MAX(order_date) as last_order,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent
    FROM orders
    WHERE customer_id = c.customer_id
) stats;

This executes a single pass through the orders table per customer instead of three separate scans.

OUTER APPLY vs CROSS APPLY

CROSS APPLY behaves like an INNER JOIN—if the applied table expression returns no rows, the left row is excluded. OUTER APPLY behaves like a LEFT JOIN, preserving left rows even when the applied expression returns nothing.

-- SQL Server - CROSS APPLY excludes customers without orders
SELECT c.customer_name, recent.order_date
FROM customers c
CROSS APPLY (
    SELECT TOP 1 order_date
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
) recent;

-- OUTER APPLY includes all customers
SELECT c.customer_name, recent.order_date
FROM customers c
OUTER APPLY (
    SELECT TOP 1 order_date
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
) recent;

PostgreSQL equivalent:

-- LEFT JOIN LATERAL includes all customers
SELECT c.customer_name, recent.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT order_date
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
    LIMIT 1
) recent ON true;

Complex Aggregations with Dependencies

Lateral joins excel when aggregations depend on calculations from outer queries.

-- PostgreSQL - Calculate running totals with dynamic windows
SELECT 
    t.transaction_date,
    t.amount,
    running.total_last_30_days
FROM transactions t
CROSS JOIN LATERAL (
    SELECT SUM(amount) as total_last_30_days
    FROM transactions
    WHERE account_id = t.account_id
      AND transaction_date BETWEEN t.transaction_date - INTERVAL '30 days' 
                               AND t.transaction_date
) running
ORDER BY t.transaction_date;

SQL Server version with date arithmetic:

SELECT 
    t.transaction_date,
    t.amount,
    running.total_last_30_days
FROM transactions t
CROSS APPLY (
    SELECT SUM(amount) as total_last_30_days
    FROM transactions
    WHERE account_id = t.account_id
      AND transaction_date BETWEEN DATEADD(day, -30, t.transaction_date) 
                               AND t.transaction_date
) running
ORDER BY t.transaction_date;

Unpivoting with APPLY

Table value constructors combined with APPLY provide elegant unpivoting solutions.

-- SQL Server - Unpivot multiple columns
SELECT 
    p.product_id,
    p.product_name,
    metrics.metric_name,
    metrics.metric_value
FROM products p
CROSS APPLY (
    VALUES 
        ('Views', p.view_count),
        ('Clicks', p.click_count),
        ('Purchases', p.purchase_count)
) metrics(metric_name, metric_value);

PostgreSQL equivalent:

SELECT 
    p.product_id,
    p.product_name,
    metrics.metric_name,
    metrics.metric_value
FROM products p
CROSS JOIN LATERAL (
    VALUES 
        ('Views', p.view_count),
        ('Clicks', p.click_count),
        ('Purchases', p.purchase_count)
) metrics(metric_name, metric_value);

Calling Table-Valued Functions

SQL Server table-valued functions naturally combine with APPLY to pass row values as parameters.

-- SQL Server - Table-valued function
CREATE FUNCTION dbo.GetEmployeeHierarchy(@employee_id INT)
RETURNS TABLE
AS
RETURN
(
    WITH hierarchy AS (
        SELECT employee_id, manager_id, employee_name, 0 as level
        FROM employees
        WHERE employee_id = @employee_id
        UNION ALL
        SELECT e.employee_id, e.manager_id, e.employee_name, h.level + 1
        FROM employees e
        INNER JOIN hierarchy h ON e.manager_id = h.employee_id
    )
    SELECT * FROM hierarchy
);

-- Use with CROSS APPLY
SELECT 
    m.manager_name,
    reports.employee_name,
    reports.level
FROM managers m
CROSS APPLY dbo.GetEmployeeHierarchy(m.employee_id) reports;

Performance Considerations

Lateral joins perform best when:

The applied subquery has selective filters based on outer row values
Appropriate indexes exist on join columns and filter predicates
The applied subquery uses LIMIT/TOP to restrict results

Monitor execution plans for:

-- PostgreSQL - Check if indexes are used
EXPLAIN ANALYZE
SELECT d.department_name, e.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT employee_name, salary
    FROM employees
    WHERE department_id = d.department_id
    ORDER BY salary DESC
    LIMIT 5
) e;

Look for “Index Scan” or “Index Seek” operations rather than full table scans. If you see sequential scans, add indexes:

CREATE INDEX idx_employees_dept_salary 
ON employees(department_id, salary DESC);

For SQL Server, examine the actual execution plan for “Nested Loops” with “Index Seek” on the inner side. High logical reads on the applied table indicate missing indexes or non-selective predicates.

Lateral joins and APPLY operators transform complex row-by-row logic into set-based operations. Use them to eliminate scalar subqueries, simplify top-N queries, and enable correlated calculations that would otherwise require procedural code.