WITH
A CTE names a subquery at the top of the statement so the rest of the query reads as one flat SELECT.
WITH [RECURSIVE] cte_name [(col_name [, col_name] ...)] AS (subquery) [, cte_name AS (subquery)] ... SELECT ...A common table expression (CTE) is a subquery you name up front with WITH and then reference like a table. MySQL gained WITH in the 8.0 series; everything here is as documented for MySQL 8.4 LTS.
The form
A CTE is a name, an optional column list, and a parenthesised subquery. The column names either come from an explicit list after the name, or are inferred from the aliases in the subquery's select list — the two spellings are the same thing, so WITH t (a, b) AS (SELECT 1, 2) and WITH t AS (SELECT 1 AS a, 2 AS b) produce identical results.
A CTE exists only for the statement that defines it. WITH is permitted before SELECT, UPDATE and DELETE, before the SELECT in INSERT ... SELECT, CREATE TABLE ... SELECT and CREATE VIEW, and at the start of a subquery or derived table.
Why it usually beats nesting
- The definition sits at the top, in reading order, instead of buried in a
FROMclause. - One CTE can be referenced several times in the same statement. A derived table has to be written out again for each use.
- A CTE can be recursive; a derived table cannot.
What trips people up
Names resolve derived tables first, then CTEs, then base tables and views — so a CTE named after a real table shadows it inside that statement. Duplicate CTE names at the same level are illegal, and you get one WITH per level: separate several CTEs with commas, never a second WITH.
Example
-- Customers whose 2025 revenue beat the 2025 average.
-- The CTE is referenced twice; a derived table would have to be written twice.
WITH customer_totals AS (
SELECT
o.customer_id,
COUNT(*) AS order_count,
SUM(o.total) AS revenue
FROM orders AS o
WHERE o.placed_at >= '2025-01-01'
AND o.placed_at < '2026-01-01' -- half-open range keeps the index usable
GROUP BY o.customer_id
)
SELECT
c.id,
c.email,
ct.order_count,
ct.revenue
FROM customer_totals AS ct
JOIN customers AS c
ON c.id = ct.customer_id
WHERE ct.revenue > (SELECT AVG(revenue) FROM customer_totals) -- second reference
ORDER BY ct.revenue DESC;When to use it
- Compute per-customer aggregates once and compare each row against the aggregate's own average.
- Name an intermediate result you need in both the SELECT list and the WHERE clause.
- Flatten a query that has a subquery inside a subquery into a single readable pass.
- Build the row set for an UPDATE or DELETE as a named step before applying it.
More examples
The same query as a derived table (the longhand)
Every non-recursive CTE can be rewritten as a derived table and vice versa — they are the same construct with the definition moved. Read this pair until you can convert either way on sight, because older code and generated SQL are full of the nested form.
-- Longhand: the subquery inlined into FROM.
SELECT c.email, dt.revenue
FROM (
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
) AS dt
JOIN customers AS c ON c.id = dt.customer_id;
-- Shorthand: identical result, identical plan, the step is named.
WITH dt AS (
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
)
SELECT c.email, dt.revenue
FROM dt
JOIN customers AS c ON c.id = dt.customer_id;Explicit column list versus inferred aliases
Use the explicit list when the subquery is a UNION and you do not want to alias every column of the first branch. Use inferred aliases when the expressions are already named — it keeps the name next to the expression it describes.
-- Explicit list after the CTE name.
WITH monthly (month_start, revenue) AS (
SELECT DATE_FORMAT(placed_at, '%Y-%m-01'), SUM(total)
FROM orders
GROUP BY 1
)
SELECT * FROM monthly;
-- Same thing, names inferred from the select list aliases.
WITH monthly AS (
SELECT DATE_FORMAT(placed_at, '%Y-%m-01') AS month_start,
SUM(total) AS revenue
FROM orders
GROUP BY month_start
)
SELECT * FROM monthly;WITH in front of UPDATE and DELETE
WITH is not SELECT-only: it may precede UPDATE and DELETE, which lets you compute the target set as a readable step instead of stuffing it into a correlated subquery in the WHERE clause.
-- Flag customers who have not ordered in 18 months.
WITH stale AS (
SELECT c.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.placed_at >= NOW() - INTERVAL 18 MONTH
WHERE o.id IS NULL
)
UPDATE customers AS c
JOIN stale AS s ON s.id = c.id
SET c.status = 'dormant';
Discussion