Why prefer a CTE over repeating a derived table when one query needs the same subresult twice?

## The situation Many real queries need the same intermediate result in two places: compare each row's value with the maximum of the same aggregate, join a summary back to itself for a period-over-period comparison, or filter one summary by a number computed from that same summary. The question is how you express "the same subresult, twice" in one statement. ## Why a derived table forces duplication A derived table is a `FROM` item with a correlation name, and that name is local to the query block containing it. There is no way for a different query block — a scalar subquery in `WHERE`, another `FROM` item, a `HAVING` predicate — to say "give me that thing again." The only option is to write the subquery a second time: ```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 = (SELECT MAX(x.headcount) FROM (SELECT dept_id, COUNT(*) AS headcount FROM employee GROUP BY dept_id) AS x); ``` The two copies are now a maintenance liability. When the definition of "headcount" changes — exclude terminated employees, count only full-time staff, add a `WHERE` on a date — whoever edits the query has to remember there are two of them. Editing one and not the other does not raise an error; it silently produces a query whose inner and outer notions of the same concept disagree. That class of bug is hard to spot in review because the two blocks are far apart on screen. ## What the CTE gives you ```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 = (SELECT MAX(headcount) FROM dept_size); ``` The `WITH` name is in scope for the entire statement, so both references resolve to the same text. There is exactly one place to change the definition. The name also carries meaning: a reader sees `dept_size` and knows what the subresult is without reading its body, which is not true of `AS x`. The same property enables shapes that are awkward otherwise — a self-join of the subresult, for instance: ```sql WITH monthly AS ( SELECT month_start, SUM(amount) AS total FROM sales GROUP BY month_start ) SELECT cur.month_start, cur.total - prev.total AS change FROM monthly cur JOIN monthly prev ON prev.month_start = cur.month_start - INTERVAL '1' MONTH; ``` Without the CTE you would write that aggregate out twice. ## What a CTE does not promise The most common overclaim in interviews is "a CTE is computed once, so it is faster than repeating the subquery." That is a statement about engine behaviour, and engines differ: some fold a CTE into the surrounding query like any other subquery, some evaluate it separately, and several offer hints or keywords to influence the choice — behaviour that has also changed across major versions. Nothing in the `WITH` keyword itself guarantees single evaluation. If you claim it, an interviewer will ask which engine and which version, and the honest answer is that you would check the plan. The portable, language-level claims are safe and sufficient: one definition instead of two, a name that documents intent, and the ability to reference the subresult anywhere in the statement including more than once. ## When a derived table is still the right call A `WITH` clause is not automatically better. A short subquery used exactly once, in one block, often reads better inline — the definition sits right where it is used, and the reader's eye does not have to travel to the top of the statement and back. A stack of five CTEs each used once can be harder to follow than the nested form it replaced, because the reader must hold five names in their head to understand the final `SELECT`. Reach for the CTE when the subresult is used more than once, when the nesting depth has become genuinely hard to read, or when naming the step adds real information. ## Scope reminder All of this stays inside one statement. If a *second statement* needs the same subresult, neither construct helps — the CTE name is gone once the statement ends, and the derived table never had a name to begin with. That is the case where a stored object such as a view becomes the right answer. ## Common mistakes Asserting single evaluation as a guarantee; converting every subquery to a CTE reflexively and producing a wall of single-use names; and, in the duplicated-derived-table form, editing one copy of the subquery and leaving the other behind.