What does a WITH clause define in SQL, and how long does the named result live?
answer
- names a subquery for reuse
- lives at the front of a statement
- one keyword, commas for the rest
- nothing is stored in the catalog
- the name dies with the statement
basics
~20 sWITH defines one or more named subqueries, called common table expressions, that the statement following it can reference by name as if they were tables. The names exist only for that single statement and are never stored.
solid answer
~50 sA `WITH` clause introduces one or more **common table expressions**: each is a name bound to a parenthesised query, and inside the statement that follows, that name can be used anywhere a table name can. The shape is `WITH name AS (SELECT ...) SELECT ... FROM name`. Several CTEs go in one clause separated by commas — you write `WITH` once, not once per CTE. The binding lasts exactly one statement: when it ends the name is gone, nothing was created in the catalog, and the next statement cannot refer to it. That is the difference from a view (a stored object) and from a temporary table (a session object you create and drop). Authors reach for `WITH` to give the steps of a multi-step query readable names and to read the query top-down instead of inside-out.
code
sql · 10 linesWITH paid_orders AS (
SELECT customer_id, total
FROM orders
WHERE status = 'PAID'
)
SELECT customer_id, SUM(total) AS lifetime_value
FROM paid_orders
GROUP BY customer_id;
-- paid_orders is unknown to any following statementgo deeper
Be ready to write a WITH clause from memory and say plainly that the name is usable only inside that one statement and creates nothing in the database.
Explain the exact syntax rules: one WITH keyword, commas between definitions, no semicolon before the main statement, and CTE names shadowing same-named tables.
Show the judgment call: when naming a step with a CTE makes a query reviewable, and when a CTE used once that reads worse than an inline subquery is just ceremony.
Own the team convention — when a repeated CTE should become a view or a modelled table instead of being pasted into every query, and how that choice affects maintenance.
## What a WITH clause is A `WITH` clause attaches one or more named subqueries to the front of a single SQL statement. Each named subquery is a **common table expression** (CTE). Inside the statement that follows, the CTE name can appear anywhere a table name can appear: in `FROM`, in a join, or inside another subquery. ```sql WITH paid_orders AS ( SELECT customer_id, total FROM orders WHERE status = 'PAID' ) SELECT customer_id, SUM(total) AS lifetime_value FROM paid_orders GROUP BY customer_id; ``` You read it top-down: first define what "paid orders" means, then use it. ## The syntax, precisely The clause is the keyword `WITH`, then a comma-separated list of definitions, then the statement. Each definition is a name, an optional parenthesised column list, the keyword `AS`, and a parenthesised query. The most common beginner error is repeating the keyword: `WITH a AS (...) WITH b AS (...)` is invalid; the second and later CTEs are introduced by a comma, `WITH a AS (...), b AS (...)`. Nothing separates the clause from the statement — no semicolon in between. The `WITH` clause and the `SELECT` (or `INSERT`, `UPDATE`, `DELETE`, where the engine allows it) form **one** statement. ## Scope: exactly one statement This is what interviewers actually probe. A CTE name is a lexical binding that lives for the duration of the statement it prefixes. Consequences: - You cannot define a CTE in one statement and select from it in the next; the second statement raises an unknown-table error. - There is no `DROP` and no cleanup. Nothing was added to the catalog, so nothing has to be removed, and two sessions running the same CTE name never collide. - The name is not visible to a different session or a different transaction. If the CTE name matches a real table, the CTE wins inside that statement — the base table is shadowed. That is convenient for prototyping a filtered replacement, and a trap if you shadow a table by accident. ## What it is not A CTE is **not** a view: a view is a schema object, created once, reusable by everyone, and it survives statements. A CTE is **not** a temporary table: there is no session lifetime and nothing you create or drop. A CTE is also not, by itself, an instruction about how the engine should evaluate the query; the `WITH` keyword expresses *what* you mean, and how the statement is executed is the engine's decision. ## Why it earns its place The practical value is naming. A multi-step query written with nested subqueries has to be read from the innermost parenthesis outward, and every step is anonymous. The same logic written as stacked CTEs reads in the order the work happens, each step carries a name that says what it is (`paid_orders`, `monthly_totals`, `ranked`), and reviewers can reason about one step at a time. In code review that difference is the whole argument for the clause. ## Portability Non-recursive CTEs are part of standard SQL and are widely available; some older engine versions lacked them entirely, so a very old code base you inherit may use derived tables everywhere for that reason rather than by choice.
- If a CTE has the same name as an existing table, which one does the statement read?The CTE. Inside the statement, the WITH name is resolved first and shadows the base table of the same name. It is handy for swapping in a filtered stand-in while testing, but accidental shadowing is confusing to review, so pick distinctive CTE names in production code.
- What happens if two CTEs in the same WITH clause share a name?It is an error — the names in one WITH list must be distinct, just as two tables in a FROM clause cannot share an alias. There is no shadowing between them and no last-one-wins rule to rely on.
- Do you need to clean up a CTE after the statement runs?No. Nothing was created: the name is a binding inside the statement's text, not an object in the catalog. There is nothing to drop, nothing to grant on, and no chance that a concurrent session sees or collides with it.
It is a local variable for a query: you name an intermediate result so the rest of the statement can talk about it, and the name goes out of scope the moment the statement finishes.
saying these in an interview costs you the question
- Says WITH creates a temporary table you can reuse later
- Thinks a CTE persists for the session or transaction
- Repeats the WITH keyword for each additional CTE
- Claims a CTE must be dropped after use
- Calls a CTE a view stored in the database