SQL CTE Tutorial: WITH Clause and Recursive Queries Explained With Examples

Most SQL becomes hard to read at the point where a subquery sits inside another subquery. A SQL CTE, or common table expression, fixes that by letting you name each step and read the query from top to bottom, much like the lines of a program.

This tutorial builds from a basic example up to recursive queries. We ran every query below in SQLite 3.50, and the results shown come from that run. The WITH syntax is standard, so it also works in PostgreSQL, SQL Server, Oracle, and MySQL 8 or later, with small differences noted along the way.

What is a CTE?

A common table expression is a temporary, named result set that you define at the start of a statement. It exists only while that one statement runs, and it is not stored anywhere.

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

There are three parts: the WITH keyword, a name of your choice, and a query in brackets. The statement that follows can then use that name as if it were a table. CTEs usually lead into a SELECT, although most databases also allow them before INSERT, UPDATE and DELETE.

The sample table

All examples use this small employees table. The manager_id column points at another row in the same table, which we use later for the recursive query.

CREATE TABLE employees (id INT, name TEXT, manager_id INT, dept TEXT, salary INT);

INSERT INTO employees VALUES
 (1, 'Meera', NULL, 'Exec', 200),
 (2, 'Arjun', 1, 'Eng', 150),
 (3, 'Sara', 1, 'Sales', 120),
 (4, 'Ravi', 2, 'Eng', 100),
 (5, 'Lina', 2, 'Eng', 110),
 (6, 'Omar', 4, 'Eng', 80),
 (7, 'Tina', 3, 'Sales', 70);

Your first CTE

This defines a set of engineers, then filters it:

WITH eng AS (
  SELECT name, salary FROM employees WHERE dept = 'Eng'
)
SELECT name, salary FROM eng WHERE salary > 100;

-- Arjun | 150
-- Lina  | 110

This example is simple enough not to need a CTE. The real benefit appears when a query has a step that must happen first, such as calculating an average and then comparing rows with it.

CTE vs subquery

Suppose we want employees who earn more than their department’s average. Here it is with a subquery in the FROM clause:

SELECT e.name, e.dept, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
) d ON d.dept = e.dept
WHERE e.salary > d.avg_salary;

And here is the same logic written as a CTE:

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

-- Arjun | Eng   | 150 | 110.0
-- Sara  | Sales | 120 |  95.0

The result is identical, but now you read the calculation first and its use second, and the step has a meaningful name. Another benefit is that one CTE can be referenced several times in a query, whereas a subquery would have to be repeated. If GROUP BY is unfamiliar, read our guide to SQL GROUP BY and HAVING.

Multiple CTEs

Separate CTEs with commas, and use only one WITH. Each can read the ones defined before it, which is what makes this pattern so useful for reports:

WITH dept_totals AS (
  SELECT dept, SUM(salary) AS total
  FROM employees
  GROUP BY dept
),
company AS (
  SELECT SUM(total) AS grand_total FROM dept_totals
)
SELECT dept, total,
       ROUND(100.0 * total / grand_total, 1) AS pct
FROM dept_totals, company
ORDER BY total DESC;

-- Eng   | 440 | 53.0
-- Exec  | 200 | 24.1
-- Sales | 190 | 22.9

You can test a long query in stages, too. To debug it, replace the final SELECT with SELECT * FROM dept_totals and check that intermediate step on its own. Nested subqueries do not allow this as easily.

Top row per group

You cannot use a window function directly in a WHERE clause, because WHERE runs before window functions are calculated. A CTE solves this neatly, by computing the rank first and filtering afterwards:

WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT dept, name, salary FROM ranked WHERE rn = 1;

-- Eng   | Arjun | 150
-- Exec  | Meera | 200
-- Sales | Sara  | 120

This is a common request in interviews and analytics work. To go deeper on ranking, see our SQL window functions tutorial.

Recursive CTEs

A recursive CTE refers to itself, which lets it follow a chain of unknown length. A normal join can only go a fixed number of levels, so it cannot answer “list everyone under this person, however deep the tree goes”.

It has two parts joined by UNION ALL:

• Anchor member: the starting rows, which run once.
• Recursive member: a query that joins to the CTE itself. It runs repeatedly on the rows from the previous step, until it returns no more rows.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT name, level FROM org ORDER BY level, name;

-- Meera  | 1
-- Arjun  | 2
-- Sara   | 2
-- Lina   | 3
-- Ravi   | 3
-- Tina   | 3
-- Omar   | 4

Here is how it executes. The anchor finds Meera, who has no manager. The first recursive pass finds Meera’s direct reports, Arjun and Sara. The next pass finds their reports, then Omar on the following pass. The pass after that finds nobody, so the query stops.

Generating a series

Recursion does not need a table at all. This produces the numbers 1 to 5, which is useful for filling gaps in reports, such as days with no sales:

WITH RECURSIVE days(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM days WHERE n < 5
)
SELECT n FROM days;

-- 1, 2, 3, 4, 5 (one row each)

The WHERE n < 5 condition is what stops it. Remove it and the query would keep going until it hits a limit or runs out of resources. Bad data can cause the same problem: if two employees are each other’s manager, the org query could loop forever. SQL Server stops at 100 levels of recursion by default, but do not rely on every database protecting you, so add a depth limit such as WHERE o.level < 20 when your data is not guaranteed to be a clean tree.

CTE vs temp table vs view

Performance

A CTE is mainly a readability tool, so do not assume it makes a query faster. How it runs depends on your database. Modern PostgreSQL (version 12 onward) can merge a simple CTE into the main query, and you can control this with MATERIALIZED and NOT MATERIALIZED. SQL Server generally treats a CTE like an inlined query, so referencing it twice may run that logic twice.

The practical advice is to write the clear version first, and then check the execution plan with EXPLAIN if it is slow. If a large intermediate result is used several times, a temp table with an index may be better. And underlying tables still need good indexes, as covered in SQL indexes explained.

Common mistakes

• Using a CTE after its statement ends. It is gone once the statement finishes. A second query cannot read it.
• Repeating WITH for each CTE. Use one WITH and separate the CTEs with commas.
• Forgetting the semicolon in SQL Server. The statement before WITH must end with a semicolon there, or you get a syntax error.
• Using UNION rather than UNION ALL in recursion. UNION removes duplicates, which adds work. It can also help stop cycles in some databases, so choose it deliberately.
• Leaving out a stopping condition. Every recursive member needs a way to run out of rows.

Final thoughts

Reach for a CTE as soon as a query has a clear “first do this, then do that” shape. It costs little and makes reviews and debugging much easier. To practise, take the longest query you maintain and split it into named steps, then run each one on its own. If you are still building your foundations, our SQL joins guide pairs well with this one.

Frequently asked questions

Keep learning