How to Use Common Table Expressions in MySQL

Common Table Expressions (CTEs) are powerful database features introduced in MySQL 8.0 that allow you to define temporary result sets that can be referenced within a single SELECT, INSERT, UPDATE, or DELETE statement. This article provides a direct, easy-to-understand guide on the syntax for writing both non-recursive and recursive CTEs in modern MySQL versions, complete with practical code templates.

Basic Syntax for a Non-Recursive CTE

A standard CTE acts like a temporary view that exists only during the execution of a query. It makes complex queries easier to read and maintain by breaking them down into logical steps.

The basic syntax for a single CTE in MySQL is as follows:

WITH cte_name AS (
    -- Your subquery goes here
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * 
FROM cte_name;

Key Components: * WITH: The keyword that initiates the CTE. * cte_name: The user-defined name of your temporary result set. * AS (...): The query container that defines the data inside the CTE. * The Main Query: The query immediately following the CTE definition that references the cte_name.

Syntax for Multiple CTEs

You can define multiple CTEs within a single WITH clause by separating them with a comma. This allows you to build subsequent CTEs that reference previously defined CTEs.

WITH cte_first AS (
    SELECT id, category_name 
    FROM categories
),
cte_second AS (
    SELECT product_name, category_id 
    FROM products
    WHERE price > 100
)
SELECT p.product_name, c.category_name
FROM cte_second p
JOIN cte_first c ON p.category_id = c.id;

Syntax for Recursive CTEs

Recursive CTEs are used to query hierarchical data, such as organizational charts or category trees. A recursive CTE references itself and must include the RECURSIVE keyword.

The syntax for a recursive CTE in MySQL is:

WITH RECURSIVE cte_name AS (
    -- Anchor member (initial query)
    SELECT id, parent_id, name, 1 AS level
    FROM organization
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive member (references the CTE itself)
    SELECT o.id, o.parent_id, o.name, r.level + 1
    FROM organization o
    INNER JOIN cte_name r ON o.parent_id = r.id
)
SELECT * FROM cte_name;

Key Components of Recursion: * WITH RECURSIVE: Tells MySQL that the CTE will refer to its own name. * Anchor Member: The initial query that returns the base result set to start the recursion. * UNION ALL or UNION: The operator used to combine the anchor member with the recursive member. * Recursive Member: The query that joins the base table back to the CTE itself. This step repeats until the join condition returns no more rows.