SQL CTEs: Write Cleaner Queries With WITH and Recursion

At some point, every SQL writer produces a query with a subquery inside a subquery inside another subquery. It works, but reading it a week later feels like untangling headphones.

A Common Table Expression, or CTE, is the fix. It lets you give a piece of your query a name and then use it like a table. You read the query top to bottom, step by step, instead of inside out.

The data

Run this in the SQL editor.

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

INSERT INTO employees VALUES
  (1, 'Maya',  'Executive',   200000, NULL),
  (2, 'Omar',  'Engineering', 150000, 1),
  (3, 'Lena',  'Engineering', 120000, 2),
  (4, 'Raj',   'Engineering',  95000, 3),
  (5, 'Sofia', 'Sales',       110000, 1),
  (6, 'Tom',   'Sales',        70000, 5),
  (7, 'Uma',   'Sales',        72000, 5);

Your first CTE

The question: which employees earn more than the average for their department?

Here's the nested subquery way:

SELECT name, department, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department = e.department
)
ORDER BY department, name;

name   department   salary
-----  -----------  ------
Omar   Engineering  150000
Sofia  Sales        110000

It works, but the logic is buried in the middle. Now with a CTE:

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

name   department   salary  dept_average
-----  -----------  ------  ------------
Omar   Engineering  150000  121667.0
Sofia  Sales        110000  84000.0

The structure is WITH name AS ( query ) main_query. The CTE exists only for this one statement. It's a temporary named result, and it doesn't create anything permanent in your database. You also get the average in the output, which the first version couldn't show.

Chaining several CTEs

Separate multiple CTEs with commas. Each one can use the ones defined before it. This turns a complicated question into a sequence of small, named steps.

The question: for each department, what share of the company payroll does it represent?

WITH dept_totals AS (
  SELECT department, SUM(salary) AS dept_payroll
  FROM employees
  GROUP BY department
),
company AS (
  SELECT SUM(dept_payroll) AS total_payroll FROM dept_totals
)
SELECT d.department,
       d.dept_payroll,
       ROUND(100.0 * d.dept_payroll / c.total_payroll, 1) AS pct_of_payroll
FROM dept_totals d
CROSS JOIN company c
ORDER BY d.dept_payroll DESC;

department   dept_payroll  pct_of_payroll
-----------  ------------  --------------
Engineering  365000        44.7
Sales        252000        30.8
Executive    200000        24.5

Each step has a name that explains what it does. If a number looks wrong, you can run SELECT * FROM dept_totals on its own to check that stage.

CTE or subquery or view?

All three can do similar things. A rough guide:

• Subquery: fine for something short and used once.
• CTE: best when the query has several steps, or when you refer to the same piece more than once.
• View: a saved query you want to reuse across many statements. Unlike a CTE, it lives in the database.

CTEs also pair nicely with window functions. Because you can't filter on a window result directly, a CTE is a clean way to rank rows in one step and filter them in the next.

WITH ranked AS (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS pay_rank
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE pay_rank = 1
ORDER BY department;

name   department   salary
-----  -----------  ------
Omar   Engineering  150000
Maya   Executive    200000
Sofia  Sales        110000

That's the highest-paid person in each department.

Recursive CTEs

A recursive CTE refers to itself. It sounds exotic, but the idea is simple: start with some rows, then repeatedly find more rows based on the ones you just found, until nothing new turns up.

It has two parts joined by UNION ALL: an anchor (the starting rows) and a recursive step (how to get the next level). Here's the simplest example, counting from 1 to 5:

WITH RECURSIVE counter(n) AS (
  SELECT 1                       -- anchor
  UNION ALL
  SELECT n + 1 FROM counter      -- recursive step
  WHERE n < 5                    -- stop condition
)
SELECT n FROM counter;

n
-
1
2
3
4
5

The WHERE n < 5 is the stop condition. Forget it and the query runs forever, so always include one.

A real use: walking an org chart

Our table has a manager_id column, so employees form a tree. Recursion can follow it from the top down and track each person's level.

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

  UNION ALL

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

level  name   path
-----  -----  ------------------------
0      Maya   Maya
1      Omar   Maya > Omar
2      Lena   Maya > Omar > Lena
3      Raj    Maya > Omar > Lena > Raj
1      Sofia  Maya > Sofia
2      Tom    Maya > Sofia > Tom
2      Uma    Maya > Sofia > Uma

The anchor picks the person with no manager. Each pass of the recursive step adds the people who report to anyone already found. The path column builds a readable chain. This same pattern handles category trees, comment threads, folder structures, and bill-of-materials lists.

You can also go the other way, from an employee up to the top, by starting at one person and joining on e.id = org.manager_id.

Mistakes and limits

• A CTE isn't always a performance boost. Some databases treat it as a plain subquery. Others run it once and reuse the result. Don't count on speed gains. Use CTEs for clarity.
• Referencing a CTE outside its statement. It disappears after the query finishes.
• Infinite recursion. Bad data, such as two people who manage each other, can cause a loop. Add a depth limit like WHERE level < 10.
• Syntax differences. Some databases need WITH RECURSIVE, while SQL Server uses just WITH. Check your database's docs.

Key takeaways

• WITH name AS (...) gives a piece of your query a name, so you read it top to bottom.
• Chain several CTEs with commas to break big problems into small steps.
• CTEs plus window functions make top-N-per-group queries easy to read.
• Recursive CTEs have an anchor, a recursive step, and a stop condition. Use them for trees and sequences.