Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 90 ยท Querying with PostgreSQL Features

LATERAL Joins

Learn PostgreSQL LATERAL joins: subqueries and functions in FROM that see earlier tables, top-N per row, unnest, LEFT JOIN LATERAL, plus runnable SQLite stand-ins.

In an ordinary FROM clause, every item is independent. A derived table (a subquery in FROM) cannot mention columns from the table next to it, because the database builds each item separately and only then joins them. That rule is simple, but it blocks a very common question: "for each author, give me their two most expensive books." The "two most expensive" part depends on which author you are looking at.

PostgreSQL solves this with the LATERAL keyword. A LATERAL item in FROM is allowed to refer to the items that appear before it, which makes it behave like a correlated subquery that lives in the FROM clause and can return several rows and several columns. It is one of the most useful features PostgreSQL has that many other databases lack (or spell differently: SQL Server calls it CROSS APPLY, Oracle also has LATERAL and CROSS APPLY, and MySQL added LATERAL in 8.0.14).

This lesson explains the idea, the syntax, and the patterns you will use most. Our in-browser engine is SQLite, which has no LATERAL keyword, so the LATERAL code blocks are read-only. Next to each one you get a runnable query that produces the same answer in SQLite, so you can see the result with your own eyes.

The problem LATERAL solves

You already know correlated subqueries in WHERE and SELECT. They return a single value per outer row, which is a limit when you want a small table per row: the latest three orders of each customer, the five closest stores to each address, the individual tags of each post.

Without LATERAL

Independent items Each FROM item is built on its own.
No peeking A subquery cannot see its neighbour's columns.
Awkward workarounds Window functions or a join to a pre-aggregated table.
1 / 2

The syntax

A lateral subquery sits after another table, is introduced by LATERAL, and needs an alias. Here are the two most expensive books of every author, in PostgreSQL:

SQL
SELECT a.name, top.title, top.price
FROM authors AS a
CROSS JOIN LATERAL (
  SELECT b.title, b.price
  FROM books AS b
  WHERE b.author_id = a.id      -- refers to the table before it
  ORDER BY b.price DESC
  LIMIT 2
) AS top
ORDER BY a.name, top.price DESC;

Conceptually the database takes one author row, runs the inner query with that author's id, joins the resulting rows to the author, and moves on to the next author. The inner query has its own ORDER BY and its own LIMIT, and both apply per outer row. That is the whole trick: you cannot get "limit per group" from a plain join, but you can get it from a lateral subquery.

Three details to remember:

  • CROSS JOIN LATERAL (...) and , LATERAL (...) are the same thing. An author with no books disappears from the result, as with any inner join.
  • To keep authors that have no matching rows, use LEFT JOIN LATERAL (...) AS top ON true. The ON true is required syntax, because a left join needs a condition and the real condition already sits inside the subquery.
  • The lateral item can only refer to items to its left. It cannot refer to something that comes later in the FROM list, and it cannot refer to the other side of a RIGHT or FULL join.

Running the same idea in SQLite

SQLite has no lateral subqueries, but the same answers can be produced with window functions. Rank each author's books with ROW_NUMBER() in a derived table and keep the first two:

SQL Top 2 books per author (window function) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

You get ten rows: the two priciest titles of each of the five authors. The result is identical to what the CROSS JOIN LATERAL version returns in PostgreSQL. The difference is how the database gets there. The window version ranks every book once. The lateral version, given an index on books (author_id, price), can stop after reading two index entries per author, which matters when each author has thousands of rows and you only want a few.

If you need exactly one row per outer row (the "top 1"), a correlated subquery with LIMIT 1 works in SQLite, and it is what LATERAL ... LIMIT 1 corresponds to:

SQL Each customer's latest order (correlated LIMIT 1) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Notice the weakness: the query has to repeat the whole "find the latest order" logic once per column. With LATERAL you write it once and select as many columns as you like:

SQL
SELECT c.name, last.ordered_on, last.title
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.ordered_on, b.title
  FROM orders AS o
  JOIN books AS b ON b.id = o.book_id
  WHERE o.customer_id = c.id
  ORDER BY o.ordered_on DESC, o.id DESC
  LIMIT 1
) AS last ON true
ORDER BY c.name;

Because it is a LEFT JOIN LATERAL, a customer with no orders still appears, with NULL in the two lateral columns. This "one subquery, many columns" shape is a good reason to prefer LATERAL over several scalar subqueries.

Set-returning functions are lateral by default

PostgreSQL has functions that return a whole table: unnest(), generate_series(), jsonb_array_elements() and many more. When such a function appears in FROM, it is implicitly lateral, so it can use columns from the tables before it without the keyword:

SQL
SELECT p.id, p.title, t.tag
FROM posts AS p
CROSS JOIN LATERAL unnest(p.tags) AS t(tag);   -- LATERAL is optional here
-- the same thing, with the comma form:
-- FROM posts AS p, unnest(p.tags) AS t(tag)

This is how you turn an array column into one row per element. A post with an empty array disappears, just like an author with no books in the CROSS JOIN LATERAL example. To keep it, write LEFT JOIN LATERAL unnest(p.tags) AS t(tag) ON true.

SQLite has the same idea for its table-valued functions. json_each() takes a JSON array and returns one row per element, and it can see the table to its left. Let's store tags as JSON text and expand them:

SQL One row per tag with json_each

Press Run (or Ctrl + Enter) to see the result here.

The first result has five rows (the draft is gone), the second has six (the draft shows up with an empty tag), and the third counts how many posts use each tag: sql appears twice and the others once. The PostgreSQL versions of these queries use unnest(p.tags) and ON true in the same places.

When to use LATERAL

Reach for LATERAL when:

  • You need top-N per row. "Latest three comments per post", "cheapest two offers per product". The pattern is a lateral subquery with ORDER BY ... LIMIT n.
  • You want to reuse a computed value. A lateral subquery can compute something once, and later items and the SELECT list can reuse it under a name, instead of repeating an expression.
  • You expand arrays or JSON. unnest, jsonb_array_elements and friends are lateral by nature.
  • You call a function per row that returns several rows or columns, such as a geographic "nearest neighbours" search.

Prefer a plain join, a window function or a regular aggregate when the answer needs every row of the other table anyway. A lateral subquery runs once per outer row, so with a million outer rows and no useful index, you run a million small queries. PostgreSQL can often make that fast with an index that matches the WHERE and ORDER BY of the inner query, but you should check with EXPLAIN on realistic data.

A good test: say the query out loud. If it sounds like "for each X, find the first few Y such that ...", it is a lateral subquery.

Common mistakes

  • Forgetting ON true. LEFT JOIN LATERAL (...) AS x without an ON clause is a syntax error.
  • Expecting a left join from a comma. FROM a, LATERAL (...) is an inner join, so rows with no match disappear.
  • Missing alias. A lateral subquery must have an alias, like every derived table.
  • Referencing something to the right. Only earlier FROM items are visible.
  • Writing LIMIT without ORDER BY. Which rows you get is then arbitrary.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the LATERAL keyword allow a subquery in FROM to do?

    A lateral item is evaluated for each row of the items to its left and may use their columns, like a correlated subquery that is allowed to return a whole table.
  2. 2. You write FROM customers c LEFT JOIN LATERAL (SELECT ...) AS x, with no ON clause. What happens?

    Every LEFT JOIN needs an ON clause. With LATERAL the real condition is already inside the subquery, so you write ON true.
  3. 3. Which query shape gives 'the three newest comments of each post' in PostgreSQL?

    The LIMIT inside the lateral subquery applies per outer row. A LIMIT on a plain join, or at the end of the whole query, limits the total, not each group.
  4. 4. What is true about FROM posts p, unnest(p.tags) AS t(tag) in PostgreSQL?

    Functions that return sets can use earlier FROM items without writing LATERAL. As an inner join it drops rows whose array is empty.
  5. 5. In this lesson's SQLite examples, what stands in for a LATERAL top-N subquery?

    SQLite has no LATERAL, so the live demo ranks rows with a window function and keeps the first N per group.

Summary

  • LATERAL lets a subquery or function in FROM use columns from earlier FROM items, so it runs once per row of those items.
  • The classic use is top-N per row: a lateral subquery with ORDER BY ... LIMIT n.
  • CROSS JOIN LATERAL (or a comma) drops outer rows with no matches; LEFT JOIN LATERAL (...) ON true keeps them.
  • Set-returning functions such as unnest() are implicitly lateral.
  • One lateral subquery can return many columns, which beats repeating several scalar subqueries.
  • SQLite has no LATERAL: use ROW_NUMBER(), correlated LIMIT 1 subqueries and json_each() for the same results.
  • Pair lateral subqueries with a matching index, and check the plan on real data.

Next: generate_series and Set-Returning Functions, the functions that turn a single call into many rows.