skip to content

In SQL, how does CROSS JOIN LATERAL (VALUES ...) unpivot twelve month columns, and what does it save compared with twelve UNION ALL branches?

level: middleimportance: should knowfreq 31%

answer

  1. a constant table that reads the row
  2. evaluated once per source row
  3. pairs of label and column
  4. the source appears once
  5. not every engine spells it this way

basics

~20 s

For each source row, a VALUES list reads that row's columns and emits one label-and-value row per month. The result matches UNION ALL, but the source appears once, so it is typically read once rather than twelve times.

solid answer

~50 s

For each row of the wide table, `LATERAL` lets a `VALUES` list read that row's columns, so `CROSS JOIN LATERAL (VALUES (1, w.jan_sales), (2, w.feb_sales), ...) AS m (month_no, amount)` emits one row per pair. Without `LATERAL`, PostgreSQL rejects the query, because a plain subquery in `FROM` cannot reference `w`. The main saving is that the source appears once and is typically read once, where twelve `UNION ALL` branches usually mean twelve reads, and that gap grows when the source is an expensive join or view. You also get one `FROM` and one `WHERE`, so a filter cannot land on a single branch by accident. The output is the same: twelve rows per source row, NULLs kept. It is widely available but not universal: SQL Server writes `CROSS APPLY (VALUES ...)`, MySQL's `VALUES` wraps rows in `ROW(...)`, and SQLite has no `LATERAL`, so `UNION ALL` is the fallback there.

code

sql · 12 lines
sql
WITH sales_wide (product_id, jan_sales, feb_sales, mar_sales) AS (
  VALUES (1, 10, NULL, 30),
         (2, 5, 7, NULL)
)
SELECT w.product_id, m.month_no, m.amount
FROM sales_wide AS w
CROSS JOIN LATERAL (
  VALUES (1, w.jan_sales),
         (2, w.feb_sales),
         (3, w.mar_sales)
) AS m (month_no, amount)
ORDER BY w.product_id, m.month_no;

go deeper

for a junior

Recognise the pattern: a VALUES list of label and column pairs, joined with CROSS JOIN LATERAL. Know that it returns the same rows as the UNION ALL version.

for a middle

Explain the per-row evaluation, why LATERAL is required for the VALUES list to see the source row, and why the row count still multiplies by the number of pairs.

for a senior

Compare the plans rather than asserting a speed-up: one read against one per branch, with the gap depending on how costly the source is. Name which engines need a different spelling.

for a principal

Weigh a single-read form against a codebase that spans several engines. Standardising on one spelling per engine costs review effort, and that can matter more than read cost on small tables.

## The building blocks Two features combine here, and each does one small job. - **`VALUES`** builds a constant table inside a query, with no table on disk. Each parenthesised list is one row, so `VALUES (1, 'a'), (2, 'b')` is a two-row, two-column table. - **`LATERAL`** allows a subquery in `FROM` to reference columns of the `FROM` items before it. A plain subquery is evaluated on its own and cannot see them. Put together, the "constant" table is no longer constant: its expressions are columns of the current source row. ```sql SELECT w.product_id, m.month_no, m.amount FROM sales_wide AS w CROSS JOIN LATERAL ( VALUES (1, w.jan_sales), (2, w.feb_sales), (3, w.mar_sales) ) AS m (month_no, amount) ORDER BY w.product_id, m.month_no; ``` A full table lists twelve pairs. Three are shown to keep the example short. ## How it evaluates The PostgreSQL documentation describes the evaluation of a `LATERAL` item as repeating per source row: 1. The engine takes one row from `sales_wide`. 2. It evaluates the `VALUES` list using that row's columns, producing one row per pair. 3. It joins those rows to the source row they came from. 4. It moves to the next source row and repeats. `CROSS JOIN` adds no join condition, so every generated row is kept. With twelve pairs, each source row produces twelve output rows. A NULL cell is just a pair whose second value is NULL, so it produces a row like any other, and the count matches the `UNION ALL` form exactly. ## What it saves over UNION ALL The output is the same. What changes is how many times you state the source, and usually how many times it is read. | Concern | Twelve `UNION ALL` branches | `CROSS JOIN LATERAL (VALUES ...)` | |---|---|---| | Source in the query text | Twelve times | Once | | Typical table reads | One per branch | One | | Where a filter goes | Per branch, or around the stack | One `WHERE` | | Label beside its column | Yes, across a long statement | Yes, in a compact list | | Runs as written | On every mainstream engine | PostgreSQL; others need changes | Running both through `EXPLAIN` on PostgreSQL shows the difference plainly: the `UNION ALL` plan has an `Append` over one sequential scan per branch, and the `LATERAL` plan is a nested loop over a single scan with a `Values Scan` inside. Planners differ, so read your own engine's plan before quoting a number. The saving is largest when: - the source is a **multi-table join or an expensive view**, which `UNION ALL` restates in every branch; - the table is **large and wide**, so twelve full reads cost real time; - the source changes, since **one edit** then covers every month. It matters little for a small table in a one-off script. ## Engine differences The form is widely available, not universal, so name your engine when you recommend it. - **PostgreSQL** runs the query above as written. Its default column names for a `VALUES` table are `column1`, `column2`, and the documentation notes that other systems name them differently, so always supply an alias list. - **SQL Server** has no `LATERAL` keyword and uses `CROSS APPLY` with a `VALUES` list for the same job. - **MySQL** has `LATERAL`, but its `VALUES` statement wraps each row in `ROW(...)`, so the text above does not run unchanged. - **Oracle** has `LATERAL`, and a `VALUES` table constructor only in its newest releases. - **SQLite** has `VALUES` but no `LATERAL`, so `UNION ALL` is the way to unpivot there. ## Two details worth knowing **Several measures per month.** A `VALUES` row can hold more than two expressions, so `(1, w.jan_units, w.jan_revenue)` unpivots two measures together and keeps them on one row. **Types still have to agree.** Corresponding entries in the list need compatible types. PostgreSQL resolves them with the same rules it uses for `UNION`, so moving from one form to the other does not fix a mix of integer and text columns. `LATERAL` joins have many other uses, such as top-N-per-group queries, which are a separate subject. For unpivoting you need only this one narrow use: a `VALUES` list that reads the current row.

  • How would you unpivot two measures per month, say units and revenue, with this form?
    Give each `VALUES` row three expressions: `(1, w.jan_units, w.jan_revenue), (2, w.feb_units, w.feb_revenue), ...`, with an alias list such as `AS m (month_no, units, revenue)`. One row per month then holds both measures, which keeps them aligned. Doing it with two single-measure unpivots would force a self-join on product and month to bring them back together.
  • When is the twelve-branch UNION ALL form still the right choice?
    When the engine has no usable form, as in SQLite, when one script must run unchanged across several engines, or when the table is small enough that repeated reads do not matter and readers know the form. Compatibility is the honest reason: on an engine that supports `LATERAL`, the single-read form is usually no harder to read.

  • CROSS JOIN here pairs every source row with every other source row.
  • LATERAL (VALUES ...) drops NULL cells, unlike the UNION ALL form.
  • LATERAL can be left out, because a VALUES list in FROM can always see earlier tables.
  • This form runs unchanged on every SQL engine, because it uses only standard keywords.
  • VALUES column names are the same on every engine, so an alias list is optional in practice.