SQL CTE (WITH Clause) Explained — From Basics to Recursive Queries

1. What Is a CTE?

A CTE is a named subquery defined at the top of a statement, before the main query runs. Its defining traits:

• Scoped to one statement. A CTE exists only for the duration of the SELECT, INSERT, UPDATE, or DELETE that follows it. The next statement in your session cannot see it.
• Named. Unlike an anonymous subquery, a CTE has an identifier you choose, so the query explains itself: top_customers, monthly_revenue, org_path.
• Referenceable like a table. You can select from it, join it to real tables, join two CTEs together, and (with WITH RECURSIVE) have it reference itself.
• Not stored. A CTE is not a schema object. It is not indexed, has no statistics of its own, and vanishes when the statement finishes. That is the key trade-off against temp tables and views, which we cover in section 6.

The SQL standard introduced CTEs in SQL:1999, and the recursive variant is what made them genuinely powerful — it is the only standard way to walk hierarchical or graph-like data (org charts, category trees, bill-of-materials) inside a single query.

2. Basic CTE Syntax

The shape is always the same: WITH, a name, an optional column list, AS, a parenthesized subquery, then the main statement.

WITH high_value_orders AS (
    SELECT user_id, order_id, total
    FROM orders
    WHERE total > 1000
      AND status = 'completed'
)
SELECT u.name, COUNT(h.order_id) AS big_orders, SUM(h.total) AS revenue
FROM users u
JOIN high_value_orders h ON h.user_id = u.id
GROUP BY u.name
ORDER BY revenue DESC
LIMIT 10;

Two syntax details worth knowing:

• You can name the columns explicitly: WITH cte (col_a, col_b) AS (SELECT ...). Useful when the inner query produces computed columns with awkward auto-names, and required in some recursive patterns.
• The CTE is visible to any statement that follows WITH — not just SELECT. For example, PostgreSQL lets you write INSERT INTO archive SELECT * FROM cte, and UPDATE ... WHERE id IN (SELECT id FROM cte) works everywhere.

3. CTE vs Nested Subquery — the Readability Case

CTEs and subqueries can express the same logic. The difference is the order a human reads them. Subqueries nest inside-out: you hit the innermost query last but must understand it first. CTEs stack top-to-bottom: each named step is defined before it is used.

Here is the same analytical question both ways — "For each department, compare every employee's salary against that department's average, but only for departments that spend more than $500k on salaries."

Version A — nested subqueries

SELECT e.name,
       e.department_id,
       e.salary,
       d.avg_salary,
       e.salary - d.avg_salary AS diff
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary, SUM(salary) AS total_payroll
    FROM employees
    GROUP BY department_id
    HAVING SUM(salary) > 500000
) d ON d.department_id = e.department_id
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
)
ORDER BY diff DESC;

Version B — the same query with CTEs

WITH dept_stats AS (
    SELECT department_id,
           AVG(salary) AS avg_salary,
           SUM(salary) AS total_payroll
    FROM employees
    GROUP BY department_id
    HAVING SUM(salary) > 500000
),
above_average_employees AS (
    SELECT e.*
    FROM employees e
    JOIN dept_stats d ON d.department_id = e.department_id
    WHERE e.salary > d.avg_salary
)
SELECT a.name,
       a.department_id,
       a.salary,
       d.avg_salary,
       a.salary - d.avg_salary AS diff
FROM above_average_employees a
JOIN dept_stats d ON d.department_id = a.department_id
ORDER BY diff DESC;

Same semantics, but version B has three properties version A lacks:

• Each step has a name. dept_stats and above_average_employees document intent. In version A, the correlated subquery in WHERE recomputes the department average a second time and is easy to misread.
• No duplication. The department average is computed once and referenced twice. The correlated-subquery version repeats the aggregation logic, which is both a readability and (potentially) a performance problem.
• Easy to edit. Want to raise the payroll threshold to $1M? In version B you change one line in one named block. In version A you must hunt through nesting levels to find every place the logic appears.

Performance-wise, the two are usually equivalent — optimizers flatten both into the same plan (see section 7 for the important exceptions). Choose CTEs for the human reading the query at 2 a.m. during an incident.

4. Chaining Multiple CTEs

A single WITH clause can define several CTEs separated by commas, and each one may reference any CTE defined before it. This turns a monster query into a named pipeline — each stage does one thing, and the final SELECT just assembles the result.

WITH
-- Stage 1: monthly revenue per product category
monthly_revenue AS (
    SELECT DATE_TRUNC('month', o.created_at) AS month,
           p.category,
           SUM(o.total) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.status = 'completed'
    GROUP BY 1, 2
),
-- Stage 2: previous month's revenue for each row (self-join on stage 1)
with_previous AS (
    SELECT mr.month,
           mr.category,
           mr.revenue,
           LAG(mr.revenue) OVER (
               PARTITION BY mr.category ORDER BY mr.month
           ) AS prev_revenue
    FROM monthly_revenue mr
),
-- Stage 3: month-over-month growth
growth AS (
    SELECT month,
           category,
           revenue,
           ROUND((revenue - prev_revenue) / NULLIF(prev_revenue, 0) * 100, 1)
               AS growth_pct
    FROM with_previous
    WHERE prev_revenue IS NOT NULL
)
-- Final: report the 5 biggest surges
SELECT * FROM growth
ORDER BY growth_pct DESC
LIMIT 5;

Rules that govern the chain:

• Forward references only. CTE #3 can use #1 and #2; #1 cannot use #2. Some engines (SQL Server) allow mutual recursion, but MySQL and PostgreSQL require strict top-down ordering for non-recursive CTEs.
• One WITH keyword. A frequent mistake is writing WITH a AS (...), WITH b AS (...). The comma separates CTEs; WITH appears once.
• Chained CTEs are not automatically reused. If two later CTEs both reference monthly_revenue, the optimizer decides whether to compute it once or twice. Do not assume shared work — check the plan with EXPLAIN.

5. Recursive CTEs: Sequences and Trees

A recursive CTE references itself. It is the SQL answer to "give me all descendants" or "generate a series" — problems that previously required stored procedures or application-side loops.

The anatomy

Every recursive CTE has exactly two parts joined by UNION ALL (or UNION):

1. Anchor member — a normal query that produces the starting rows. Runs once.
2. Recursive member — a query that references the CTE itself. On each iteration it sees only the rows produced by the previous iteration (the "working table"), and its output becomes the input for the next iteration. Recursion stops when an iteration produces zero rows.

WITH RECURSIVE cte_name (columns) AS (
    -- anchor: SELECT ... (no reference to cte_name)
    UNION ALL
    -- recursive member: SELECT ... FROM cte_name JOIN base_table ...
    -- must eventually produce zero rows, or it runs forever
)
SELECT * FROM cte_name;

In PostgreSQL and MySQL 8.0+, you must write the RECURSIVE keyword even for a single self-referencing CTE. (SQL Server infers it and has no such keyword — a classic portability trap.)

Example 1 — generate a number sequence

The simplest recursion: count from 1 to 10. Useful in practice for filling gaps in time-series reports or generating test rows.

WITH RECURSIVE numbers (n) AS (
    -- Anchor: start at 1
    SELECT 1
    UNION ALL
    -- Recursive step: take previous iteration's rows, add 1, stop past 10
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

Output: 1, 2, 3, ..., 10. Here is exactly how one engine executes it: the anchor puts {1} into the working table. Iteration 1 runs the recursive member against {1}, passes the n < 10 filter, and emits {2} — which becomes both output and the input for iteration 2. This continues until an iteration reads {10}: the filter 10 < 10 is false, the member emits zero rows, and the recursion terminates. The final result is the anchor plus every iteration's output. In practice this pattern generates date spines too — join the sequence against a start date (SELECT DATE '2026-09-01' + (n - 1) in PostgreSQL, DATE_ADD('2026-09-01', INTERVAL n-1 DAY) in MySQL) to fill gaps in time-series reports.

Example 2 — walk an org chart (hierarchy traversal)

The canonical real-world use. Given an employees table where each row has a manager_id pointing to another row, find everyone reporting under one executive, at any depth, with their level in the tree:

-- Setup (runnable as-is in MySQL 8+ and PostgreSQL)
CREATE TABLE employees (
    id          INT PRIMARY KEY,
    name        VARCHAR(50),
    manager_id  INT NULL          -- NULL means top of the tree
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1,  'Alice (CEO)',    NULL),
    (2,  'Bob (CTO)',      1),
    (3,  'Carol (CFO)',    1),
    (4,  'Dave (Eng Mgr)', 2),
    (5,  'Erin (Dev)',     4),
    (6,  'Frank (Dev)',    4),
    (7,  'Grace (SRE)',    2),
    (8,  'Heidi (Fin)',    3),
    (9,  'Ivan (Analyst)', 8);

-- Walk the whole tree from the CEO downward
WITH RECURSIVE org_tree (id, name, manager_id, level, path) AS (
    -- Anchor: the root(s) of the hierarchy
    SELECT id, name, manager_id, 0 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: find direct reports of the previous iteration's rows
    SELECT e.id, e.name, e.manager_id,
           t.level + 1,
           CONCAT(t.path, ' > ', e.name)
    FROM employees e
    JOIN org_tree t ON t.id = e.manager_id
)
SELECT level, name, path
FROM org_tree
ORDER BY level, name;

Output:

level | name         | path
------+--------------+------------------------------------------
0     | Alice (CEO)  | Alice (CEO)
1     | Bob (CTO)    | Alice (CEO) > Bob (CTO)
1     | Carol (CFO)  | Alice (CEO) > Carol (CFO)
2     | Dave (Eng)   | Alice (CEO) > Bob (CTO) > Dave (Eng Mgr)
2     | Grace (SRE)  | Alice (CEO) > Bob (CTO) > Grace (SRE)
2     | Heidi (Fin)  | Alice (CEO) > Carol (CFO) > Heidi (Fin)
3     | Erin (Dev)   | ... > Dave (Eng Mgr) > Erin (Dev)
3     | Frank (Dev)  | ... > Dave (Eng Mgr) > Frank (Dev)
4     | Ivan (Anlst) | ... > Heidi (Fin) > Ivan (Analyst)

Why this works: the anchor seeds the working table with the CEO. Each iteration joins employees.manager_id against the previous iteration's id, producing the next level down. When the leaf employees (Erin, Frank, Ivan) have no reports, the join returns zero rows and recursion terminates naturally. The level counter and the accumulated path string fall out for free — you can WHERE level <= 2 to cap depth, or filter path LIKE '%Bob%' to grab one subtree.

To walk upward instead (find all managers above one employee), swap the anchor and the join direction:

WITH RECURSIVE chain (id, name, manager_id) AS (
    SELECT id, name, manager_id FROM employees WHERE id = 5   -- start at Erin
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON c.manager_id = e.id                        -- hop to the manager
)
SELECT * FROM chain;

6. CTE vs Temp Table vs View — Which to Use

These three get confused because all three "name a query result." They differ in lifetime, storage, and reusability:

Practical heuristics from production work:

• Default to a CTE. If the whole thing fits in one statement, a CTE is almost always the right call — zero DDL, zero cleanup, self-documenting.
• Escalate to a temp table when an intermediate result is large (millions of rows), is needed by multiple subsequent statements, or benefits from an index. A CTE recomputed across five statements wastes more than the temp table's write cost.
• Use a view when the same logic is consumed by many people or applications and should have one canonical definition. Use a materialized view (PostgreSQL) when the query is expensive and stale-acceptable data is fine.

7. MySQL 8.0+ vs PostgreSQL: Support and Behavior

The behavioral difference that matters most is materialization:

• MySQL does not promise to materialize a CTE. The optimizer either merges it into the outer query (like a view with MERGE algorithm) or turns it into an internal temp table, and for a CTE referenced multiple times it may execute the underlying query more than once. There is no hint syntax to force materialization. If a MySQL CTE feels slow, run EXPLAIN and look for MATERIALIZED vs merged access — see our EXPLAIN tool for reading plans.
• PostgreSQL 12+ inlines a non-recursive CTE that is referenced exactly once and has no side effects — so it behaves like a subquery, letting predicates push down. Referenced twice or more, it materializes once and reuses the result. You can override either way with AS MATERIALIZED (...) or AS NOT MATERIALIZED (...). Before PostgreSQL 12, every CTE was an optimization fence: filters from the outer query could never push in, which is the source of the persistent "CTEs are slow in Postgres" folklore.

Recursive CTE execution is nearly identical in both engines: iterate, feed the previous result back in, stop on empty. MariaDB mirrors MySQL's syntax from 10.2 onward, so most MySQL 8 CTE code runs unchanged there.

8. Common CTE Pitfalls

Pitfall 1 — Assuming MySQL materializes CTEs (it doesn't)

People port PostgreSQL habits to MySQL and assume a CTE referenced three times is computed once. It may not be. MySQL's optimizer can merge non-materializable CTEs into the outer query, and merged references can cause the underlying logic to run per outer row. Symptom: a query that got slower after you "cleaned it up" into CTEs. Diagnosis: EXPLAIN FORMAT=TREE and look at whether the CTE shows up once. Fix for pathological cases: move the shared logic into a temp table, or restructure so the CTE is referenced once.

Pitfall 2 — Hitting the recursion depth limit in MySQL

-- MySQL: default cap is 1000 iterations
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
Try increasing CTE_MAX_RECURSION_DEPTH to a higher value.

-- Raise it for the session (max 4294967295):
SET SESSION cte_max_recursion_depth = 100000;

-- Or per-query with an optimizer hint:
WITH RECURSIVE deep_tree AS (...)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1000000) */ * FROM deep_tree;

Generating 50k date rows or walking a 2000-level category tree will fail out of the box on MySQL. The limit is a feature — it catches runaway recursions from cyclic data — but you need to know it exists before your ETL job dies at row 1001 on a Friday afternoon.

Pitfall 3 — Missing terminating condition (or cycle) in the data

PostgreSQL has no depth cap, so a manager_id cycle (A→B→A) loops forever, eating memory until you cancel. Defenses: keep a level counter and cap it (WHERE level < 50), track visited ids in an array (WHERE NOT id = ANY(path_ids)), or use UNION instead of UNION ALL so duplicates terminate the walk.

Pitfall 4 — Referencing a CTE from the next statement

WITH recent_orders AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 7 DAY)
SELECT COUNT(*) FROM recent_orders;

SELECT * FROM recent_orders;  -- ERROR: table doesn't exist.
                              -- The CTE died with the previous statement.

If you need the result across statements, that is a temp table job (section 6).

Pitfall 5 — Forgetting the RECURSIVE keyword, or writing it where it isn't needed

MySQL and PostgreSQL both reject a self-referencing CTE that lacks WITH RECURSIVE, while SQL Server rejects WITH RECURSIVE because it doesn't have the keyword at all. Also note: WITH RECURSIVE applies to the whole WITH clause in Postgres/MySQL, so mixing one recursive and several plain CTEs under a single RECURSIVE keyword is legal — the non-recursive ones are simply unaffected.

Pitfall 6 — Column count mismatch between anchor and recursive member

The recursive member's SELECT must produce exactly the same number of columns, in compatible types, as the anchor. The CTE's column names always come from the anchor (or the explicit column list). If you add a column to the anchor during refactoring, update the recursive member too — the error message ("Each UNION clause must have the same number of columns") does not tell you which member drifted.

FAQ — SQL CTEs