SQL CTEs Explained: How the WITH Clause Works

A CTE — Common Table Expression — is a named, temporary result set that exists only for the duration of one query. You define it at the top with WITH, give it a name, and then use that name below exactly as if it were a table. Nothing is stored, nothing is created in the database: it is purely a way of structuring a query so the logic reads top-to-bottom instead of inside-out.

Your first CTE

The shape is always the same: WITH name AS ( a complete SELECT ), then the main query. Here, the CTE isolates the high earners, and the main query works with that result by name:

WITH high_earners AS (
  SELECT first_name, last_name, dept_id, salary
  FROM Employees
  WHERE salary > 100000
)
SELECT * FROM high_earners ORDER BY salary DESC;

This particular example could of course be a single SELECT — the point is the shape. The value appears the moment the logic has more than one step.

Why not just use a subquery?

You can — a CTE and a subquery in FROM do the same job, and the database typically executes them the same way. The difference is where the reader's eye lands. A subquery buries the first step of the logic in the middle of the statement; a CTE puts step one at the top, named, followed by step two. Compare how naturally this reads:

WITH dept_salaries AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY dept_id
)
SELECT d.dept_name, s.avg_salary
FROM dept_salaries s
JOIN Departments d ON d.dept_id = s.dept_id
ORDER BY s.avg_salary DESC;

First compute the average salary per department; then attach the department names. The query says it in that order. The name dept_salaries also documents what the intermediate result is — a subquery has no name, so the reader has to work it out.

Filtering an aggregate the readable way

A classic use: compute something per group, then filter on the computed value. HAVING can do this inline, but with a CTE the two steps stay visibly separate — and the main query can use a plain WHERE:

WITH order_revenue AS (
  SELECT customer_id, SUM(order_total) AS revenue
  FROM Orders
  WHERE status <> 'Cancelled'
  GROUP BY customer_id
)
SELECT c.company, r.revenue
FROM order_revenue r
JOIN Customers c ON c.customer_id = r.customer_id
WHERE r.revenue > 5000
ORDER BY r.revenue DESC;

Inside the CTE, revenue is being computed, so filtering on it there would need HAVING. Outside the CTE it is just a column of a result set, so an ordinary WHERE works — one of the small ways CTEs flatten SQL's evaluation-order surprises.

Several CTEs in one query

WITH takes a comma-separated list, and each CTE can refer to the ones defined before it. This is where CTEs genuinely pull ahead of nested subqueries — three levels of nesting is painful to read, three named steps is not:

WITH shipped AS (
  SELECT emp_id, COUNT(*) AS n
  FROM Orders
  WHERE status = 'Shipped'
  GROUP BY emp_id
),
totals AS (
  SELECT emp_id, SUM(order_total) AS revenue
  FROM Orders
  GROUP BY emp_id
)
SELECT e.first_name, s.n AS shipped_orders, t.revenue
FROM Employees e
JOIN shipped s ON s.emp_id = e.emp_id
JOIN totals t ON t.emp_id = e.emp_id
ORDER BY t.revenue DESC;

Each step is separately understandable, separately testable — you can copy just the inner SELECT and run it on its own while debugging — and the final query joins the pieces by name.

CTE vs subquery vs view

All three package up a SELECT for reuse; they differ in lifetime. A subquery lives inside one clause of one query. A CTE lives for one whole query and can be referenced several times within it. A view is stored in the database and shared by every query and every user. Reach for the shortest lifetime that does the job: most multi-step queries want a CTE, and a CTE that every report keeps re-defining is a hint that it should become a view.

Recursive CTEs: walking a hierarchy

A CTE may also refer to its own name, which lets one query follow a chain of rows to any depth: an org chart, a category tree, parts inside parts. PostgreSQL, MySQL 8 and SQLite introduce it with WITH RECURSIVE; SQL Server and Oracle write it with plain WITH, and SQL Server rejects the word RECURSIVE. The usual shape is a base query, UNION ALL, then a step query that refers back to the CTE itself. In the sample data, Employees.manager_id points at another employee, so the reporting tree can be walked from the top down, with each person's level in it:

WITH RECURSIVE org AS (
  -- base: the people who report to nobody
  SELECT emp_id, first_name, manager_id, 1 AS level
  FROM Employees
  WHERE manager_id IS NULL
  UNION ALL
  -- step: everyone whose manager is already in org
  SELECT e.emp_id, e.first_name, e.manager_id, o.level + 1
  FROM Employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, first_name, manager_id, level
FROM org
ORDER BY level, emp_id;

The base query finds the five employees with no manager and labels them level 1. The step then adds everyone who reports to someone already found, one level further down, and repeats on the rows it just added until a pass finds nobody new. This tree is only two levels deep, so the first pass adds the other seven employees at level 2 and the next pass finds no one, which ends the recursion. A deeper org chart would need no change to the query.

Loops in the data are the thing to watch for. This query cannot be trapped by one: it starts from the people who report to nobody, so if two people were recorded as each other's manager, neither could ever be reached, and they (with anyone reporting to them) would simply be missing from the result. A walk in the other direction, from one employee up through their managers, has no such protection and would go round that loop again and again. Some databases cap recursion depth by default and some do not, so when a recursive query could meet a loop, a guard in the step query, such as WHERE o.level < 20, is cheap insurance.

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Big orders, with names Medium
• Well-paid departments Hard
• Two CTEs, one report Hard

See all 42 exercises →

Frequently asked questions

Related reading

• SQL Views: CREATE VIEW, When to Use One, and What It Costs
• How to Find the Second Highest Salary in SQL (5 Ways)
• SQL Subqueries Explained: Nested SELECT Statements
• GROUP BY vs HAVING in SQL: What's the Difference?
• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL