MySQL Recursive CTE

Summary: in this tutorial, you will learn about MySQL recursive CTE and how to use it to traverse hierarchical data.

Notice that a common table expression (CTE) is only available in MySQL version 8.0 or later. Therefore, ensure that you have the right version of MySQL installed to use the statements in this tutorial.

Introduction to MySQL recursive CTE

In MySQL, a recursive Common Table Expression (CTE) is a named temporary result set that references itself in the recursive member, enabling the hierarchical traversal or iteration over data until a specified termination condition is met.

The following illustrates the syntax of a recursive CTE:

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;Code language: SQL (Structured Query Language) (sql)

A recursive CTE consists of three main parts:

An initial query that forms the base result set of the CTE structure. The initial query part is referred to as an anchor member.
A recursive query part is a query that references the CTE name, therefore, it is called a recursive member. The recursive member is joined with the anchor member by a UNION ALL or UNION DISTINCT operator.
A termination condition that ensures the recursion stops when the recursive member returns no row.

The execution order of a recursive CTE is as follows:

First, separate the members into two: anchor and recursive members.
Next, execute the anchor member to form the base result set ( R0) and use this base result set for the next iteration.
Then, execute the recursive member with Ri result set as an input and make Ri+1 as an output.
After that, repeat the third step until the recursive member returns an empty result set, in other words, the termination condition is met.
Finally, combine result sets from R0 to Rn using UNION ALL operator.

Recursive member restrictions

The recursive member must not contain the following constructs:

Aggregate functions e.g., MAX, MIN, SUM, AVG, COUNT, etc.
GROUP BY clause
ORDER BY clause
LIMIT clause
DISTINCT

Note that the above constraint does not apply to the anchor member. Furthermore, the restriction on using DISTINCT only applies when you use UNION operator. If you use the UNION DISTINCT operator, the DISTINCT is permitted.

In addition, the recursive member can reference the CTE name only once in its FROM clause and not in any subquery.

Basic MySQL recursive CTE example

See the following simple recursive CTE example:

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;Code language: SQL (Structured Query Language) (sql)

In this example, the following query:

SELECT 1Code language: SQL (Structured Query Language) (sql)

is the anchor member that returns 1 as the base result set.

The following query

SELECT n + 1
FROM cte_count 
WHERE n < 3Code language: SQL (Structured Query Language) (sql)

is the recursive member because it references the name of the CTE which is cte_count.

The expression n < 3 in the recursive member is the termination condition. Once n equals 3, the recursive member returns an empty set that will stop the recursion.

The following picture illustrates the elements of CTE above:

The recursive CTE returns the following output:

The execution steps of the recursive CTE are as follows:

First, separate the anchor and recursive members.
Next, the anchor member forms the initial row ( SELECT 1) therefore the first iteration produces 1 + 1 = 2 with n = 1.
Then, the second iteration operates on the output of the first iteration (2) and produces 2 + 1 = 3 with n = 2.
After that, before the third operation ( n = 3), the termination condition ( n < 3) is met therefore the query stops.
Finally, combine all result sets 1, 2, and 3 using the UNION ALL operator

Using MySQL recursive CTE to traverse the hierarchical data

First, create a new database called mydb:

CREATE DATABASE IF NOT EXIST mydb;

Second, change the current database to mydb:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

Third, insert some rows into the employees table:

INSERT INTO employees VALUES
    (1, 'John Doe', NULL),         -- CEO, no manager
    (2, 'Jane Smith', 1),          -- Manager, reports to CEO
    (3, 'Bob Johnson', 2),         -- Employee, reports to Jane Smith
    (4, 'Alice Brown', 2),         -- Employee, reports to Jane Smith
    (5, 'Charlie Davis', 3);       -- Employee, reports to Bob Johnson
Code language: PHP (php)

Finally, traverse the hierarchical data in the employees table using a recursive CTE:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL -- Anchor member (root of the hierarchy)
        
    UNION ALL
    
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM
        employees e
    INNER JOIN
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- Recursive member
)
-- Final query to select from the CTE
SELECT
    employee_id,
    employee_name,
    manager_id,
    level
FROM
    EmployeeHierarchy
ORDER BY
    level, employee_id;Code language: PHP (php)

Output:

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | John Doe      |       NULL |     0 |
|           2 | Jane Smith    |          1 |     1 |
|           3 | Bob Johnson   |          2 |     2 |
|           4 | Alice Brown   |          2 |     2 |
|           5 | Charlie Davis |          3 |     3 |
+-------------+---------------+------------+-------+
5 rows in set (0.01 sec)Code language: JavaScript (javascript)

How it works.

Define The CTE with the name EmployeeHierarchy.
Define an anchor member that selects employees who do not have a manager (manager_id IS NULL), starting with the root of the hierarchy (CEO).
Use a recursive member to join the employees table with the CTE on the condition that the manager_id in the employees table matches the employee_id in the CTE, effectively traversing the hierarchy.
Select information from the CTE, including the employee’s ID, name, manager’s ID, and the level in the hierarchy in the final query. And sort the result set by level and employee ID.

Summary

Use MySQL recursive CTE to traverse hierarchical data.

Was this tutorial helpful?