What’s MySQL Recursive Query for?
Recursive queries are one of the effective ways to solve the problem of building a hierarchy or tree and traversing through it. Here are some scenarios where this is useful:
- List down employees under a department or division head from the highest to the lowest rank.
- Getting a list of products under a category and subcategories.
- From a family tree, list the ancestors of someone.
In Math, you probably heard of these:
- Fibonacci sequence
- Factorials
- Sets
And yes, you can do these sorts of recursion in SQL.
Recursion Basics
In plain English, you are doing recursion if you create a procedure or function. And one of the steps in that procedure or function is calling itself repeatedly.
To do that, it must satisfy these 2 things:
- A base case (or cases) as a starting point. Sometimes, this is called an anchor member.
- And a recursive step. Traversing a hierarchy or a sequence of computation occurs here. Sometimes, this is called a recursive member.
To make sense of this, let’s use the family tree example. Let’s do this by traversing forward your family tree.
- The base case is you. It means you and your marriage mate are at the top of the list.
- And the recursive step is listing your children and their children. Moving further are your great-grandchildren, and so on.
And here’s how it goes if you traverse backward:
- The base case is again, you.
- And the recursive step is listing your parents. And then your grandparents, and so on.
Looks simple, right? But how does this happen in SQL?
Hierarchical and Recursive Queries in SQL
Queries in SQL are recursive when you get hierarchical information from the same source. The source is a table or related tables where you derive the tree or hierarchy.
For example, descendants on a family tree come from the same source the parent came from. The nesting levels of each descendant also increase as the source is queried many times over. This happens until the final level is reached. Or at least until it reaches the limit you set.
You can display a list of hierarchies using a recursive query by means of the following:
- using a script or a stored procedure where the recursion logic is defined,
- recursive common table expression, or recursive CTE,
- and repeated self-joins
Let’s examine each.
- The first option is not the easiest. It could be difficult depending on your table structure. Also, SQL is a set-based, declarative language. Describing the logic with control flows in SQL is not its best usage. But if the SQL product you use does not support the second option below, you can use this.
- Then, the second option is available in major SQL database products like MySQL. Recursive CTE is also easier and more flexible.
- Finally, the third option is good only when you have a fixed and not-so-deep hierarchy. Repeated self-joins are also easy but not as flexible as recursive CTE.
How to Create a MySQL Recursive Query
Before going through the steps, here’s the MySQL version I’m using: 8.0.31-0ubuntu0.22.04.1. The code below won’t work on versions lower than MySQL 8. And I’m using dbForge Studio for MySQL version 9.1.21 as my GUI tool. To get your version of MySQL, run the following in your MySQL code editor:
MySQL recursive query is easier explained with an example. So, let’s start with a very simple example using a recursive CTE:
The query above has the 2 things needed for a recursive query:
- SELECT ‘2022-11-22’is the base case. Expect this to be the first row in the result set.
- SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM cte_date_sampleis the recursive step. It calls itself within the same CTE. This will produce the succeeding rows. Each row adds 1 day starting from ‘2022-11-22’ to ‘2022-12-31’.
See the result below.
So, you can craft a MySQL recursive query using recursive CTE with the following steps:
- Define the CTE with WITH RECURSIVE followed by the CTE name.
- Add a base case or the anchor member query.
- Then, add a UNION ALL or UNION DISTINCT to join the anchor member with the recursive member.
- Add the query for the recursive step. It should query itself with the name you define in #1.
- Add a SELECT query below the CTE (or INSERT if you want to insert the result of the recursive CTE to a table).
Here’s the same query with the parts mentioned above:
Now, we’re done with the easy part. Let’s move on to better examples.
Hierarchical MySQL Recursive Query Examples
Employee Hierarchy Example
A common example is a list of employees showing who reports to someone. Let’s define the table structure first.
Next, let’s insert some records.
From these 10 rows, we can see who’s the big boss, who is under him, and so on. Now, it’s so simple. We can also do a simple query and you get the hierarchy as is. But the simplicity of this will make us understand how a recursive query works.
Here’s the recursive query:
Analysis
The first line defines the employee id where traversing starts. We store it in a MySQL local variable. (SET @employee_id = 1;)
Then, we define the recursive CTE and name it employee_ranks. (WITH RECURSIVE employee_ranks AS …). Note this name because this is used within the recursive member.
Then, the first SELECT query is the anchor member or base case. (SELECT id, reports_to, 1 AS employee_level FROM Employee WHERE id = @employee_id). This will simply query the top of the hierarchy. Expect this to be the first row in the result set. We are also adding an employee level and it starts at level 1.
Then, the UNION ALL clause will connect the anchor member with the recursive member. And then, continue querying the next levels.
Then, the second SELECT query is the recursive member. This will be queried repeatedly until all the lower-ranking employees are listed. Notice that the recursive member calls the same CTE (employee_ranks) in the INNER JOIN. On its first pass, it will display the level 2 employees under Sam Beckett. The next 2 passes will display the level 3 employees. And then, it will stop because no further rows and levels exist. Notice that we added 1 for each new employee level found (employee_level + 1 AS employee_level).
And finally, the third SELECT query will display all the results from levels 1 to 3.
See the results below:
The next example will show you a recursive query with more than 1 anchor member and recursive member.
Family Tree Example
If you go back to the recursion basics above, you can see that you can define more than one base case. You can also do that in MySQL. In MySQL recursive query, you can also define more than one recursive member.
With this idea, let’s use a family tree example. And we’ll use a famous family – the British royal family. Here’s the data structure and how to populate data:
We started with Queen Elizabeth II and Prince Philip, Duke of Edinburgh. And the tree includes children and grandchildren down to the youngest to date.
So, what are we going to do next? Let’s have a query traversing upwards the family tree starting with someone. And let’s pick Prince George, son of Prince William.
Here’s the code:
This is quite long. It has 3 base cases or anchor members: The ID of the family member and his 2 parents. Then, it has 2 recursive steps to get the grandparents up to the top of the family tree. We specify ‘N/A’ if the ancestor is not in the table using COALESCE. Notice also that we did an INNER JOIN to itself (Ancestor) in the 2 recursive members.
Here’s the result set:
Alternatively, you can produce similar results with a different presentation using repeated self-joins:
The above code is limited to a fixed level. And the ancestors of the mother, if available, will never appear too. So, the recursive CTE is far superior to query hierarchical data in this case.
Here’s the result of the above code:
That’s how we query hierarchical data using MySQL recursive query.
But there’s more that you need to know about MySQL recursive CTE. And it’s kind of restrictive.
Recursive Member Restrictions in MySQL
The catch with recursive steps or recursive members in MySQL CTE is you can end up in an infinite loop. To fix that problem, MySQL has a limit of 1000 recursions by default. This is defined in @@cte_max_recursion_depth.
In dbForge Studio for MySQL, you can view the Server Variables from the top menu by clicking Database -> Server Variables.
Highlighted above is the limit of the @@cte_max_recursion_depth equal to 1000. You can use a SET statement to increase or decrease its value.
The above SET statement reduces the limit to only 10. Now, try running this query again.
You will end up with a runtime error like this:
Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
So, what can we do from here?
How to Solve MySQL Recursive Query Restrictions Using dbForge Studio for MySQL
You can either follow the recommendation above to increase the limit. Then, re-run the query. Or, you have the following 2 more options:
- Change the WHERE clause with a condition that will make sure the limit will not be reached.
- Add a LIMIT clause.
The first approach is very straightforward. Just increase the limit. Do this in the SQL editor of dbForge Studio for MySQL.
You will not encounter the error after this.
But if it’s a different query, you might end up increasing the limit whenever the error occurs.
The second option gives you more control by changing the WHERE clause. If you really want a limit of 10 depths, here’s a fix to the WHERE clause:
Why an interval of 9 days if we have a @@cte_max_recursion_depth = 10?
Because the first of 10 records is the base case (‘2022-11-22’). Nine days more and we have 10 rows. Try changing the interval to 10. And you will see the runtime error again.
Here’s the result of the successful run with INTERVAL 9 DAY:
Alternatively, the third option uses a LIMIT. Here’s the code that will produce the same result.
The LIMIT clause will make sure the error won’t occur. And you will still see the same result set earlier. Though the WHERE clause is weird because it will never reach the value of ‘2022-12-31’ ever. But you get the point.
Conclusion
You may have other hierarchies in mind aside from the ones we have here. And MySQL recursive CTE can help you with that. You also had another example but not so flexible using repeated self-joins.
You may also download it a GUI tool like dbForge Studio for MySQL. The multiple features provided by this tool like the code suggestions, autocomplete, refactoring, query profiler, etc. help in increased productivity.
Continue Reading:
Best Programming Languages to Learn
Blind SQL Injection – Prevention and Consequences
ABOUT THE AUTHOR
