In this blog, you will study about MySQL recursive CTE and how to use it to navigate hierarchical data.
Notice that mutual table expression or CTE only available in MySQL version 8.0 or later. Hence, you must have the right version of MySQL installed to use the declarations in this tutorial.
Overview to MySQL recursive CTE
A recursive common table expression (CTE) is a CTE that has a subquery which mentions to the CTE name itself. The subsequent exemplifies the syntax of a recursive CTE
A recursive CTE comprises of three key parts:
A primary query that forms the base result set of the CTE construction. The primary query part is stated to as an presenter member.
A recursive query part is a query that orientations to the CTE name, therefore, it is called a recursive participant. The recursive related is joined with the anchor member by a UNION ALL or UNION DISTINCT operative.
A conclusion condition that confirms the recursion stops when the recursive fellow returns no row.
The implementation order of a recursive CTE is as follows:
First, distinct the members into two: anchor and recursive affiliates.
Next, execute the anchor member to form the base result set (R0) and use this base result set for the resulting iteration.
Then, execute the recursive member with Ri result set as an input and make Ri+1 as an yield.
After that, recurrence the third step until the recursive member returns an blank result set, in other words, the finish condition is met.
Finally, combine result sets from R0 to Rn using UNION ALL operator.
Recursive member restrictions
The recursive member must not comprise the subsequent constructs:
Aggregate functions e.g., MAX, MIN, SUM, AVG, COUNT, etc.
GROUP BY clause
ORDER BY clause
LIMIT clause
DISTINCT
Note that the above restriction does not apply to the anchor member. Also, the proscription on DISTINCT applies only when you use UNION operator. In case you use the UNION DISTINCT operator, the DISTINCT is allowed.
In addition, the recursive member can only position the CTE name once and in its FROM clause, not in any subquery.
Simple MySQL recursive CTE example
See the subsequent simple recursive CTE sample:
In this sample, the subsequent query:
is the anchor member that returns 1 as the base result set?
The subsequent query
is the recursive member because it situations to the name of the CTE which is cte_count.
The expression < 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 next picture demonstrates the elements of the CTE above:
The recursive CTE returns the subsequent output:
The execution steps of the recursive CTE is 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
We will use the employees table in the classicmodels sample database for the demo.
The employees table has the reportsTo field that references to the employeeNumber field. The reportsTo column stores the ids of managers. The top manager does not report to anyone in the company’s organization structure, therefore, the value in reports To column is NULL.
You can apply the recursive CTE to query the whole organization structure in the top-down manner as follows:
Let’s break the query into minor parts to make it easier to recognize.
First, form the anchor member by using the subsequent query:
This query (anchor member) returns the top manager whose reportsTo is NULL.
Second, make the recursive member by position to the CTE name, which is employee_paths in this situation:
This query (recursive member) returns all straight reports of the manager(s) until there are no more straight reports. The if the recursive member returns no straight reports, the recursion stops.
Third, the query that uses the employee_paths CTE joins the result set returned by the CTE with the offices table to make the result set.
The subsequent is the output of the query:
In this blog, you have educated about MySQL recursive CTE and how to use it to crisscross hierarchical data.
Comments