{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"common-table-expressions","title":"CTEs: naming the steps","url":"https://addaly.com/learn/data-and-sql/common-table-expressions","position":21,"of":66,"minutes":7,"answer":"A CTE names a step so a query reads in order; it is not a saved result, and since Postgres 12 it is inlined into the main query unless you write MATERIALIZED.","keyIdea":"A CTE names a step so a query reads in order; it is not a saved result, and since Postgres 12 it is inlined into the main query unless you write MATERIALIZED."},"body":{"format":"markdown","text":"## A query you can read a year later\n\nA CTE (a `WITH` clause) gives a name to an intermediate result:\n\n```sql\nWITH recent_orders AS (\n  SELECT * FROM orders\n  WHERE placed_at >= current_date - interval '90 days'\n    AND status = 'paid'\n),\nper_customer AS (\n  SELECT customer_id, count(*) AS orders, sum(amount) AS spend\n  FROM recent_orders\n  GROUP BY customer_id\n)\nSELECT c.full_name, p.orders, p.spend\nFROM per_customer p\nJOIN customers c ON c.id = p.customer_id\nWHERE p.spend > 50000\nORDER BY p.spend DESC;\n```\n\nRead it top to bottom: take recent paid orders, summarise them per customer, attach names, keep the big spenders. Four sentences, in the order a person would say them. The same logic as nested subqueries is the same length and takes five times as long to understand, because you have to read it inside out.\n\nLater CTEs can reference earlier ones, which is what makes them compose. This is the single biggest readability improvement available in SQL, and it costs nothing.\n\n## What a CTE is not\n\nIt is not a variable, and it is not stored. It exists for the duration of the one statement. Running the query again recomputes everything.\n\nIt is also **not necessarily evaluated once**. Older Postgres treated every CTE as an optimisation fence: always materialised, always exactly once, never merged into the outer query. Since version 12 the planner may inline a CTE that is referenced once, which is usually faster and occasionally changes behaviour you were relying on. You can force either:\n\n```sql\nWITH heavy AS MATERIALIZED ( ... )      -- compute once, keep the result\nWITH light AS NOT MATERIALIZED ( ... )  -- inline it into the outer query\n```\n\nReach for `MATERIALIZED` when a CTE is expensive and used several times. Reach for `NOT MATERIALIZED` when a CTE selects from a large table and the outer query has a filter that could have used an index: materialising it first would read the whole table before filtering.\n\nOther engines differ. MySQL 8 and SQLite support CTEs; older MySQL does not have them at all, and derived tables in the FROM clause are the portable fallback.\n\n## Recursive CTEs\n\n`WITH RECURSIVE` builds a result from itself, which is how you walk a tree or generate a series:\n\n```sql\nWITH RECURSIVE months AS (\n  SELECT date '2026-01-01' AS m\n  UNION ALL\n  SELECT (m + interval '1 month')::date FROM months WHERE m < date '2026-12-01'\n)\nSELECT * FROM months;\n```\n\nAn anchor row, a rule for the next row, and a stopping condition. Leave out the stopping condition and it runs forever, or until the server runs out of memory, which is the same thing from your point of view.\n\nIn Postgres, `generate_series` does this particular job better and the next lesson uses it. Recursion earns its place on genuine hierarchies: an org chart, a category tree, a bill of materials, the reply chain of a comment thread.\n\n## CTEs that write\n\nIn Postgres a CTE can contain `INSERT`, `UPDATE` or `DELETE` with a `RETURNING` clause, which makes multi-step data changes atomic:\n\n```sql\nWITH moved AS (\n  DELETE FROM orders WHERE placed_at < '2024-01-01' RETURNING *\n)\nINSERT INTO orders_archive SELECT * FROM moved;\n```\n\nThe delete and the insert happen in one statement, so there is no window where the rows exist in neither table. This is a useful pattern for archiving and for audit logging, and it is Postgres-specific.\n\nOne rule that trips people: all parts of the statement see the *same snapshot* of the data. A CTE that updates a row and a later part of the same statement that reads that row will not see the update. Two statements in a transaction, not one statement with two CTEs, when you need the second step to see the first.\n\n## When a CTE is the wrong tool\n\nTwelve CTEs chained into one enormous statement is not more readable than four, it is a program written in the wrong language. When a query reaches that size, the pieces usually deserve to be **views**: named, reusable, documented, testable on their own. A view that ten queries share is one definition to fix when the business rule changes; the same logic copied into ten CTEs is ten places to forget.\n\nThe rule of thumb: a CTE is for structuring one query. A view is for a definition that more than one query needs."},"check":{"question":"A query beginning `WITH recent AS (SELECT * FROM events) SELECT ... FROM recent WHERE day = '2026-03-14'` took 40 seconds on Postgres 11 and under a second on Postgres 16 against the same data and indexes. What changed?","options":[{"text":"Newer versions store CTE results in memory rather than on disk, so the whole events table is held in RAM and the temporary file writes that dominated the old run are avoided.","correct":false,"tempting":"Materialised CTEs spill to disk in both versions; the change was in whether they are materialised at all."},{"text":"The newer version added an index on the events table automatically during query planning.","correct":false,"tempting":"Databases do not create indexes as a side effect of planning a query."},{"text":"Older versions always materialised the CTE, reading the whole table before filtering; newer ones can inline it so the day filter applies during the scan.","correct":true},{"text":"The older version evaluated the CTE once per output row, and the newer one evaluates it once.","correct":false,"tempting":"Reverses the historic behaviour: the old rule was exactly-once materialisation, which is why it was called an optimisation fence."}]},"prev":{"slug":"subqueries-and-exists","title":"Subqueries: a query inside a query","url":"https://addaly.com/learn/data-and-sql/subqueries-and-exists"},"next":{"slug":"a-spine-to-join-against","title":"Generating the rows that are not there","url":"https://addaly.com/learn/data-and-sql/a-spine-to-join-against"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'CTEs: naming the steps', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/common-table-expressions (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"CTEs: naming the steps","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"A CTE names a step so a query reads in order; it is not a saved result, and since Postgres 12 it is inlined into the main query unless you write MATERIALIZED.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"a-query-you-can-read-a-year-later","runs":[{"text":"A query you can read a year later"}]},{"t":"p","runs":[{"text":"A CTE (a "},{"text":"WITH","code":true},{"text":" clause) gives a name to an intermediate result:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH recent_orders AS (\n  SELECT * FROM orders\n  WHERE placed_at >= current_date - interval '90 days'\n    AND status = 'paid'\n),\nper_customer AS (\n  SELECT customer_id, count(*) AS orders, sum(amount) AS spend\n  FROM recent_orders\n  GROUP BY customer_id\n)\nSELECT c.full_name, p.orders, p.spend\nFROM per_customer p\nJOIN customers c ON c.id = p.customer_id\nWHERE p.spend > 50000\nORDER BY p.spend DESC;"},{"t":"p","runs":[{"text":"Read it top to bottom: take recent paid orders, summarise them per customer, attach names, keep the big spenders. Four sentences, in the order a person would say them. The same logic as nested subqueries is the same length and takes five times as long to understand, because you have to read it inside out."}]},{"t":"p","runs":[{"text":"Later CTEs can reference earlier ones, which is what makes them compose. This is the single biggest readability improvement available in SQL, and it costs nothing."}]},{"t":"h2","id":"what-a-cte-is-not","runs":[{"text":"What a CTE is not"}]},{"t":"p","runs":[{"text":"It is not a variable, and it is not stored. It exists for the duration of the one statement. Running the query again recomputes everything."}]},{"t":"p","runs":[{"text":"It is also "},{"text":"not necessarily evaluated once","b":true},{"text":". Older Postgres treated every CTE as an optimisation fence: always materialised, always exactly once, never merged into the outer query. Since version 12 the planner may inline a CTE that is referenced once, which is usually faster and occasionally changes behaviour you were relying on. You can force either:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH heavy AS MATERIALIZED ( ... )      -- compute once, keep the result\nWITH light AS NOT MATERIALIZED ( ... )  -- inline it into the outer query"},{"t":"p","runs":[{"text":"Reach for "},{"text":"MATERIALIZED","code":true},{"text":" when a CTE is expensive and used several times. Reach for "},{"text":"NOT MATERIALIZED","code":true},{"text":" when a CTE selects from a large table and the outer query has a filter that could have used an index: materialising it first would read the whole table before filtering."}]},{"t":"p","runs":[{"text":"Other engines differ. MySQL 8 and SQLite support CTEs; older MySQL does not have them at all, and derived tables in the FROM clause are the portable fallback."}]},{"t":"h2","id":"recursive-ctes","runs":[{"text":"Recursive CTEs"}]},{"t":"p","runs":[{"text":"WITH RECURSIVE","code":true},{"text":" builds a result from itself, which is how you walk a tree or generate a series:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH RECURSIVE months AS (\n  SELECT date '2026-01-01' AS m\n  UNION ALL\n  SELECT (m + interval '1 month')::date FROM months WHERE m < date '2026-12-01'\n)\nSELECT * FROM months;"},{"t":"p","runs":[{"text":"An anchor row, a rule for the next row, and a stopping condition. Leave out the stopping condition and it runs forever, or until the server runs out of memory, which is the same thing from your point of view."}]},{"t":"p","runs":[{"text":"In Postgres, "},{"text":"generate_series","code":true},{"text":" does this particular job better and the next lesson uses it. Recursion earns its place on genuine hierarchies: an org chart, a category tree, a bill of materials, the reply chain of a comment thread."}]},{"t":"h2","id":"ctes-that-write","runs":[{"text":"CTEs that write"}]},{"t":"p","runs":[{"text":"In Postgres a CTE can contain "},{"text":"INSERT","code":true},{"text":", "},{"text":"UPDATE","code":true},{"text":" or "},{"text":"DELETE","code":true},{"text":" with a "},{"text":"RETURNING","code":true},{"text":" clause, which makes multi-step data changes atomic:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH moved AS (\n  DELETE FROM orders WHERE placed_at < '2024-01-01' RETURNING *\n)\nINSERT INTO orders_archive SELECT * FROM moved;"},{"t":"p","runs":[{"text":"The delete and the insert happen in one statement, so there is no window where the rows exist in neither table. This is a useful pattern for archiving and for audit logging, and it is Postgres-specific."}]},{"t":"p","runs":[{"text":"One rule that trips people: all parts of the statement see the "},{"text":"same snapshot","i":true},{"text":" of the data. A CTE that updates a row and a later part of the same statement that reads that row will not see the update. Two statements in a transaction, not one statement with two CTEs, when you need the second step to see the first."}]},{"t":"h2","id":"when-a-cte-is-the-wrong-tool","runs":[{"text":"When a CTE is the wrong tool"}]},{"t":"p","runs":[{"text":"Twelve CTEs chained into one enormous statement is not more readable than four, it is a program written in the wrong language. When a query reaches that size, the pieces usually deserve to be "},{"text":"views","b":true},{"text":": named, reusable, documented, testable on their own. A view that ten queries share is one definition to fix when the business rule changes; the same logic copied into ten CTEs is ten places to forget."}]},{"t":"p","runs":[{"text":"The rule of thumb: a CTE is for structuring one query. A view is for a definition that more than one query needs."}]}],"check":{"question":[{"text":"A query beginning "},{"text":"WITH recent AS (SELECT * FROM events) SELECT ... FROM recent WHERE day = '2026-03-14'","code":true},{"text":" took 40 seconds on Postgres 11 and under a second on Postgres 16 against the same data and indexes. What changed?"}],"options":[{"text":[{"text":"Newer versions store CTE results in memory rather than on disk, so the whole events table is held in RAM and the temporary file writes that dominated the old run are avoided."}],"correct":false,"tempting":[{"text":"Materialised CTEs spill to disk in both versions; the change was in whether they are materialised at all."}]},{"text":[{"text":"The newer version added an index on the events table automatically during query planning."}],"correct":false,"tempting":[{"text":"Databases do not create indexes as a side effect of planning a query."}]},{"text":[{"text":"Older versions always materialised the CTE, reading the whole table before filtering; newer ones can inline it so the day filter applies during the scan."}],"correct":true,"tempting":null},{"text":[{"text":"The older version evaluated the CTE once per output row, and the newer one evaluates it once."}],"correct":false,"tempting":[{"text":"Reverses the historic behaviour: the old rule was exactly-once materialisation, which is why it was called an optimisation fence."}]}],"order":[3,2,0,1],"why":null},"prev":{"slug":"subqueries-and-exists","title":"Subqueries: a query inside a query","minutes":8},"next":{"slug":"a-spine-to-join-against","title":"Generating the rows that are not there","minutes":8}}}