How to Use CTE in MySQL

Galaxy Glossary

How do I write a CTE in MySQL?

A Common Table Expression (CTE) defines a temporary, named result set inside a WITH clause that can be referenced within a single MySQL statement.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does a CTE do in MySQL?

A CTE creates a temporary, readable alias for a subquery. It simplifies complex joins, improves readability, and enables recursion—all without persisting data beyond the current statement.

Why choose a CTE over a subquery?

CTEs improve clarity, allow multiple references to the same derived set, and make recursive logic possible. They also keep business logic close to the final query, easing maintenance.

What is the basic CTE syntax?

The WITH clause precedes the main query. Each CTE is named, may include an optional column list, and is terminated by a comma if another CTE follows.

Basic CTE syntax

WITH cte_name (col1, col2) AS ( SELECT ... ) SELECT ... FROM cte_name;

Recursive CTE syntax

WITH RECURSIVE cte_name AS ( SELECT ... -- anchor UNION ALL SELECT ... FROM cte_name ... -- recursive ) SELECT * FROM cte_name;

How do I chain multiple CTEs?

Separate each CTE with a comma. The final SELECT can reference any preceding CTE.

Practical example: Customers and their total spend

See the example query below to calculate each customer’s lifetime spend and return only those above $1,000.

Best practices for CTEs

Keep each CTE focused on one task, name CTEs descriptively, and avoid unnecessary recursion. Always test performance against equivalent subqueries.

Common mistakes with CTEs

Review the pitfalls section below to avoid scope, ordering, and performance issues.

Why How to Use CTE in MySQL is important

How to Use CTE in MySQL Example Usage


WITH customer_spend AS (
    SELECT c.id,
           c.name,
           SUM(o.total_amount) AS lifetime_spend
    FROM Customers c
    JOIN Orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name
)
SELECT *
FROM customer_spend
WHERE lifetime_spend > 1000
ORDER BY lifetime_spend DESC;

How to Use CTE in MySQL Syntax


-- Basic CTE
WITH high_value_orders AS (
    SELECT id, customer_id, total_amount
    FROM Orders
    WHERE total_amount > 1000
),
customer_totals AS (
    SELECT c.id AS customer_id,
           c.name,
           SUM(o.total_amount) AS lifetime_spend
    FROM Customers c
    JOIN Orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name
)
SELECT ct.*
FROM customer_totals ct
JOIN high_value_orders hvo ON hvo.customer_id = ct.customer_id;

-- Recursive CTE (example: product category hierarchy)
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM Categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM Categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree;

Common Mistakes

Forgetting the final query: A CTE must be immediately followed by a SELECT/INSERT/UPDATE/DELETE that references it. Fix by adding the main statement right after the WITH clause.
Relying on ORDER BY inside a non-recursive CTE for deterministic output. MySQL may reorder results when the outer query optimizes. Apply ORDER BY in the final SELECT instead.

Frequently Asked Questions (FAQs)

What MySQL version supports CTEs?

CTEs are available starting in MySQL 8.0. Prior versions do not recognize the WITH clause.

Can I update data using a CTE?

Yes. Use the CTE to define rows to update, then issue an UPDATE that joins or references the CTE.

Are CTEs stored in memory?

MySQL materializes CTEs as temporary tables when needed. The optimizer may inline them when beneficial. Memory or disk usage depends on size and configuration.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie