Common Table Expressions (CTEs) — SQL Tutorial with Examples

What You'll Learn

A CTE is a named temporary result set defined with the WITH clause before the main query. CTEs improve readability by breaking complex queries into named steps. Multiple CTEs can be chained. Recursive CTEs can traverse hierarchies like org charts or category trees.

Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

-- Multiple CTEs
WITH cte1 AS (...), cte2 AS (...)
SELECT ...;

Example

-- Basic CTE
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT department_id, COUNT(*) AS headcount
FROM high_earners
GROUP BY department_id;

-- Chained CTEs (MySQL syntax; repeat the expression in
-- GROUP BY for portable SQL — see Topic 9)
WITH
monthly_rev AS (
  SELECT DATE_FORMAT(order_date,'%Y-%m') AS mo,
         SUM(total) AS rev
  FROM orders GROUP BY mo
),
avg_rev AS (
  SELECT AVG(rev) AS avg_val FROM monthly_rev
)
SELECT mo, rev FROM monthly_rev, avg_rev
WHERE rev > avg_val;

-- Recursive CTE: number series 1-10
WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

Beyond the Basics

Data in play — reference tables for this topic

-- employees — manager_id added in Topic 15
-- id | name    | department_id | manager_id | salary
-- 1  | Ada     | 1             | NULL       | 145000
-- 2  | Grace   | 1             | 1          | 115000
-- 3  | Alan    | 2             | 1          | 92000
-- 4  | Edsger  | NULL          | 2          | 78000
-- 5  | Barbara | 3             | 3          | 64000

-- orders (from Topic 7)
-- id | total  | order_date
-- 1  | 240.00 | 2024-01-15
-- 2  | 89.90  | 2024-02-03
-- 3  | 430.00 | 2024-03-11
-- 4  | 59.50  | 2024-04-02
-- 5  | 120.00 | 2024-05-20

Named steps: Topic 16's derived table, made readable

The derived table in Topic 16 and a CTE are the same query with different punctuation — but the CTE version reads top-to-bottom like a recipe, and each step gets a name that documents intent. In interviews, 'I'd use a CTE to name each stage' signals maintainable thinking.

WITH dept_stats AS (
  SELECT department_id AS dept,
         AVG(salary)   AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT dept, avg_sal
FROM dept_stats
WHERE avg_sal > 75000;
-- Same three groups as Topic 9 — including Edsger's NULL group

A CTE is a subquery you can name, reuse within one statement, and chain — readability is the feature, not a side effect.

CTEs are not (usually) materialized

A CTE looks like a temp table but usually isn't: PostgreSQL 12+ inlines simple CTEs, re-expanding them everywhere they're referenced; MySQL 8 materializes once per statement; SQL Server always inlines. Reference the same CTE twice in PostgreSQL and the underlying scan may run twice.

-- Referenced twice → possibly two full scans (PostgreSQL):
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT ...
FROM high_earners h1 JOIN high_earners h2 ON ...

-- Force one evaluation (PostgreSQL 12+):
WITH high_earners AS MATERIALIZED (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT ...;

CTE = readability, not caching. When a step is expensive and referenced more than once, reach for MATERIALIZED (PostgreSQL) or a real temp table.

Recursive CTE: the org chart ShopCo was missing

Topic 15 gave employees a manager_id; a recursive CTE is what turns that column into an org chart. Two halves: an anchor (rows to start from — Ada, whose manager_id is NULL) and a recursive member that joins the CTE to itself, one level at a time, until it adds no more rows.

WITH RECURSIVE org AS (
  -- Anchor: the CEO (no manager)
  SELECT id, name, manager_id, 1 AS lvl
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- Recursive: everyone who reports to someone already in org
  SELECT e.id, e.name, e.manager_id, o.lvl + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT name, lvl FROM org;
-- Ada(1) → Grace(2), Alan(2) → Edsger(3), Barbara(3)

Anchor + recursive step + termination (no new rows) — the same three-part shape that walks any hierarchy: org charts, category trees, bill-of-materials.

Common Mistakes

Assuming CTEs are always materialized (cached). Many databases inline CTEs, re-running the query each time it's referenced. For truly expensive reusable results, use a temporary table. Also: CTEs cannot be indexed directly.

Interview Tips

CTEs make complex queries readable — interviewers appreciate clean, well-structured SQL. Recursive CTEs are a powerful interview topic for org chart traversal, bill-of-materials, and date series generation.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. What is a CTE fundamentally?

2. Are CTEs materialized (cached) by default?

3. A recursive CTE needs which parts?

4. The ShopCo org chart CTE (seed: manager_id IS NULL) produces which levels?

5. CTE vs temp table vs view — which needs the database to store anything?

6. Why use UNION instead of UNION ALL in some recursive CTEs?

Practice

Using a CTE, return each department_id whose average salary is above the company average (compute the company average once inside the WITH clause).

⚡ Solve it in the SQL playground →

Frequently Asked Questions

CTE vs temp table vs view — when do I use which?

CTE: named steps inside one statement. Temp table: intermediate results reused across statements or needed indexable — it costs storage and lifetime management. View: a named query saved permanently in the schema (Topic 19). Escalate only when the simpler tool measurably fails.

Why would a recursive CTE loop forever?

When the recursive step keeps producing rows — typically a cycle in the data (A manages B manages A) or a missing termination condition. UNION instead of UNION ALL stops on duplicates, and cycles need explicit cycle detection (or a depth column capped by WHERE). Always test recursion against cyclic data.