As questions get harder, queries get longer. A report might need to total each customer's spending, work out the average, and then compare each customer with it. Written as one statement with subqueries nested inside subqueries, it quickly becomes something nobody wants to read, including you next month.
A common table expression, or CTE, lets you give a step of the query a name with the WITH keyword, then use that name like a table in the rest of the query. You met it briefly in the subqueries lesson. Here you'll use it properly: several steps in a row, each building on the last, reading from top to bottom like a recipe.
CTEs have a second, more surprising power: a recursive CTE can refer to itself. That makes it possible to walk through hierarchies such as organisation charts and category trees, and to generate series of numbers or dates, things plain SQL can't do in one query.
The WITH clause
WITH big_orders AS (
SELECT * FROM orders WHERE quantity >= 5
)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id;WITH name AS ( query )defines a named, temporary result.- The main query that follows can use the name as if it were a table.
- The CTE exists only for this one statement. Nothing is saved in the database.
CTEs work in PostgreSQL, SQL Server, SQLite and MySQL 8.0 or later.
Several CTEs in a row
Separate several CTEs with commas. Each one can use the ones defined before it:
WITH spending AS (...),
average AS (SELECT AVG(spent) AS avg_spent FROM spending)
SELECT ...This turns a complex question into small, named steps you can test one at a time: run the first CTE on its own, check the result, then add the next. Good names, such as spending and average, make the final query read almost like a description of what it does.
CTE, subquery or view?
| Lives for | Reusable in other queries | Best for | |
|---|---|---|---|
| Subquery | One place in one query | No | Small, one-off conditions |
| CTE | One statement | No | Breaking one complex query into steps |
| View | Until dropped | Yes | Logic many queries share |
A CTE can be referenced several times in the same statement, which a subquery can't. If you find yourself copying the same CTE into many queries, it's time to turn it into a view.
Recursive CTEs
A recursive CTE has two parts joined by UNION ALL:
- The anchor: a starting query that runs once.
- The recursive member: a query that refers to the CTE itself and runs again on each new batch of rows, until it returns nothing.
WITH RECURSIVE numbers(n) AS (
SELECT 1 -- anchor
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5 -- recursive member
)
SELECT n FROM numbers; -- 1, 2, 3, 4, 5The WHERE condition is what stops it. PostgreSQL, SQLite and MySQL need the word RECURSIVE; SQL Server leaves it out.
SQL CTEs Syntax
CTE templates
WITH step1 AS (SELECT ...),
step2 AS (SELECT ... FROM step1)
SELECT ... FROM step2;
WITH RECURSIVE r(cols) AS (
SELECT ... -- anchor
UNION ALL
SELECT ... FROM r WHERE ... -- recursive member with a stop
)
SELECT * FROM r;SQL CTEs Summary
WITH name AS (query)names a temporary result for a single statement.- Chain several CTEs with commas; each can use the ones before it.
- Use CTEs to make complex queries readable and testable step by step.
- A recursive CTE has an anchor and a recursive member joined by UNION ALL.
- Recursive CTEs generate series and walk hierarchies; always include a stop condition.
SQL CTEs Examples
Each example shows the code, the output it produces and what each part does.
Comparing each customer with the average
WITH spending AS (
SELECT o.customer_id, SUM(o.quantity * p.price) AS spent
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY o.customer_id
),
average AS (
SELECT AVG(spent) AS avg_spent FROM spending
)
SELECT c.name,
ROUND(s.spent, 2) AS spent,
CASE WHEN s.spent > a.avg_spent THEN 'above' ELSE 'below' END AS vs_average
FROM spending s
JOIN customers c ON c.id = s.customer_id
CROSS JOIN average a
ORDER BY s.spent DESC;name | spent | vs_average
------+-------+-----------
Ana | 46.99 | above
Ben | 29.5 | below
Dev | 24.5 | below
Chloe | 24 | belowLine-by-line explanation
- Lines 1 to 6Step one: each customer's total spending.
- Lines 7 to 9Step two uses step one to find the average, about 31.25.
- Line 15The average is a single row, so a CROSS JOIN attaches it to every customer.
Filling gaps in a daily report
WITH RECURSIVE days(day) AS (
SELECT '2024-07-01'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2024-07-08'
)
SELECT d.day, COUNT(o.id) AS orders
FROM days d
LEFT JOIN orders o ON o.order_date = d.day
GROUP BY d.day
ORDER BY d.day;day | orders
-----------+-------
2024-07-01 | 1
2024-07-02 | 2
2024-07-03 | 0
2024-07-04 | 0
2024-07-05 | 1
2024-07-06 | 1
2024-07-07 | 0
2024-07-08 | 1Line-by-line explanation
- Lines 1 to 5Generate every date from 1 to 8 July, one row per day. PostgreSQL could use generate_series instead.
- Line 8LEFT JOIN the real orders onto the full calendar. Days with no orders, such as the 3rd, 4th and 7th, appear with 0 instead of being missing, which matters for charts.
Walking an organisation chart
The staff table from the joins lesson: Maria manages Tom and Aisha, Tom manages Leo and Priya, and Aisha manages Sam.
WITH RECURSIVE chain(id, name, level, path) AS (
SELECT id, name, 0, name
FROM staff
WHERE manager_id IS NULL
UNION ALL
SELECT s.id, s.name, c.level + 1, c.path || ' > ' || s.name
FROM staff s
JOIN chain c ON s.manager_id = c.id
)
SELECT level, path FROM chain ORDER BY path;level | path
------+--------------------
0 | Maria
1 | Maria > Aisha
2 | Maria > Aisha > Sam
1 | Maria > Tom
2 | Maria > Tom > Leo
2 | Maria > Tom > PriyaLine-by-line explanation
- Lines 2 to 4The anchor finds the top of the tree: the person with no manager.
- Lines 6 to 8Each round finds the people reporting to the previous round, adding one to the level and extending the path.
- Line 10It stops by itself when a round finds no more staff. Sorting by path groups each team together.
Common SQL CTEs Mistakes
Using a CTE in a later statement
WITH big AS (SELECT * FROM orders WHERE quantity >= 5);
SELECT * FROM big; -- error: no such tableWITH big AS (SELECT * FROM orders WHERE quantity >= 5)
SELECT * FROM big;A recursive CTE that never stops
WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n)
SELECT x FROM n;WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 100)
SELECT x FROM n;Repeating WITH for each CTE
WITH a AS (...)
WITH b AS (...)
SELECT ...WITH a AS (...),
b AS (...)
SELECT ...SQL CTEs in Real Projects
CTEs in analytics
Analysts write long reports as a chain of CTEs: clean the raw data, join it, aggregate it, then rank it, with each step named and easy to check. Recursive CTEs power category menus with unlimited sub-levels, comment threads with replies to replies, bill-of-materials lists in manufacturing, and calendars that include days with no activity.
SQL CTEs FAQ
Do CTEs make queries faster?
Mostly they make queries easier to read. Modern PostgreSQL, from version 12, usually merges a simple CTE into the main query, so it performs like the equivalent subquery; WITH name AS MATERIALIZED (...) forces it to be computed once. Other databases behave similarly, so choose CTEs for clarity and measure when performance matters.
Which databases support CTEs?
All the major ones: PostgreSQL, SQL Server, Oracle, SQLite since version 3.8.3 and MySQL since version 8.0, including recursive CTEs. If you're using MySQL 5.7, you'll need subqueries or temporary tables instead.
Can I use a CTE with INSERT, UPDATE or DELETE?
Yes, in PostgreSQL, SQLite and SQL Server, and in MySQL for UPDATE and DELETE. For example, WITH old AS (SELECT id FROM orders WHERE order_date < '2023-01-01') DELETE FROM orders WHERE id IN (SELECT id FROM old);. PostgreSQL even allows data-changing statements inside the WITH itself.
Is there a limit to how deep a recursive CTE can go?
Some databases set one: MySQL stops at 1,000 levels by default through the cte_max_recursion_depth setting, and SQL Server at 100 through MAXRECURSION. PostgreSQL and SQLite have no fixed depth limit, so a missing stopping condition runs until memory or time runs out. Always include a stop condition.
Should I use a CTE or a temporary table?
A CTE exists only for one statement and needs no clean-up. A temporary table stores results for the rest of the session, can be indexed and can be reused by several later queries. Use a temporary table when an intermediate result is large and used many times.
SQL CTEs Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Complete the keyword that starts a common table expression.
recent AS (
SELECT * FROM orders WHERE order_date >= '2024-07-05'
)
SELECT COUNT(*) FROM recent;How long does a CTE exist?
What does this return?
WITH RECURSIVE n(x) AS (
SELECT 2 UNION ALL SELECT x * 2 FROM n WHERE x < 16
)
SELECT x FROM n;How do you write two CTEs in one query?
What are the two parts of a recursive CTE?
SQL CTEs Quiz
5 questions to check what you learned.
What's the main benefit of a CTE?
- It makes tables permanent
- It splits a complex query into readable, named steps
- It adds an index
- It locks the table
When should a CTE become a view instead?
- When many different queries need the same logic
- When it has more than two columns
- When it uses a join
- Never
Which problem do recursive CTEs solve well?
- Deleting duplicates
- Encrypting data
- Creating indexes
- Walking hierarchies like organisation charts and category trees
Why generate a series of dates before joining orders?
- To speed up the query
- So days with no orders still appear, with a count of 0
- To sort the orders
- Dates can't be grouped otherwise
What stops a recursive CTE?
- The LIMIT on the anchor
- A semicolon
- The recursive member returning no new rows, usually because of a WHERE condition
- The database after one round