SQL Tutorial › Subqueries & CTEs › CTE (WITH Clause)

SQL CTE (WITH Clause)

AdvancedLesson 43 of 6911 min read5 queries run on MySQL 8.4

A CTE (Common Table Expression) gives a subquery a name at the top of your query with WITH name AS (...). You then use it like a table. CTEs turn a nested, inside-out query into readable steps from top to bottom, which is why they are now the standard way to write analytical SQL, and a favourite interview topic.

In this lesson

Write a CTE with WITH ... AS (...)
Chain several CTEs, each building on the last
Reuse one CTE several times
Choose between a CTE, a subquery and a view

CTE syntax

MySQL

WITH cte_name AS (  SELECT ...)SELECT ...FROM cte_name;

A CTE exists only for the one statement that follows it. MySQL supports CTEs from version 8.0.

Sample data

orders · 10 rows

order_id	customer_id	order_date	status
1001	1	2025-01-05	Delivered
1002	2	2025-01-17	Delivered
1003	1	2025-02-02	Delivered
1004	3	2025-02-14	Cancelled
1005	4	2025-03-08	Delivered
1006	5	2025-03-22	Shipped
1007	2	2025-04-10	Delivered
1008	1	2025-04-28	Pending
1009	3	2025-05-15	Delivered
1010	5	2025-06-01	Delivered

order_items · 16 rows

order_id	product_id	quantity
1001	2	1
1001	6	5
1002	4	1
1003	1	1
1003	2	1
1004	5	1
1005	6	10
1005	7	4
1006	3	2
1006	8	1
1007	1	1
1008	4	2
1008	8	2
1009	2	1
1010	6	3
1010	7	2

products · 9 rows (some columns)

product_id	product_name	category	price
1	Wireless Mouse	Electronics	799
2	Mechanical Keyboard	Electronics	3499
3	USB-C Hub	Electronics	1999
4	Office Chair	Furniture	8999
5	Standing Desk	Furniture	18999
6	Notebook Pack	Stationery	249
7	Gel Pens	Stationery	199
8	Desk Lamp	Furniture	1499
9	Monitor Arm	Furniture	2799

A first CTE

Order totals, then the orders above 5,000:

SQL

WITH order_totals AS (  SELECT oi.order_id, SUM(oi.quantity * p.price) AS total  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY oi.order_id)SELECT order_id, totalFROM order_totalsWHERE total > 5000ORDER BY total DESC;

ResultMySQL 8.4

order_id	total
1008	20996
1004	18999
1002	8999
1006	5497

4 rows in set

How this query works, step by step

WITH order_totals AS (...) defines a named result: one row per order with its total.
The main query reads order_totals like a table.
WHERE total > 5000 works directly on the computed column, no repeated expression needed.

Compare with the same query as a derived table: the logic is identical, but you read it from the inside out.

SQL

SELECT order_id, totalFROM (  SELECT oi.order_id, SUM(oi.quantity * p.price) AS total  FROM order_items oi JOIN products p ON p.product_id = oi.product_id  GROUP BY oi.order_id) AS order_totalsWHERE total > 5000ORDER BY total DESC;

ResultMySQL 8.4

order_id	total
1008	20996
1004	18999
1002	8999
1006	5497

4 rows in set

Several CTEs in steps

Separate CTEs with commas. Each one may use the ones defined before it. Customer spend, the average spend, and who is above it:

SQL

WITH order_totals AS (  SELECT o.order_id, o.customer_id, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.order_id, o.customer_id),customer_spend AS (  SELECT customer_id, SUM(total) AS spend, COUNT(*) AS orders  FROM order_totals  GROUP BY customer_id),average AS (  SELECT AVG(spend) AS avg_spend FROM customer_spend)SELECT c.customer_name, cs.orders, cs.spend, ROUND(a.avg_spend) AS avg_spendFROM customer_spend csJOIN customers c ON c.customer_id = cs.customer_idCROSS JOIN average aWHERE cs.spend > a.avg_spendORDER BY cs.spend DESC;

ResultMySQL 8.4

customer_name	orders	spend	avg_spend
Ananya Sharma	3	30038	10653

1 row in set

How this query works, step by step

order_totals: the value of each non-cancelled order.
customer_spend: built from order_totals, one row per customer.
average: one row with the average customer spend.
The final query joins the steps and keeps customers above average.
Each step can be tested on its own by temporarily selecting from it, which makes debugging easy.

Reusing a CTE

A CTE can be referenced more than once in the same query. Each department’s payroll compared with the largest department payroll:

MySQL

WITH dept_pay AS (  SELECT dept_id, SUM(salary) AS payroll  FROM employees  WHERE dept_id IS NOT NULL  GROUP BY dept_id)SELECT d.dept_id, d.payroll,       ROUND(100 * d.payroll / (SELECT MAX(payroll) FROM dept_pay), 1) AS pct_of_largestFROM dept_pay dORDER BY d.payroll DESC;

ResultMySQL 8.4

dept_id	payroll	pct_of_largest
1	392000	100.0
2	207000	52.8
3	133000	33.9
4	78000	19.9

4 rows in set

CTE with UPDATE and DELETE

In MySQL a CTE can also feed an UPDATE or DELETE. Restock every product that sold 3 or more units:

MySQL

WITH best_sellers AS (  SELECT product_id FROM order_items GROUP BY product_id HAVING SUM(quantity) >= 3)UPDATE productsSET stock = stock + 100WHERE product_id IN (SELECT product_id FROM best_sellers); SELECT product_id, product_name, stock FROM products ORDER BY product_id;

ResultMySQL 8.4

mysql> WITH best_sellers AS (

Query OK, 5 rows affected

mysql> SELECT product_id, product_name, stock FROM products ORDER BY product_id

product_id	product_name	stock
1	Wireless Mouse	120
2	Mechanical Keyboard	145
3	USB-C Hub	0
4	Office Chair	112
5	Standing Desk	5
6	Notebook Pack	400
7	Gel Pens	350
8	Desk Lamp	130
9	Monitor Arm	18

9 rows in set

CTE vs subquery vs view

	CTE	Derived table (subquery)	View
Named	yes	alias only	yes
Readable top to bottom	yes	no (inside out)	yes
Reusable in the same query	yes	no	yes
Lives after the query	no	no	yes, stored in the database
Recursive	yes (Recursive CTE)	no	no

Exercise: Category revenue share

Write a CTE cat_rev with revenue per product category, then show each category’s revenue and its percentage of total revenue.

Expected resultMySQL 8.4

category	revenue	pct
Furniture	50493	69.9
Electronics	16093	22.3
Stationery	5676	7.9

3 rows in set

Hint 1

In the main query, divide by (SELECT SUM(revenue) FROM cat_rev).

MySQL

WITH cat_rev AS (  SELECT p.category, SUM(oi.quantity * p.price) AS revenue  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category)SELECT category, revenue,       ROUND(100 * revenue / (SELECT SUM(revenue) FROM cat_rev), 1) AS pctFROM cat_revORDER BY revenue DESC;

ResultMySQL 8.4

category	revenue	pct
Furniture	50493	69.9
Electronics	16093	22.3
Stationery	5676	7.9

3 rows in set

Quick quiz

1. How long does a CTE exist?

2. How do you define two CTEs?

WITH a AS (...), b AS (...) SELECT ...

3. Can a later CTE use an earlier one?

CTE FAQ

What is a CTE in SQL?

A Common Table Expression is a named temporary result defined with WITH name AS (SELECT ...) and used by the statement that follows.

What is the difference between a CTE and a subquery?

They often produce the same plan. A CTE is named, defined up front, can be referenced several times and can be recursive; a subquery is written inline where it is used.

Does MySQL support CTEs?

Yes, from MySQL 8.0, including recursive CTEs.

Are CTEs faster than subqueries?

Not inherently. MySQL may merge a CTE into the outer query or materialize it once; the main benefit is readability.