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 highlight 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.

A note on recursive CTEs

Standard SQL also allows a CTE to reference itself — WITH RECURSIVE — which is how databases like PostgreSQL, MySQL 8 and SQL Server walk tree-shaped data such as org charts or category hierarchies. It is a genuinely different technique with its own rules, and the browser engine this site's playground runs on does not support it, so the examples here stay non-recursive. When you meet one in the wild, the shape to recognise is: a base query, UNION ALL, then a query that references the CTE's own name.

Frequently asked questions

Related reading

• SQL Subqueries Explained: Nested SELECT Statements
• GROUP BY vs HAVING in SQL: What's the Difference?
• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL