SQL Academy · Lesson

Lateral Joins (LATERAL JOIN)

Reference the previous FROM item from inside a subquery with LATERAL — the SQL equivalent of a per-row function call.

Lateral Joins (LATERAL JOIN) is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is LATERAL?

LATERAL lets a sub-select in the FROM clause reference columns from items earlier in the FROM list. Without LATERAL, FROM-items can't see each other.

Mental Model

LATERAL is "for each row of the left side, evaluate this query". It's like a correlated subquery — but in the FROM clause and returning multiple rows/columns.

Top 3 Orders per User

The classic LATERAL use:

SELECT u.id, u.email, o.id AS order_id, o.total
FROM users u
JOIN LATERAL (
  SELECT id, total FROM orders
  WHERE user_id = u.id
  ORDER BY total DESC
  LIMIT 3
) AS o ON true;

Why Not a Subquery in SELECT?

A scalar subquery in SELECT returns one value. LATERAL returns multiple rows and multiple columns per outer row.

LATERAL with Functions

Set-returning functions are LATERAL-ish by default:

SELECT t.id, unnest_tag
FROM posts t
CROSS JOIN LATERAL unnest(t.tags) AS unnest_tag;

-- Equivalent (set-returning functions are implicit LATERAL):
SELECT t.id, unnest_tag
FROM posts t, unnest(t.tags) AS unnest_tag;

LATERAL vs Window Functions

For "top-N per group", both LATERAL and ROW_NUMBER work. ROW_NUMBER is often more efficient because it walks the table once; LATERAL re-queries per outer row.

-- ROW_NUMBER alternative:
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
)
SELECT u.email, r.id, r.total
FROM users u
JOIN ranked r ON r.user_id = u.id
WHERE r.rn <= 3;

LATERAL with Aggregates

Per-row precomputed values:

SELECT u.email, stats.last_order_at, stats.total_orders
FROM users u
LEFT JOIN LATERAL (
  SELECT MAX(created_at) AS last_order_at,
         COUNT(*)        AS total_orders
  FROM orders WHERE user_id = u.id
) AS stats ON true;

ON true

LATERAL requires an ON clause for JOIN — but the join condition lives inside the subquery, so the outer ON is true.

CROSS JOIN LATERAL vs LEFT JOIN LATERAL

CROSS JOIN LATERAL — outer row dropped if inner returns no rows
LEFT JOIN LATERAL ... ON true — outer row kept with NULL columns

LATERAL for Distance Queries

For each user, the nearest store:

SELECT u.id, s.name, s.distance
FROM users u
CROSS JOIN LATERAL (
  SELECT name, location <-> u.location AS distance
  FROM stores
  ORDER BY location <-> u.location
  LIMIT 1
) AS s;

LATERAL is SQL-Standard

Standardised in SQL:1999. PostgreSQL implements it as LATERAL; SQL Server uses CROSS APPLY / OUTER APPLY.

Performance

LATERAL is a per-outer-row execution. With an appropriate index on the inner table, each iteration is fast. Without one, it can be slow at scale.

Recap

LATERAL = "FROM-subquery that sees the outer row".

Top-N per group
Per-row precomputation
Set-returning functions
Pair with LEFT JOIN ... ON true for outer joins

Quick Check

What does LATERAL enable that a normal FROM-subquery does not?

Frequently asked questions

Is the “Lateral Joins (LATERAL JOIN)” lesson free?

Yes — the full text of “Lateral Joins (LATERAL JOIN)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Lateral Joins (LATERAL JOIN)”?

Reference the previous FROM item from inside a subquery with LATERAL — the SQL equivalent of a per-row function call. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Lateral Joins (LATERAL JOIN)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy