A CTE, short for common table expression, is a temporary, named result set that you define with the WITH keyword and then query as if it were a table. It exists only for the one statement that follows it. Think of it as giving a name to a subquery so you can read the query top to bottom instead of inside out.
Jump to: syntax · sample data · 7 examples · recursive CTEs · CTE vs subquery · common mistakes · FAQ
CTE syntax
WITH cte_name AS (
SELECT ...
)
SELECT ...
FROM cte_name;The part inside the parentheses is an ordinary SELECT. The final SELECT after it can use cte_name in FROM or JOIN exactly like a real table. You can also list the column names up front, WITH cte_name (col_a, col_b) AS (...), which is required for recursive CTEs.
CTEs are part of standard SQL and work in PostgreSQL, MySQL 8+, SQL Server, SQLite 3.8.3+, Oracle, Snowflake and BigQuery. In SQL Server a statement before WITH must end in a semicolon. If you search for the "SQL WITH clause" or "WITH statement", this is what you are looking for.
Set up the sample data
Every query below ran on SQLite 3.45 and the output is real. Paste this into any SQL client, such as Beekeeper Studio, to follow along.
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
(1, 'Grace', 'Executive', 200, NULL),
(2, 'Ana', 'Engineering', 150, 1),
(3, 'Ben', 'Engineering', 110, 2),
(4, 'Chloe', 'Engineering', 100, 2),
(5, 'Dev', 'Sales', 90, 1),
(6, 'Eli', 'Sales', 70, 5),
(7, 'Fay', 'Sales', 80, 5),
(8, 'Gus', 'Support', 60, 5);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER, order_date TEXT);
INSERT INTO orders VALUES
(1, 'Ana', 120, '2026-01-05'), (2, 'Ana', 80, '2026-02-10'),
(3, 'Ben', 300, '2026-01-20'), (4, 'Ben', 45, '2026-03-02'),
(5, 'Chloe', 60, '2026-02-14'), (6, 'Dev', 500, '2026-03-15'),
(7, 'Dev', 250, '2026-03-20');7 CTE examples
1. A first CTE: name a calculation, then filter on it
Which customers have spent more than 200 in total? You need the total per customer before you can filter, so calculate it in a CTE first.
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 200
ORDER BY total DESC;customer | total
Dev | 750
Ben | 345You cannot put SUM(amount) > 200 in a WHERE clause, because WHERE runs before grouping. A CTE sidesteps that: the grouping happens inside it, and the outer query filters the finished totals. (HAVING would also work here; a CTE shines once the logic gets bigger.)
2. Compare each row to a group average
Who earns more than the average for their own department?
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_salary;name | department | salary | avg_salary
Ana | Engineering | 150 | 120.0
Dev | Sales | 90 | 80.0Notice the CTE behaves like a table you can join to. This "aggregate first, then join back" shape is one of the most common reasons to reach for a CTE.
3. Chain several CTEs
Separate multiple CTEs with commas, with a single WITH at the start. Later CTEs can use earlier ones.
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
),
avg_total AS (
SELECT AVG(total) AS avg_total FROM customer_totals
)
SELECT c.customer, c.total, ROUND(a.avg_total, 1) AS avg_total
FROM customer_totals c, avg_total a
WHERE c.total > a.avg_total
ORDER BY c.total DESC;customer | total | avg_total
Dev | 750 | 338.8
Ben | 345 | 338.8Each step has a name that says what it does, so you can read the query like a recipe. There is more on this in how to use 2 CTEs in a single SQL query.
4. A CTE with a window function
Window functions cannot be used directly in WHERE, so wrap them in a CTE and filter outside. Here is the top earner in each department:
WITH ranked AS (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn = 1
ORDER BY department;department | name | salary
Engineering | Ana | 150
Executive | Grace | 200
Sales | Dev | 90
Support | Gus | 60This is the classic "top N per group" pattern. See SQL window functions explained for how ROW_NUMBER and PARTITION BY work.
5. Month-over-month change
WITH monthly AS (
SELECT substr(order_date, 1, 7) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;month | revenue | change
2026-01 | 420 | NULL
2026-02 | 140 | -280
2026-03 | 795 | 655The first month has no previous month, so change is NULL. Wrap it in COALESCE if you want 0 instead.
6. Use a CTE to filter before you delete or update
In SQLite, PostgreSQL and SQL Server, a CTE can precede UPDATE and DELETE too (MySQL 8 supports it as well). I tested this in SQLite:
WITH low AS (SELECT id FROM employees WHERE salary < 75)
DELETE FROM employees WHERE id IN (SELECT id FROM low);It removed the two rows with salary under 75 (Eli and Gus), leaving six employees. Run the matching SELECT first, as a CTE on its own, to check what you are about to change.
7. Generate a sequence of numbers
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;n
1
2
3
4
5That is a recursive CTE, covered next.
Recursive CTEs
A recursive CTE refers to itself, which lets SQL walk through hierarchies such as org charts, category trees or folder structures. It has two parts joined by UNION ALL:
- The anchor member: a normal query that returns the starting rows.
- The recursive member: a query that references the CTE itself and finds the next level. SQL repeats it until it returns no new rows.
Here is the reporting chain of our sample company, starting from the person with no manager:
WITH RECURSIVE org(id, name, level) AS (
SELECT id, name, 0 FROM employees WHERE manager_id IS NULL -- anchor
UNION ALL
SELECT e.id, e.name, o.level + 1 -- recursive step
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT substr(' ', 1, level * 2) || name AS org_chart, level
FROM org
ORDER BY level, name;org_chart | level
Grace | 0
Ana | 1
Dev | 1
Ben | 2
Chloe | 2
Eli | 2
Fay | 2
Gus | 2You can also build the full path to one person by carrying text along:
WITH RECURSIVE org(id, name, path) AS (
SELECT id, name, name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, o.path || ' > ' || e.name
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT path FROM org WHERE name = 'Eli';path
Grace > Dev > EliSyntax differs slightly by database: PostgreSQL, MySQL and SQLite require the RECURSIVE keyword, while SQL Server and Oracle do not use it. Always make sure the recursive member moves toward an end (a WHERE limit or a tree that actually terminates), otherwise the query loops until the database stops it.
CTE vs subquery vs temp table vs view
| Lives for | Best for | |
|---|---|---|
| CTE | One statement | Readable multi-step queries, reusing a result inside one query, recursion |
| Subquery | One statement | Short, one-off filters such as WHERE id IN (SELECT ...) |
| Temp table | Your session | Reusing a big result across several statements, or adding an index to it |
| View | Saved in the database | A query many people or reports share (see CREATE VIEW) |
Is a CTE better than a subquery? For the same logic they usually give the same result, and many databases plan them identically. A CTE wins on readability, and when you need to use the same result twice. A short subquery is fine when it is used once. Performance is not a reliable reason to pick either: older PostgreSQL (before version 12) always materialized CTEs, which could be slower, while most modern engines inline them. Check your database's query plan if speed matters.
Common CTE mistakes
- Using a CTE outside its statement. It disappears after the query ends. In my SQLite test, running a second
SELECT * FROM tafter definingtas a CTE failed withno such table: t. Use a view or temp table to keep a result. - Repeating
WITHfor each CTE. WriteWITH a AS (...), b AS (...), with oneWITHand commas between them. - Forgetting the semicolon before
WITHin SQL Server. The previous statement must end with;. - Expecting a CTE to speed things up. A CTE is mainly a readability tool. It does not automatically store results or add indexes.
- Naming a column twice. Every column in a CTE needs a unique name, so alias aggregates such as
SUM(amount) AS total. - Infinite recursion. A recursive CTE whose rows never run out will loop. Add a depth limit like
WHERE level < 10while you are developing.
FAQ
What is a CTE in SQL?
A common table expression is a named temporary result set defined with WITH and available only to the statement that follows it. It works like a throwaway view for a single query.
What is the difference between a CTE and a temp table?
A CTE exists for one statement and is not stored. A temp table is a real table that lasts for your session, can be indexed, and can be used by many statements.
Is a CTE faster than a subquery?
Not by default. They are usually equivalent in speed; a CTE is chosen for clarity. Performance depends on your database's optimizer, so check the query plan.
Can I use more than one CTE in a query?
Yes. Use one WITH and separate the CTEs with commas. Later CTEs can reference earlier ones.
Can a CTE be used with INSERT, UPDATE or DELETE?
Yes, in most databases. In SQLite, PostgreSQL and SQL Server the WITH clause can lead into any of these statements.
What is a recursive CTE used for?
Walking hierarchies (org charts, category trees, bills of materials), generating number or date series, and following paths in graph-like data.
Does MySQL support CTEs?
Yes, from MySQL 8.0. Older versions such as 5.7 do not, so use a subquery or temp table there.
Practice CTEs
A CTE is mostly a better way to write subqueries, so start with the free interactive nested query lesson and the GROUP BY lesson, which run in your browser. Then try the SQL practice problems, see more SQL query examples, and keep the SQL cheat sheet open for syntax.