MySQL CTE: Common Table Expressions with WITH

A common table expression (CTE) is a named query result that you can use within one SQL statement. In MySQL, define a CTE with the WITH clause, then refer to its name from the main query. MySQL supports CTEs in MySQL 8.0 and later.

CTEs are useful when a query has several logical steps. Naming each step can make the SQL easier to read, and a CTE can be referenced more than once in the statement. A CTE is not a permanent table or a result that remains available to the next statement.

MySQL CTE syntax

The basic syntax for a CTE used by a SELECT statement is:

WITH cte_name [(column_name, ...)] AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

The AS clause must contain a parenthesized query. The optional column-name list gives names to the CTE’s result columns. If you omit it, MySQL uses the column names from the query inside AS.

MySQL CTE example

Suppose an orders table has order_id, customer_id, amount, and status columns. The following query finds customers whose paid orders total at least 500:

WITH paid_orders AS (
    SELECT order_id, customer_id, amount
    FROM orders
    WHERE status = 'paid'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount >= 500
ORDER BY total_amount DESC;

This query defines two CTEs in one WITH clause:

1. paid_orders filters the source rows to paid orders.
2. customer_totals groups those rows by customer and calculates each total.
3. The final SELECT filters and sorts the totals.

Separate CTE definitions with commas. A later CTE can refer to an earlier one in the same WITH clause, as customer_totals refers to paid_orders here. You can also refer to one CTE several times in the statement.

MySQL CTE rules and common mistakes

• A CTE exists only for the statement that contains its WITH clause. To use the same logic in another statement, define the CTE again.
• Put multiple CTEs in one WITH clause and separate them with commas. Do not write two consecutive WITH clauses at the same query level.
• CTE names must be unique within the clause. Define a CTE before another CTE that refers to it.
• A regular CTE does not refer to itself. To generate a sequence or walk a hierarchy, use a recursive CTE and include WITH RECURSIVE.
• A CTE can make a query easier to organize, but its syntax alone does not guarantee that the query will run faster. Check the execution plan when performance matters.

If you only need a nested query in one place, a MySQL subquery or derived table may be enough. A CTE gives that intermediate query a name before the main statement and can make multi-step logic easier to follow.

Further reading

See the MySQL 8.0 Reference Manual: WITH (Common Table Expressions) for the complete syntax and additional restrictions.