SQL CTE: The WITH Clause Explained Simply
· 7 min read
A SQL CTE (common table expression) is a named, temporary result that you define at the top of a query with WITH name AS (...) and then use like a table. It exists only while that one query runs. Analysts use CTEs to split a calculation into steps that can be read, and checked, one at a time.
WITH order_sizes AS (
SELECT order_id, region, SUM(quantity) AS items
FROM order_lines
GROUP BY order_id, region
)
SELECT region, AVG(items) AS avg_items_per_order
FROM order_sizes
GROUP BY region
ORDER BY region;Step one adds up the items in each order. Step two averages those order sizes per region. Below is the output on real rows, the same query without a CTE, how to chain several CTEs, and the mistake that skips step one.
A worked example
| order_id | region | product_name | quantity |
|---|---|---|---|
| 1 | North | Paper A4 | 10 |
| 1 | North | Stapler | 2 |
| 2 | North | Paper A4 | 4 |
| 3 | South | Dish soap | 6 |
| 3 | South | Sponges | 6 |
| 3 | South | Coffee beans | 3 |
| 4 | South | Paper A4 | 5 |
| 5 | West | Mop head | 2 |
Each row is one product on an order, so order 3 has three rows.
Step 1: what the CTE holds
You can run the CTE on its own by selecting from it:
WITH order_sizes AS (
SELECT order_id, region, SUM(quantity) AS items
FROM order_lines
GROUP BY order_id, region
)
SELECT * FROM order_sizes ORDER BY order_id;| order_id | region | items |
|---|---|---|
| 1 | North | 12 |
| 2 | North | 4 |
| 3 | South | 15 |
| 4 | South | 5 |
| 5 | West | 2 |
Now there is one row per order. This habit, ending the query with SELECT * FROM the last CTE, is the easiest way to debug a long query: check each step before you build on it.
Step 2: the full query
Now swap the last line for the real question, the query from the top:
WITH order_sizes AS (
SELECT order_id, region, SUM(quantity) AS items
FROM order_lines
GROUP BY order_id, region
)
SELECT region, AVG(items) AS avg_items_per_order
FROM order_sizes
GROUP BY region
ORDER BY region;| region | avg_items_per_order |
|---|---|
| North | 8 |
| South | 10 |
| West | 2 |
North: (12 + 4) / 2 = 8. South: (15 + 5) / 2 = 10.
SQL WITH clause syntax
The pattern is always the same:
WITH step_name AS (
SELECT ...
)
SELECT ...
FROM step_name;WITHcomes first, before the mainSELECT.- The name follows normal table naming rules. Pick one that says what a row is:
order_sizes,customer_totals,monthly_revenue. - The main query after the brackets is required. A CTE on its own is not a complete statement.
All major databases support it: DuckDB, Postgres, SQL Server, Oracle, SQLite, and MySQL from version 8.0. In SQL Server, the statement before a WITH must end with a semicolon, which is why you often see ;WITH in SQL Server code.
CTE vs subquery
A CTE does the same job as a subquery in FROM. This gives the identical result:
SELECT region, AVG(items) AS avg_items_per_order
FROM (
SELECT order_id, region, SUM(quantity) AS items
FROM order_lines
GROUP BY order_id, region
) AS order_sizes
GROUP BY region
ORDER BY region;| CTE | Subquery in FROM | |
|---|---|---|
| Reading order | top to bottom, step by step | inside out |
| Reuse in the same query | refer to the name as often as you like | write it out again each time |
| Debugging | SELECT * FROM step_name | copy the inner part out |
| Best for | two or more steps | one small inline step |
For a one-line filter, a subquery is fine. Once you have two levels of nesting, a CTE is easier for the next person, including you next month. Subqueries in WHERE, IN and correlated subqueries are covered in SQL subquery examples.
On speed: in DuckDB and in Postgres 12 and later, a simple CTE is usually optimised the same way as the subquery. Older Postgres versions always computed a CTE separately, which could be slower. Write for clarity first.
Reusing a CTE
Because a CTE has a name, you can use it twice. Here it feeds both the main query and a subquery that finds the largest order:
WITH order_sizes AS (
SELECT order_id, region, SUM(quantity) AS items
FROM order_lines
GROUP BY order_id, region
)
SELECT order_id, region, items
FROM order_sizes
WHERE items = (SELECT MAX(items) FROM order_sizes);| order_id | region | items |
|---|---|---|
| 3 | South | 15 |
Multiple CTEs
Separate CTEs with commas and write WITH only once. Each CTE can use the ones above it:
WITH order_sizes AS (
SELECT order_id, region, SUM(quantity) AS items
FROM order_lines
GROUP BY order_id, region
),
region_avg AS (
SELECT region, AVG(items) AS avg_items
FROM order_sizes
GROUP BY region
),
overall AS (
SELECT AVG(items) AS avg_items FROM order_sizes
)
SELECT r.region, r.avg_items, o.avg_items AS company_avg
FROM region_avg AS r
CROSS JOIN overall AS o
WHERE r.avg_items > o.avg_items
ORDER BY r.region;| region | avg_items | company_avg |
|---|---|---|
| North | 8 | 7.6 |
| South | 10 | 7.6 |
The company average is 38 items across 5 orders, 7.6. West's average of 2 is below it, so West is filtered out. overall has one row, so the CROSS JOIN just attaches that number to every region.
Two rules that trip people up: there is no comma after the last CTE, and you cannot refer to a CTE before it is defined.
A common pattern with multiple CTEs is "aggregate, then add a window function", for example monthly totals followed by a cumulative sum. That is how the running total guide builds year-to-date figures.
Recursive CTEs
WITH RECURSIVE lets a CTE refer to itself, which is useful for generating a list of numbers or dates, or walking a hierarchy like manager to employee:
WITH RECURSIVE months AS (
SELECT 1 AS m
UNION ALL
SELECT m + 1 FROM months WHERE m < 4
)
SELECT m FROM months;| m |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
SQL Server writes this without the word RECURSIVE. Most analysts rarely need it, but it is handy for building a full list of months to left join against.
Common mistakes
Skipping the per-order step (the AI mistake to watch for)
Ask an AI assistant for "average items per order by region" and a frequent answer is a single GROUP BY:
SELECT region, AVG(quantity) AS avg_items_per_order
FROM order_lines
GROUP BY region
ORDER BY region;| region | avg_items_per_order |
|---|---|
| North | 5.333333333333333 |
| South | 5 |
| West | 2 |
It runs, and the column name says what you asked for. But it averages lines, not orders. North looks bigger than South here, when per order it is the other way round (8 vs 10). On real data with many lines per order, the gap can be large and can flip a ranking.
How to spot it: ask "what is one row in the table I am averaging?" If the question says "per order" and the table has one row per order line, there must be a step that sums lines into orders first. A CTE named after its grain, like order_sizes, makes the step visible.
Expecting a CTE to last
A CTE disappears when the query ends. If you need the result in the next query, repeat the CTE, or create a view or temporary table.
Ordering inside the CTE
ORDER BY inside a CTE does not guarantee the order of the final result. Sort in the main query.
FAQ
What does CTE stand for in SQL?
Common table expression. It is the named result you define with WITH name AS (...).
Is a CTE faster than a subquery?
Usually neither is faster. Modern databases tend to optimise them the same way. Choose the CTE when it makes the query easier to read.
Can you have multiple CTEs in one query?
Yes. Write WITH once, separate the CTEs with commas, and each one can refer to the CTEs defined before it.
Can I use a CTE in an UPDATE or DELETE?
In Postgres and SQL Server, yes. Support varies elsewhere, so check your database's documentation.
When should I use a CTE instead of a temp table?
Use a CTE for a step that only this query needs. Use a temporary table when several queries need the same intermediate result, or when it is expensive to compute and you want to calculate it once.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Average order value by segment” →