MySQL recursive CTE is a Common Table Expressions that has a subquery which refers to the CTE name itself.

As part of our Server management services, Bobcares provides solutions to all queries.

Let’s take a deeper look at MySQL’s recursive CTE.

Introduction to MySQL recursive CTE

A recursive CTE is a subquery that refers to itself by name.

• We use the WITH RECURSIVE clause to define recursive CTEs.
• Recursive CTE should have a terminating condition.
• The recursive CTEs can generate series and traverse hierarchical or tree-structured data.

Main parts of MySQL recursive CTE

A recursive CTE is made up of three major components:

• An initial query that forms the CTE structure’s base result set. The first query component is known as an anchor member.
• Recursive query part is a query that refers to the CTE name; thus, it is referred to as a recursive member. A UNION ALL or UNION DISTINCT operator joins the recursive member to the anchor member.
• A condition that ensures the recursion ends when the recursive member returns no rows.

Execution of recursive CTE

A recursive CTE’s execution order is as follows:

• Firstly, divide the members into two categories: anchor and recursive members.
• Secondly, run the anchor member to generate the base result set (R0), and use this base result set for the next iteration.
• Then, with Ri result set as an input, run the recursive member, and make Ri+1 as an output.
• We should repeat step 3 until the recursive member gives us an empty result. It will indicate the termination.
• Finally, using the UNION ALL operator, combine the R0 to Rn result sets.

Syntax:

WITH RECURSIVE
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;


cte_name: Recursive subquery in subquery block.
col1, col2, …colN: Columns created by a subquery.
subquery: A MySql query that uses cte name as its own name to refer to itself.

The column names in the SELECT statement should match the names in the list after cte name. The subquery block’s recursive CTE structure is as follows:


Select col1, col2, ... coln from table_name
UNION [ALL, DISTINCT]
Select col1, col2, ...coln from cte_name
WHERE clause


A non-recursive subquery precedes the recursive CTE.

• The first select statement is a non-recursive statement that provides the result set’s initial rows.
• To add more rows to the previous result set, use UNION [ALL, DISTINCT]. 
  The keywords ALL and DISTINCT are used to include or exclude duplicate rows from the final result set.
• Second select statement is a recursive statement that produces results iteratively until the WHERE clause condition is met.
• Base table for each iteration’s result set is the result set from the previous iteration.
• When the recursive select statement produces no more rows, the recursion ends.

Example for MySQL recursive CTE

Consider the “bst” table below:

The table “bst” above has two columns: “node” and “parent,” which show the value of a node in the binary search tree and its parent value.

Here, the problem is to find the paths of all the nodes in the given “bst.”

Query:


WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL
UNION ALL
SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;


Output:

Explanation:

The non-recursive part of the CTE above will only return a single row containing a root node and its path, which is set to 1.

SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL

Output:
+——+———–+
| node | path |
+——+———–+
| 1 | 1 |
+——+———–+

Recursive part-

SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent


The recursive SELECT statement will return all nodes in bst whose parent is the previous iteration’s node. When the previous iteration’s node (leaf node) has no child nodes in bst, the iteration ends.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, we discovered MySQL recursive CTE and how to use it.