Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a named, temporary result set defined with WITH at the start of a statement and usable within that statement only.

Concept

A Common Table Expression (CTE) is a named, temporary result set defined with WITH at the start of a statement and usable within that statement only. Think of it as a named subquery that makes complex SQL readable, step by step.

A recursive CTE refers to itself and is used to traverse hierarchies (org charts, bill of materials, ECU networks) and generate series.

Support: PostgreSQL, SQL Server 2005+, Oracle 9i+ (recursive from 11gR2), SQLite 3.8.3+, MySQL 8.0+, MariaDB 10.2+.

Why It Matters

Breaks complex logic into readable, named steps.

Avoids repeating the same subquery.

Enables recursion, which is otherwise impossible in plain SQL.

Widely used in analytics and data engineering (dbt models are essentially chains of CTEs).

Syntax

WITH cte_name [(col1, col2, ...)] AS (
    SELECT ...
),
another_cte AS (
    SELECT ... FROM cte_name ...
)
SELECT ...
FROM another_cte;

-- Recursive
WITH RECURSIVE cte_name AS (         -- SQL Server and Oracle: omit the RECURSIVE keyword
    anchor_query                      -- starting rows
    UNION ALL
    recursive_query                   -- references cte_name
)
SELECT * FROM cte_name;

Example

16.1 Basic CTE 16.2 Multiple CTEs (chained) Each CTE can reference those defined before it.

16.3 CTE with aggregation and comparison Departments above the company average salary:

16.4 Recursive CTE — number and date series How it executes:

Date series for filling gaps (every day of March 2026):

16.5 Recursive CTE — organization hierarchy Asha > Rahul > Priya Asha > Vikram > Meera Asha > Vikram > Neha

All subordinates (direct and indirect) of Vikram — change the anchor:

Walking up the chain (manager chain of Priya):

Safety against cycles (bad data such as A manages B and B manages A): limit depth (WHERE lvl < 20), track visited IDs in a path, use PostgreSQL 14+ CYCLE clause, or SQL Server MAXRECURSION (default 100).

16.6 CTE vs subquery Subquery / derived table High; named, top-down steps Degrades with nesting Reuse within the statement Reference the same CTE multiple times Must repeat the subquery

Usually identical (inlined). PostgreSQL < 12 always materialized CTEs (optimization fence); 12+ inlines unless MATERIALIZED is specified 16.7 CTE vs temporary table Session or transaction

Not persisted (may be materialized in memory/disk during execution) Physically created in tempdb/temp space Yes, can be indexed No separate statistics Yes (helps the optimizer) Reuse across statements

Readable logic within one query, recursion Multi-step processing, large intermediate results reused many times

WITH dept_stats AS (
    SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT d.dept_name, s.headcount, s.avg_salary
FROM dept_stats s
JOIN departments d ON d.dept_id = s.dept_id
ORDER BY s.avg_salary DESC;

WITH order_lines AS (
    SELECT o.order_id, o.customer_id, o.status,
           oi.quantity * oi.unit_price AS line_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
),
valid_lines AS (
    SELECT * FROM order_lines WHERE status <> 'CANCELLED'
),
customer_totals AS (
    SELECT customer_id, SUM(line_total) AS revenue, COUNT(DISTINCT order_id) AS n_orders
    FROM valid_lines
    GROUP BY customer_id
)
SELECT c.name, t.n_orders, t.revenue
FROM customer_totals t
JOIN customers c ON c.customer_id = t.customer_id
ORDER BY t.revenue DESC;

WITH company AS (
    SELECT AVG(salary) AS avg_sal FROM employees
),
dept AS (
    SELECT dept_id, AVG(salary) AS dept_avg FROM employees GROUP BY dept_id
)
SELECT dept.dept_id, dept.dept_avg, company.avg_sal
FROM dept CROSS JOIN company
WHERE dept.dept_avg > company.avg_sal;

-- PostgreSQL, MySQL 8, SQLite (SQL Server/Oracle: remove RECURSIVE)
WITH RECURSIVE nums (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;

Iteration 0 (anchor):   {1}
Iteration 1:            {2}      (from 1, since 1 < 5)
Iteration 2:            {3}
Iteration 3:            {4}
Iteration 4:            {5}
Iteration 5:            {}       (5 < 5 is false) → stop
Result = union of all iterations

-- PostgreSQL
WITH RECURSIVE days AS (
    SELECT DATE '2026-03-01' AS d
    UNION ALL
    SELECT d + 1 FROM days WHERE d < DATE '2026-03-31'
)
SELECT d FROM days;
-- PostgreSQL shortcut: SELECT generate_series(DATE '2026-03-01', DATE '2026-03-31', INTERVAL '1 day')::DATE;
-- SQL Server: SELECT DATEADD(day, 1, d) ... and OPTION (MAXRECURSION 400) if > 100 levels
-- SQL Server 2022+: GENERATE_SERIES(1, 31)

WITH RECURSIVE org AS (
    -- Anchor: the root (no manager)
    SELECT emp_id, first_name, manager_id, 1 AS lvl,
           CAST(first_name AS VARCHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: direct reports of the previous level
    SELECT e.emp_id, e.first_name, e.manager_id, o.lvl + 1,
           CAST(o.path || ' > ' || e.first_name AS VARCHAR(500))
    FROM employees e
    JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, first_name, lvl, path
FROM org
ORDER BY path;
-- SQL Server: drop RECURSIVE and use + for concatenation; MySQL: use CONCAT()

Asha (CTO)
├── Vikram (Senior Engineer)
│   ├── Neha
│   └── Meera
├── Rahul (Sales Manager)
│   └── Priya
├── Arjun (HR Manager)
└── Kiran (Consultant)

WITH RECURSIVE subs AS (
    SELECT emp_id, first_name FROM employees WHERE manager_id = 2
    UNION ALL
    SELECT e.emp_id, e.first_name FROM employees e JOIN subs s ON e.manager_id = s.emp_id
)
SELECT * FROM subs;     -- Neha, Meera

WITH RECURSIVE chain AS (
    SELECT emp_id, first_name, manager_id FROM employees WHERE emp_id = 5
    UNION ALL
    SELECT m.emp_id, m.first_name, m.manager_id
    FROM employees m JOIN chain c ON m.emp_id = c.manager_id
)
SELECT first_name FROM chain;    -- Priya, Rahul, Asha

Expected Result

Monthly revenue with month-over-month change, computed step by step:

(Window functions in Module 17 make this simpler with LAG.)

WITH monthly AS (
    SELECT EXTRACT(MONTH FROM order_date) AS m, SUM(total_amount) AS revenue
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY EXTRACT(MONTH FROM order_date)
)
SELECT cur.m, cur.revenue, prev.revenue AS prev_revenue,
       cur.revenue - prev.revenue AS change
FROM monthly cur
LEFT JOIN monthly prev ON prev.m = cur.m - 1
ORDER BY cur.m;

Common Mistakes

Forgetting RECURSIVE in PostgreSQL/MySQL/SQLite, or adding it in SQL Server/Oracle (error).

Recursive CTE without a termination condition (infinite loop until a limit error).

Using UNION instead of UNION ALL in recursion (slower; sometimes used intentionally to stop cycles).

Column type mismatch between anchor and recursive parts (e.g., path string grows beyond the anchor's type) — cast in the anchor.

Referencing a CTE in a later, separate statement (it no longer exists).

Putting a semicolon issue in SQL Server: the statement before WITH must end with ;.

Best Practices

Name CTEs by what they contain (active_customers, monthly_revenue).

One logical step per CTE.

Add depth limits to recursive CTEs.

Use a temp table instead when an intermediate result is large and reused by several statements.

Interview Relevance

High for mid/senior roles: "What is a CTE and why use it?", "CTE vs subquery vs temp table?", "Write a recursive CTE for an org chart", "Generate a date series".

Practice Questions

Using a CTE, list customers whose average order value exceeds 20000.

Using two CTEs, compute total revenue per category and then the category with the highest revenue.

Write a recursive CTE producing numbers 1 to 10 and their squares.

List every employee with their level in the hierarchy.

Using a recursive CTE, count how many people (directly or indirectly) report to Asha.