A common table expression (CTE) is a named, temporary result set that you define with WITH at the start of a statement and then use like a table in the rest of that statement. It exists only while that one statement runs. MySQL supports CTEs from version 8.0; MySQL 5.7 and older will give a syntax error.
WITH cte_name AS (
SELECT ... -- any query
)
SELECT ... FROM cte_name; -- use it like a table
Sample data
A small sales table for a team of five sales reps in two regions:
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
rep VARCHAR(20),
region VARCHAR(10),
sale_date DATE,
amount DECIMAL(10,2)
);
INSERT INTO sales VALUES
(1,'Neha','North','2026-01-05',42000), (2,'Neha','North','2026-02-11',38000),
(3,'Vikram','North','2026-01-20',25000),(4,'Vikram','North','2026-02-03',21000),
(5,'Ishaan','South','2026-01-14',51000),(6,'Ishaan','South','2026-02-22',47000),
(7,'Meera','South','2026-01-09',30000), (8,'Meera','South','2026-02-17',36000),
(9,'Kabir','South','2026-02-25',18000);
Why use a CTE? The same question two ways
Question: which reps sold more than the average rep in their own region? Without a CTE you need the per-rep totals twice, so the same subquery gets pasted twice, one level deep inside another:
SELECT r.rep, r.region, r.total
FROM (SELECT rep, region, SUM(amount) AS total
FROM sales GROUP BY rep, region) AS r
JOIN (SELECT region, AVG(total) AS avg_total
FROM (SELECT rep, region, SUM(amount) AS total
FROM sales GROUP BY rep, region) AS x
GROUP BY region) AS a ON a.region = r.region
WHERE r.total > a.avg_total
ORDER BY r.region, r.total DESC;
With CTEs, each step gets a name and is written once. The second CTE reads from the first:
WITH rep_totals AS (
SELECT rep, region, SUM(amount) AS total
FROM sales
GROUP BY rep, region
),
region_avg AS (
SELECT region, AVG(total) AS avg_total
FROM rep_totals
GROUP BY region
)
SELECT r.rep, r.region, r.total, ROUND(a.avg_total, 2) AS region_avg
FROM rep_totals r
JOIN region_avg a ON a.region = r.region
WHERE r.total > a.avg_total
ORDER BY r.region, r.total DESC;
| rep | region | total | region_avg |
|---|---|---|---|
| Neha | North | 80000.00 | 63000.00 |
| Ishaan | South | 98000.00 | 60666.67 |
| Meera | South | 66000.00 | 60666.67 |
Both queries return the same three reps. The CTE version reads top to bottom like the steps you would explain to a colleague: first total each rep, then average per region, then compare. Note the syntax: one WITH, CTEs separated by commas, no comma before the final SELECT.
Referencing one CTE twice: month-over-month growth
A derived table (subquery in FROM) can be used only where it is written. A CTE can be joined to itself. Here the monthly totals are computed once and joined to the previous month:
WITH monthly AS (
SELECT CAST(DATE_FORMAT(sale_date, '%Y-%m-01') AS DATE) AS month_start,
SUM(amount) AS revenue
FROM sales
GROUP BY month_start
)
SELECT cur.month_start, cur.revenue, prev.revenue AS prev_revenue,
ROUND(100 * (cur.revenue - prev.revenue) / prev.revenue, 1) AS growth_pct
FROM monthly AS cur
LEFT JOIN monthly AS prev
ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;
| month_start | revenue | prev_revenue | growth_pct |
|---|---|---|---|
| 2026-01-01 | 148000.00 | NULL | NULL |
| 2026-02-01 | 160000.00 | 148000.00 | 8.1 |
(The same result can come from the LAG() window function, covered in the window functions tutorial. CTEs and window functions are often used together.)
CTEs in UPDATE and DELETE
In MySQL 8, WITH can also start an UPDATE or DELETE. This gives every rep who sold ₹60,000 or more a 2% bonus:
CREATE TABLE rep_bonus (rep VARCHAR(20) PRIMARY KEY, bonus DECIMAL(10,2) DEFAULT 0);
INSERT INTO rep_bonus (rep) SELECT DISTINCT rep FROM sales;
WITH totals AS (
SELECT rep, SUM(amount) AS total FROM sales GROUP BY rep
)
UPDATE rep_bonus b
JOIN totals t ON t.rep = b.rep
SET b.bonus = t.total * 0.02
WHERE t.total >= 60000;
SELECT * FROM rep_bonus ORDER BY bonus DESC, rep;
| rep | bonus |
|---|---|
| Ishaan | 1960.00 |
| Neha | 1600.00 |
| Meera | 1320.00 |
| Kabir | 0.00 |
| Vikram | 0.00 |
For INSERT ... SELECT, put the WITH after the INSERT INTO ... part: INSERT INTO t (cols) WITH c AS (...) SELECT ... FROM c;
CTE, derived table, view or temporary table?
| Option | Lives for | Can be referenced twice | Good for |
|---|---|---|---|
CTE (WITH) | One statement | Yes | Breaking one complex query into readable steps |
| Derived table | One statement | No (repeat it) | A single small subquery in FROM |
| View | Until dropped | Yes, by any query | Logic many queries or users share |
| Temporary table | Until the session ends | Yes, across statements | Large intermediate results you query many times, or need to index |
Common mistakes
- Using a CTE in the next statement.
WITH a AS (SELECT 1 AS x) SELECT * FROM a;works, but a followingSELECT * FROM a;fails with error 1146 "Table ... doesn't exist". Use a temporary table or a view if you need it again. - A comma before the main query, or a missing comma between two CTEs. Both are syntax errors.
- Expecting a speed-up. A CTE is mainly about readability. The optimizer may merge it into the outer query or materialise it once; check with
EXPLAIN, as you would for any subquery. - Running on MySQL 5.7. Check
SELECT VERSION();first; shared hosting sometimes still runs older versions or MariaDB (which added CTEs in 10.2). - Forgetting
RECURSIVEwhen a CTE refers to itself. Hierarchies and number sequences are covered in recursive CTEs in MySQL.
If CTEs still feel new, practise with plain subqueries first and then rewrite them as CTEs; you will see quickly where the named steps help.