What a CTE does
A common table expression (CTE) is a named query result introduced by a WITH clause and available to the statement that follows. It gives a complex SELECT a readable sequence of steps without creating a permanent table. CTEs are useful when a query has an intermediate aggregate, when the same logical stage is referenced more than once, or when recursion is needed to walk hierarchical data. MySQL added CTE support in MySQL 8.0, so verify the production server version before relying on this syntax.
A nonrecursive CTE is similar in purpose to a derived table, but its name can make the query's intent clearer. It exists only for one statement and does not persist data. A CTE is not automatically a performance optimization or a cache. Depending on query shape and MySQL version, the optimizer may merge or materialize it. Choose it for clarity first, then inspect the plan when performance matters.
WITH monthly_sales AS (
SELECT customer_id,
DATE_FORMAT(created_at, '%Y-%m-01') AS month_start,
SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id, DATE_FORMAT(created_at, '%Y-%m-01')
)
SELECT customer_id, month_start, revenue
FROM monthly_sales
WHERE revenue >= 1000
ORDER BY month_start, customer_id;The named stage calculates monthly revenue by customer, and the outer query filters that summary. The condition on revenue belongs after aggregation because it depends on an aggregate result. The sample formats a month label for readability; production reporting should make time-zone and calendar boundaries explicit and consider range-filtering source timestamps so unnecessary periods are not scanned.
Multiple CTEs and readable stages
A WITH clause can define multiple named CTEs separated by commas. This can separate filtering, aggregation, and ranking into distinct parts of one statement. Use names that explain the row grain and purpose, such as paid_orders_by_customer rather than a generic temp. Each stage should have a clear output contract: what one row represents, which columns are available, and whether duplicate values are expected. If a sequence of CTEs becomes harder to understand than the original query, simplify it or use a view for a reusable interface.
CTEs help reason about complex queries, but repeated references do not guarantee that the server executes a stage only once. The optimizer's strategy can vary. Avoid relying on side effects in query expressions, and verify important behavior using the target server. Use EXPLAIN to understand the resulting plan and compare it with a derived table or a simpler query when appropriate.
Recursive CTEs and hierarchies
A recursive CTE refers to itself and can walk parent-child data such as an organizational chart or category tree. It contains an anchor member that selects the initial rows and a recursive member that joins the current set to the next level. UNION ALL commonly combines these rows. The recursive step must make progress toward termination. Cycles or an incorrect join can produce excessive work, so use depth limits or cycle-detection techniques appropriate to the data and version.
WITH RECURSIVE descendants AS (
SELECT employee_id, manager_id, 0 AS depth
FROM employees
WHERE employee_id = 100
UNION ALL
SELECT e.employee_id, e.manager_id, d.depth + 1
FROM employees AS e
JOIN descendants AS d
ON e.manager_id = d.employee_id
WHERE d.depth < 20
)
SELECT employee_id, manager_id, depth
FROM descendants
ORDER BY depth, employee_id;The anchor starts at employee 100; each recursive iteration adds direct reports. The depth predicate bounds traversal but does not by itself detect cycles in every data model. Ensure identifiers and manager relationships are constrained and test malformed hierarchies. MySQL also has recursion limits; check the deployed configuration and manual before planning very deep traversals.
Version, ordering, and common mistakes
CTE syntax is version-specific across database products. Do not copy recursive syntax from PostgreSQL or SQL Server without checking MySQL behavior. A CTE's internal ordering does not guarantee final result order; use an outer ORDER BY. Keep aliases explicit and avoid shadowing table names in ways that make references confusing. Recursive CTEs need compatible column types between anchor and recursive members; an unexpectedly narrow inferred type can cause truncation or errors, so cast the anchor deliberately when the recursive value grows.
When to choose CTEs
Use a CTE to name a meaningful intermediate result or to express recursion in one statement. Use a derived table when a small nested expression is clearer inline, a view when a query interface needs reuse across statements, and a temporary table when multiple statements need session-scoped materialized data. Each has distinct lifetime, optimization, and operational behavior. Test the final statement with representative data and inspect its execution plan.
CTEs improve query structure when their stages reflect the problem's logic. They do not replace indexing or measurement. Continue with subqueries, window functions, and EXPLAIN.