CTE and Recursion · Advanced

WITH

using CTEs to split complex queries.

This topic uses window functions or CTEs; MySQL requires version 8.0 or later.

Overview

WITH defines a common table expression visible to the current statement and lets a complex query be split into named steps. A CTE improves readability, reuse, and incremental debugging, but does not automatically guarantee better performance; MySQL 8.0 optimization still depends on query shape and version. Separate multiple CTEs with commas, and later definitions may reference earlier ones.

Basic syntax

WITH high_orders AS (SELECT * FROM orders WHERE amount > 1000) SELECT * FROM high_orders;

Examples

WITH example

Define high_orders for amounts above 1000, then read rows from that named intermediate result.

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Run result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100

Common issues

Does a CTE automatically improve performance?

No. It primarily improves structure; materialization and performance depend on the database version and optimizer.

Can one WITH define several CTEs?

Yes. Separate definitions with commas, and later CTEs may reference earlier ones.

How long is a CTE visible?

It is normally scoped to the single statement that immediately follows it.

Was this explanation clear?

RECURSIVE CTE →