MySQL Recursive CTE (Common Table Expression)

In this tutorial, we will learn what is recursive CTE and how to write a recursive CTE query. Recursive CTE is very similar to regular CTE which starts with the WITH clause but, it calls itself for n interactions until the condition becomes false.

Let’s see how the characteristic of recursive CTE and how it is different from regular CTE.

Common Expression Table (CTE) is only available in mysql version 8.0 and later. So, if you are using a older version of a mysql, consider updating to the latest version.

Also read: MySQL Common Table Expression (CTE)

What is a Recursive CTE?

A recursive CTE is very similar to the recursive function in any programming language. It calls itself and uses the previous result to calculate the next result.

In recursive CTE, we refer to the name of its own CTE to derive the next result. Following are the characteristics and key points of recursive CTE.

It is defined using the WITH clause, similar to regular CTE.
You must specify a condition to terminate the CTE. Else, it will iterate to infinity.
The main use of recursive CTE is to traverse through the hierarchical data such as trees.

Syntax of Recursive CTE

The syntax of a recursive CTE is as follows-

WITH RECURSIVE cte_name(col_names) 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)

The above syntax is divided into three main parts-

initial_query – It gives the base result which is used to calculate the next results using recursion. It is also called an anchor member.
recursive_query – In this query, we refer to its own CTE name, resulting in the recursion. Using the UNION ALL clause, we join the result of initial_query and recursive_query.
condition – It is the most important part as it tells the CTE how long recursion should take place. As soon as the condition becomes false, the CTE terminates.

Recursive Query Restrictions

In the recursive member, you cannot use-

Aggregate functions such as MIN, MAX, SUM, etc.
GROUP BY, ORDER BY.
LIMIT
DISTINCT

The prohibition of the DISTINCT is applied only when you use the UNION operator. If you use UNION DISTINCT, it is permitted.

MySQL Recursive CTE Examples

We will write a recursive CTE to print 1 to 5 digits.

WITH RECURSIVE count_5(count) AS(
SELECT 1
UNION ALL
SELECT count+1 FROM count_5
WHERE count<5
)
SELECT * FROM count_5;Code language: SQL (Structured Query Language) (sql)

Let’s check the result and then we will see how it works.

Simple Recursive Cte — Simple Recursive CTE

Here, the SELECT 1 statement is an anchor member which is a non-recursive query. It will give a base result.

The SELECT count+1 FROM count_5 is a recursive query that will add one to the previous digit. So, at the very beginning, the value of the ‘count’ variable is 1. The recursive query will keep adding one to the previous value. Which will give us incremented values.

The WHERE clause is used to stop the recursive query when the value of the ‘count’ variable reaches 5.

MySQL Recursive CTE Advanced Examples

Now, we will see an advanced example of a MySQL CTE. In this example, we will find a path of all nodes from the root node in the given tree. The graphical representation of the tree is as follows-

Now let’s create a table and insert the node values and their parent values.

CREATE TABLE tree(
node INT,
parent INT
);

INSERT INTO tree VALUES
(1,NULL),(2,1),(3,1),
(4,2),(5,2),(6,3),(7,3);Code language: SQL (Structured Query Language) (sql)

As you can see, the table consists of node ids and their parent ids.

We have to find the paths of all nodes from the root node in the given tree.

WITH RECURSIVE findPath(node,path)
AS(
SELECT node, CAST(1 as CHAR(50)) FROM tree
WHERE parent IS NULL
UNION ALL
SELECT tree.node , CONCAT(findPath.path,' --> ',tree.node)
FROM findPath JOIN tree ON findPath.node = tree.parent
)
SELECT * FROM findPath;Code language: SQL (Structured Query Language) (sql)

Let’s see the result first and then break it down to understand how the above query works.

Here, the first SELECT statement is the anchor member to pick the parent node along with its path which is itself. So, the output for the first query will be 1,1.

The second SELECT statement is the recursive member.

In the recursive SELECT statement, we concatenate the path of the previously found iteration of the same parent using the join clause.

This iteration will end when there is a leaf node.

Conclusion

In this tutorial, we have learned what is recursive CTE and how to write a recursive CTE through simple and advanced examples. Recursive CTE is a very important and useful method to find the paths in the given trees.