SQL Common Table Expressions (WITH)

A Common Table Expression (CTE) is a named, temporary result set that you define with the WITH keyword right before a query, and then reference like an ordinary table for the rest of that single statement. CTEs let you break a complicated query into small, readable, named steps instead of nesting subqueries five levels deep. They can also reference themselves, which is what makes recursive CTEs the standard SQL tool for walking hierarchical data such as org charts, category trees, or folder structures. Once you’re comfortable with subqueries and joins, CTEs are the next tool that makes real-world queries dramatically easier to write and maintain.

Overview: How Common Table Expressions Work

A CTE is created with a WITH clause placed immediately before a SELECT, INSERT, UPDATE, or DELETE statement. Inside the parentheses you write an ordinary query; its result set gets a name, and that name can then be used anywhere a table name would normally appear later in the same statement — in a FROM clause, in a JOIN, or even inside another CTE defined after it.

Conceptually, the engine treats a non-recursive CTE much like an inline view or a named subquery. In SQLite (and most engines, by default), the query planner is free to inline the CTE’s definition into the surrounding query rather than always materializing it once into a separate temporary table. This matters if a CTE is expensive and referenced more than once: it may be re-evaluated on each use. SQLite 3.35 and later let you force the behavior with AS MATERIALIZED (compute once, store the result) or AS NOT MATERIALIZED (inline every time), but the default is fine for typical queries.

A recursive CTE is different: it is a CTE that refers to itself, introduced with WITH RECURSIVE. The engine evaluates it iteratively rather than all at once: it runs a starting query (the “anchor member”), then repeatedly re-runs a second query (the “recursive member”) using only the rows produced by the previous pass, appending each new batch to the final result, until a pass produces zero new rows. This is exactly how you climb or descend a tree — an employee hierarchy, a category tree, a bill of materials — one level at a time, without knowing in advance how many levels exist.

Syntax

WITH cte_name1 AS (
  SELECT column1, column2
  FROM some_table
  WHERE ...
),
cte_name2 AS (
  SELECT ...
  FROM cte_name1   -- a CTE can reference an earlier CTE
)
SELECT ...
FROM cte_name2
JOIN some_other_table ON ...;

Recursive form:

WITH RECURSIVE cte_name AS (
  SELECT ...              -- anchor member: runs once
  UNION ALL
  SELECT ...              -- recursive member: re-runs until it returns no rows
  FROM cte_name
  WHERE ...                -- stopping condition
)
SELECT * FROM cte_name;

Part	Meaning
`WITH`	Introduces one or more CTE definitions before the main statement.
`cte_name`	The name given to the result set; used later like a table name.
`AS ( ... )`	The query that defines the CTE’s rows and columns.
`RECURSIVE`	Required keyword when a CTE refers to itself.
Anchor member	The non-recursive part of a recursive CTE; runs first, exactly once.
`UNION ALL`	Joins anchor and recursive members; must be `UNION ALL`, not `UNION`, in the recursive form.
Recursive member	References the CTE’s own name; re-runs against each new batch of rows.

Examples

Example 1: A single CTE to simplify a filter

Suppose you want every employee who earns more than their department’s average salary. Without a CTE you’d repeat the average-salary subquery inline; with a CTE you compute it once and give it a name.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Engineering', 78000),
(3, 'Carol', 'Sales', 62000),
(4, 'Dave', 'Sales', 71000),
(5, 'Eve', 'Marketing', 55000);

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary
ORDER BY e.department, e.salary DESC;

Result:

name	department	salary	avg_salary
Alice	Engineering	95000	86500
Dave	Sales	71000	66500

The CTE dept_avg computes one average per department (Engineering: 86500, Sales: 66500, Marketing: 55000). The outer query then joins each employee back to their department’s average and keeps only the rows where the employee’s own salary beats it. Eve is excluded because her salary equals — not exceeds — her department’s average (she’s the only person in Marketing).

Example 2: Multiple, chained CTEs

You can define several CTEs in one WITH clause, separated by commas, and later ones can reference earlier ones. Here the first CTE totals spending per customer, and the second ranks those totals.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL
);

INSERT INTO customers VALUES (1, 'Nora'), (2, 'Omar'), (3, 'Priya');
INSERT INTO orders VALUES
(101, 1, 250.00),
(102, 1, 125.50),
(103, 2, 300.00),
(104, 3, 80.00),
(105, 2, 45.25);

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
),
ranked_customers AS (
  SELECT customer_id, total_spent,
         RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
  FROM customer_totals
)
SELECT c.customer_name, r.total_spent, r.spend_rank
FROM ranked_customers r
JOIN customers c ON c.customer_id = r.customer_id
ORDER BY r.spend_rank;

Result:

customer_name	total_spent	spend_rank
Nora	375.5	1
Omar	345.25	2
Priya	80.0	3

customer_totals sums each customer’s orders (Nora: 250 + 125.50 = 375.50; Omar: 300 + 45.25 = 345.25; Priya: 80). ranked_customers then applies a window function, RANK(), over that already-summarized data. Splitting the work into two named steps means each piece stays simple, and you can test either CTE on its own by temporarily changing the final SELECT to read from it directly.

Example 3: A recursive CTE for a reporting hierarchy

Recursive CTEs shine when you don’t know how many levels deep a hierarchy goes. Here each staff member has a manager_id, and we want everyone’s depth in the org chart.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER
);

INSERT INTO staff VALUES
(1, 'Grace', NULL),
(2, 'Hassan', 1),
(3, 'Ines', 1),
(4, 'Jamal', 2),
(5, 'Kira', 2);

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS level
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, s.manager_id, oc.level + 1
  FROM staff s
  JOIN org_chart oc ON s.manager_id = oc.id
)
SELECT id, name, level
FROM org_chart
ORDER BY level, id;

Result:

id	name	level
1	Grace	0
2	Hassan	1
3	Ines	1
4	Jamal	2
5	Kira	2

The anchor member finds Grace, the only row with no manager, and assigns her level 0. The recursive member then joins staff back to the rows produced so far (org_chart): first pass finds Hassan and Ines (who report to Grace) at level 1, second pass finds Jamal and Kira (who report to Hassan) at level 2. A third pass finds nobody reporting to Ines, Jamal, or Kira, so recursion stops.

How It Works Step by Step (Under the Hood)

For a regular query, the logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. A CTE simply supplies rows to whichever FROM clause names it, as if it were a table or view — the rest of the pipeline is unchanged. A recursive CTE adds its own inner loop before that pipeline ever sees the CTE’s rows:

Run the anchor member once. Its result rows become the initial contents of a working set and are also copied into the final result.
Run the recursive member, but only against the rows added in the previous step (not the whole accumulated result) — in the org-chart example, pass 1 only sees Grace, so it only finds her direct reports.
Append any new rows produced to the final result, and replace the working set with just those new rows.
Repeat steps 2–3 using the newest batch as input, going one level deeper each time.
Stop as soon as a pass produces zero new rows — this is what makes an unbounded-depth hierarchy safe to query without knowing its depth in advance.
Once the CTE’s full result set exists, the outer statement runs its normal FROM/WHERE/ORDER BY pipeline against it, exactly like any other table.

Common Mistakes

Mistake 1: Forgetting the RECURSIVE keyword

A CTE that refers to itself must be introduced with WITH RECURSIVE, not just WITH. Leaving it out is one of the most common errors beginners hit:

-- WRONG: self-reference without RECURSIVE
WITH org_chart AS (
  SELECT id, name, manager_id, 0 AS level
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, s.manager_id, oc.level + 1
  FROM staff s
  JOIN org_chart oc ON s.manager_id = oc.id
)
SELECT * FROM org_chart;

SQLite rejects this because, without RECURSIVE, a CTE is not allowed to reference itself — the engine has no table named org_chart yet when it parses the recursive member. The fix is simply adding the keyword: WITH RECURSIVE org_chart AS ( ... ), as shown in Example 3.

Mistake 2: Referencing a later CTE from an earlier one

CTEs are evaluated in the order you define them, and (outside of a single recursive CTE referencing itself) each one can only see CTEs defined before it — not after:

-- WRONG: first_cte references second_cte, which isn't defined yet
WITH first_cte AS (
  SELECT * FROM second_cte
),
second_cte AS (
  SELECT 1 AS x
)
SELECT * FROM first_cte;

This fails with a “no such table: second_cte” error, because second_cte doesn’t exist yet at the point first_cte is defined. The fix is to reorder the definitions so each CTE only references ones that came earlier: define second_cte first, then first_cte AS ( SELECT * FROM second_cte ).

Best Practices

Use CTEs to name intermediate steps in a complex query — it documents intent far better than a wall of nested subqueries.
Remember a CTE’s scope is the single statement it’s attached to; you cannot reference it from a later, separate statement the way you can a view or temp table.
For recursive CTEs, always include a real stopping condition (a base case that eventually stops matching, like manager_id IS NULL) — an unbounded recursive member can run indefinitely or hit the engine’s recursion limit.
Use UNION ALL, not UNION, in recursive CTEs unless you specifically need de-duplication; UNION forces an expensive distinctness check on every iteration.
If a non-recursive CTE is referenced multiple times and is expensive to compute, consider AS MATERIALIZED so it’s computed once instead of re-inlined on each use.
Don’t reach for a CTE when a simple subquery or join is just as clear — CTEs earn their keep on genuinely multi-step logic or recursion, not every query.
Give CTEs descriptive names (dept_avg, ranked_customers) instead of generic ones like t1, so the query reads like prose.

Practice Exercises

Exercise 1: Using the employees table from Example 1, write a CTE-based query that returns each department alongside the number of employees in it and the department’s total salary cost. (Hint: one CTE grouping by department is enough — no join needed this time.)
Exercise 2: Using the customers and orders tables from Example 2, write a query with two CTEs: the first computing each customer’s average order amount, the second filtering to customers whose average order is above 100. Which customer(s) qualify?
Exercise 3: Using the staff table from Example 3, modify the recursive CTE so it also builds a text path showing each employee’s chain of managers (for example, 'Grace > Hassan > Jamal'). Hint: concatenate a text column in the recursive member using ||.

Summary

A CTE is a named, temporary result set defined with WITH and used like a table for the rest of one statement.
Multiple CTEs can be chained with commas; later CTEs may reference earlier ones, but not the reverse.
Non-recursive CTEs are usually inlined by the query planner like a view, and may be re-evaluated per use unless marked AS MATERIALIZED.
A recursive CTE needs WITH RECURSIVE, an anchor member, UNION ALL, and a recursive member with a stopping condition.
Recursive CTEs run iteratively: anchor once, then the recursive member repeats against only the newest rows until a pass yields nothing new.
CTEs make deeply nested subqueries readable and are the standard way to query hierarchical data like org charts or category trees.

SQL Common Table Expressions (WITH)

Overview: How Common Table Expressions Work

Syntax

Examples

Example 1: A single CTE to simplify a filter

Example 2: Multiple, chained CTEs

Example 3: A recursive CTE for a reporting hierarchy

How It Works Step by Step (Under the Hood)

Common Mistakes

Mistake 1: Forgetting the RECURSIVE keyword

Mistake 2: Referencing a later CTE from an earlier one

Best Practices

Practice Exercises

Summary

Table of Contents