MySQL CTE: Common Table Expressions with WITH

A common table expression (CTE) gives a name to a subquery, so you can use it later in the same statement like a table. It is introduced with the keyword WITH. CTEs arrived in MySQL 8.0. MySQL 5.7 does not have them.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

query.sql

SQL

WITH customer_totals AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY customer_id
)
SELECT c.name, t.order_count, t.spent
FROM customers c
JOIN customer_totals t ON t.customer_id = c.id
ORDER BY t.spent DESC;

Output

OUTPUT

+-------------+-------------+---------+
| name        | order_count | spent   |
+-------------+-------------+---------+
| Maya Shah   |           2 | 1979.00 |
| Sana Ali    |           2 | 1449.00 |
| Rohan Verma |           2 |  365.00 |
+-------------+-------------+---------+
3 rows in set

customer_totals is a named temporary result that lives for this one statement. The main query after it reads like normal SQL, using customer_totals as if it were a table.

The syntax

Text

PLAIN

WITH name [(column_list)] AS (
    SELECT ...
)
SELECT ... FROM name ...;

WITH comes first, before the main SELECT.
The CTE’s query is in brackets.
You may name the columns in the CTE header, instead of in the query:

query.sql

SQL

WITH monthly (month, revenue) AS (
    SELECT DATE_FORMAT(ordered_on, '%Y-%m'), SUM(total) FROM orders GROUP BY DATE_FORMAT(ordered_on, '%Y-%m')
)
SELECT month, revenue FROM monthly ORDER BY month;

Output

OUTPUT

+---------+---------+
| month   | revenue |
+---------+---------+
| 2026-05 | 2124.00 |
| 2026-06 | 1749.00 |
+---------+---------+
2 rows in set

Why CTEs are nice

Readability. The query is written in the order you think: first this, then that. No deeply nested brackets.
A name for an idea. customer_totals explains the step. A subquery has no name.
Reuse. The same CTE can be referenced several times in the main query.
Recursion. Only CTEs can be recursive (Recursive CTEs).

Using a CTE twice

query.sql

SQL

WITH customer_totals AS (
    SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id
)
SELECT a.customer_id, a.spent, ROUND(a.spent - (SELECT AVG(spent) FROM customer_totals), 2) AS vs_average
FROM customer_totals a
ORDER BY a.spent DESC;

Output

OUTPUT

+-------------+---------+------------+
| customer_id | spent   | vs_average |
+-------------+---------+------------+
|           1 | 1979.00 |     688.00 |
|           3 | 1529.00 |     238.00 |
|           2 |  365.00 |    -926.00 |
+-------------+---------+------------+
3 rows in set

customer_totals is read once for the rows and once for the average. With a derived table, you would have to write the inner query twice.

CTE or derived table?

The same query with a derived table:

query.sql

SQL

SELECT c.name, t.order_count, t.spent
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY customer_id
) AS t ON t.customer_id = c.id
ORDER BY t.spent DESC;

Output

OUTPUT

+-------------+-------------+---------+
| name        | order_count | spent   |
+-------------+-------------+---------+
| Maya Shah   |           2 | 1979.00 |
| Sana Ali    |           2 | 1449.00 |
| Rohan Verma |           2 |  365.00 |
+-------------+-------------+---------+
3 rows in set

Same result. The CTE puts the “preparation” first and the answer last. For one small step, a derived table is fine. For several steps, or for reuse, a CTE is clearer (Derived Tables).

Rules

A CTE exists only for the one statement that follows. It is not stored.
A CTE name must not clash with a real table name you want to use in the same query (the CTE hides it).
A CTE can refer to CTEs defined before it in the same WITH.
A CTE can be used in SELECT, UPDATE and DELETE statements (CTEs in UPDATE and DELETE).
A CTE that is not referenced is allowed (and pointless).

CTE name and alias

Treat a CTE like a table: you can give it an alias in the main query (FROM customer_totals AS t), and join it to real tables or other CTEs.

MariaDB and older MySQL

MariaDB supports CTEs from 10.2, with slightly different details. MySQL 5.7 has none, so scripts that must run on 5.7 need derived tables or temporary tables.

lightbulb

Name CTEs by what they hold

customer_totals, active_users, last_orders explain the idea. cte1, cte2 do not.