skip to content

What does the optional column list in WITH totals (mth, revenue) AS (...) do?

level: middleimportance: nice to knowfreq 30%

answer

  1. sits between the name and AS
  2. gives the step an output contract
  3. matched by position, not by alias
  4. count must match the select list
  5. handy when columns are expressions

basics

~20 s

It renames the CTE's output columns positionally, so the CTE exposes those names instead of whatever the inner SELECT produced. The list must name exactly as many columns as the query returns, or the statement fails.

solid answer

~50 s

After the CTE name you may write a parenthesised list of column names: `WITH totals (mth, revenue) AS (SELECT ..., SUM(amount) FROM ...)`. Those names replace the inner query's output names positionally, and they are the only names the rest of the statement can use — the inner aliases are no longer visible from outside. It solves two real problems: naming expression columns that would otherwise have engine-chosen or duplicate names, and documenting a CTE's contract at the top where a reader sees it before the body. The arity must match exactly; a list with fewer or more names than the query's select list is a compile error, which is a useful guard when someone later adds a column to the CTE body. It is optional — if the inner query already aliases every column clearly, skip it.

code

sql · 11 lines
sql
WITH monthly (yr, mth, revenue) AS (
    SELECT EXTRACT(YEAR FROM order_date),
           EXTRACT(MONTH FROM order_date),
           SUM(total)
    FROM orders
    GROUP BY EXTRACT(YEAR FROM order_date),
             EXTRACT(MONTH FROM order_date)
)
SELECT yr, mth, revenue
FROM monthly
WHERE revenue > 10000;

go deeper

for a junior

Recognise the parenthesised list after a CTE name and know it renames the output columns; aliasing columns in the inner SELECT achieves the same thing.

for a middle

Explain that matching is positional and arity must be exact, and give a case where the header is the cleaner option, such as unaliased aggregates or duplicate column names.

for a senior

Argue the convention: a header acts as a step contract that fails loudly when the body's shape changes, but duplicating good inner aliases is a maintenance cost.

for a principal

Decide the house style for analytics SQL so reviewers see consistent, self-documenting steps rather than a mix of header and inline aliasing in the same query.

## The syntax slot A CTE definition is `name [ (column_list) ] AS ( query )`. The bracketed part is optional: ```sql WITH monthly (yr, mth, revenue) AS ( SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), SUM(total) FROM orders GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) ) SELECT yr, mth, revenue FROM monthly WHERE revenue > 10000; ``` The inner query aliases nothing. Without the column list you could not reliably write `WHERE revenue > 10000` — the third column would carry whatever name the engine gives `SUM(total)`, and engines disagree about that. The list gives the CTE a stable, documented output contract. ## Positional, total, and exact Three properties matter: 1. **Positional.** Names are matched to the select list by position, not by any correspondence with inner aliases. If you reorder the inner select list without touching the column list, the query still compiles and every column silently means something else — a genuine hazard worth knowing about. 2. **All or nothing.** You cannot rename only the third column; if you supply a list, you supply a name for every output column. 3. **Exact arity.** Too few or too many names is a compile-time error. That is a feature: if a colleague adds a column to the CTE body and forgets the header, the statement fails loudly instead of quietly changing shape. ## When it earns its place - **Expression columns.** Aggregates, arithmetic, `CASE` results and function calls have no natural name. Naming them once in the header keeps the body free of a wall of `AS` clauses. - **Duplicate names.** A CTE that selects `a.id` and `b.id` produces two columns called `id`, and an outer reference to `id` is ambiguous. A column list forces distinct names such as `(order_id, customer_id)`. - **Readability as documentation.** The header tells a reviewer what the step yields before they read how it is computed, in the way a function signature does. - **Recursive CTEs.** Many authors always write the column list on a recursive CTE because the anchor and recursive members must agree on shape, and the header states that shape once. ## When to skip it If the inner query already aliases each column with a good name, adding a header duplicates that information and creates a second place to keep in sync. Most everyday CTEs are clearer with `AS` aliases in the body and no column list. Choose one convention per CTE rather than mixing both — when both are present, only the header names are visible from outside, so an inner alias that disagrees with the header is misleading to whoever reads it next. ## Scope of the new names The column list renames the CTE's output for anything that references the CTE: later CTEs in the same `WITH` list, and the main statement. It does not affect anything inside the CTE body — an `ORDER BY` or `HAVING` within the body still uses the body's own names. And, like the CTE name itself, the column names vanish when the statement ends.

  • If the body aliases a column and the header names it differently, which name wins outside the CTE?
    The header. The column list replaces the output names positionally, so the rest of the statement sees only the header names and the inner alias becomes invisible. Because that is confusing to read, pick one mechanism per CTE rather than both.
  • Why does a column list help when a CTE joins two tables that both have an id column?
    The CTE would otherwise expose two output columns named id, and any outer reference to id is ambiguous. A header such as (order_id, customer_id) renames them positionally and makes the outer query unambiguous without editing the join.

saying these in an interview costs you the question

  • Thinks the column list matches names, not positions
  • Believes you can rename just one column
  • Says a shorter list simply drops trailing columns
  • Claims the header names are visible inside the CTE body
  • Treats the list as declaring column data types

context

resolving links…