LATERAL Joins

Learning Focus

Use this lesson to understand LATERAL joins — a DuckDB feature that lets subqueries reference columns from preceding tables in the FROM clause.

What is a LATERAL Join?

A LATERAL join allows a subquery (or function call) in the FROM clause to reference columns from tables that appear earlier in the same FROM clause. Think of it as a correlated subquery that returns a table instead of a scalar.

Basic Syntax

SELECT ...
FROM table_a,
LATERAL (SELECT ... FROM table_b WHERE table_b.col = table_a.col) sub;

Examples

Example 1: Top N per Group (Lateral Pattern)

-- Top 2 earners from each department (lateral alternative to row_number CTE)
SELECT e.department, top.name, top.salary
FROM (SELECT DISTINCT department FROM employees) e,
LATERAL (
    SELECT name, salary
    FROM employees
    WHERE department = e.department
    ORDER BY salary DESC
    LIMIT 2
) top;

Example 2: Flatten a List Column

-- Unnest a list column into rows (DuckDB shorthand)
SELECT student_id, unnest(test_scores) AS score
FROM student_scores;

-- Equivalent LATERAL form
SELECT s.student_id, scores.score
FROM student_scores s,
LATERAL (SELECT unnest(s.test_scores) AS score) scores;

Example 3: Apply a Function Per Row

-- Compute stats for each department's salary array
SELECT e.department, stats.*
FROM (SELECT DISTINCT department FROM employees) e,
LATERAL (
    SELECT
        MIN(salary) AS min_sal,
        MAX(salary) AS max_sal,
        AVG(salary) AS avg_sal
    FROM employees
    WHERE department = e.department
) stats;

LATERAL vs Correlated Subquery

Aspect	Correlated Subquery	LATERAL
Returns	Scalar	Table (multiple rows/columns)
Location	WHERE / SELECT	FROM clause
Multiple columns	No	Yes

Common Pitfalls

Pitfall	Consequence	Prevention
LATERAL without referencing outer query	Identical to a plain subquery	Ensure the lateral references outer columns
Forgetting aliases	Column name ambiguity	Always alias the LATERAL subquery
Heavy use on large tables	Per-row execution	Use window functions or JOINs when possible

Quick Reference

-- Lateral with correlation
FROM outer_table, LATERAL (SELECT ... WHERE col = outer_table.col LIMIT n) sub

-- Unnest via lateral
FROM t, LATERAL (SELECT unnest(t.list_col) AS val) exploded

-- Cross apply equivalent
FROM t, LATERAL (SELECT ... FROM other WHERE other.id = t.id) derived

What's Next

• Next: File and Data Ingestion - Read CSV, Parquet, and JSON files natively.
• Module Overview - Return to this module index.