Common Table Expressions

## What all three have in common A derived table, a common table expression (CTE) and a view all do the same basic job: they give a query result a name so that a bigger query can use it as though it were a table. In all three cases the thing being named is a `SELECT` statement, and in all three cases the outer query can join to it, filter it, group it and select columns from it exactly as it would from a base table. Because they look interchangeable in a query's `FROM` clause, interviewers ask candidates to state what actually separates them. The separating axis is **scope**: how far the name reaches, and how long it lives. ## Derived table A derived table is a subquery written directly in the `FROM` clause: ```sql SELECT d.dept_id, d.headcount FROM (SELECT dept_id, COUNT(*) AS headcount FROM employee GROUP BY dept_id) AS d WHERE d.headcount > 10; ``` Standard SQL requires the derived table to be given a correlation name — the alias `d` — and engines differ only in how strictly they enforce that. The alias is visible to the query block that contains the `FROM` item: that block's join conditions, `WHERE`, `GROUP BY`, `HAVING` and select list. It is not visible anywhere else. If a second `FROM` item, or a subquery elsewhere in the statement, needs the same result, you must write the subquery text out a second time. Nothing survives the statement; there is no catalog entry and no name to reuse. ## Common table expression A CTE is a subquery given a name in a `WITH` clause attached to a single statement: ```sql WITH dept_size AS ( SELECT dept_id, COUNT(*) AS headcount FROM employee GROUP BY dept_id ) SELECT dept_id, headcount FROM dept_size WHERE headcount > 10; ``` `WITH` has been in the SQL standard since SQL:1999. Several CTEs can be listed, separated by commas, and each may reference the ones defined before it — which is why stacked CTEs read like a pipeline of named steps. An optional column list renames the output: `WITH dept_size (dept_id, headcount) AS (...)`. With `WITH RECURSIVE`, a CTE may also reference itself. The important property for this comparison is that the CTE name is visible to the **whole statement** the `WITH` is attached to. Every `FROM` in the main query can use it, including more than once and including a self-join of the CTE to itself. When the statement finishes, the name is gone: the next statement in the same transaction or session cannot select from it. Nothing is written to the catalog, and no privileges or migrations are involved — a CTE is just part of the text of one statement. ## View A view stores the query in the database under a name: ```sql CREATE VIEW dept_size AS SELECT dept_id, COUNT(*) AS headcount FROM employee GROUP BY dept_id; ``` From then on, any statement — in any session, from any application, written by anyone with access — can say `FROM dept_size`. That is the whole point of a view and the one thing neither of the other two constructs can do. The cost of that reach is that a view is a schema object: it is created and dropped with DDL, so it belongs in your migration scripts, and once other queries reference it, changing its shape becomes a coordinated change rather than an edit to one file. A view also takes no parameters; callers narrow it by adding their own predicates against it in their own `WHERE`. ## Choosing between them - Needed once, inside one query block → **derived table**. It keeps the definition next to its only use. - Needed more than once in one statement, or the nesting has become hard to follow → **CTE**. One definition, one name, referenced as often as you like. - Needed by more than one statement, report or team → **view**. One definition in the database instead of copies in several files. Naming is an underrated part of the choice. `WITH active_customer AS (...)` tells the next reader what the subresult *means*; a nested `(SELECT ...) AS t3` does not. ## What the comparison is not about Candidates often jump straight to "a CTE is materialized and a derived table is inlined." Whether an engine computes a subresult once, folds it into the surrounding query, or re-evaluates it per reference is an engine-level decision that varies by engine and by version, and it is not what the language guarantees. At the language level the guarantee is about naming and scope: a CTE gives you one definition reachable from the whole statement, a derived table gives you a definition reachable from one block, and a view gives you a definition reachable from every statement. ## Common mistakes Forgetting the alias on a derived table; expecting a CTE name to still exist in the next statement; creating a view for a subresult only one query will ever want, which adds a schema object nobody can safely delete later; and assuming that because the three read alike in `FROM`, the choice does not matter to the people who maintain the query afterwards.