MySQL Recursive Query

1. MySQL Recursive Query
2. Implement Recursive Query in MySQL

In this guide, we will learn about MySQL’s recursive query. How to write a recursive query in SQL and how it works will be explained for your better understanding in this guide.

MySQL Recursive Query

The recursive query in SQL is a subquery; as the name suggests, it works recursively. It has a base case, a user-defined name and a recursive case with a terminating condition.

with [Recursive] CTE(user_defined name) AS
(
    SELECT query (Non Recursive query or the Base query)
    UNION
    SELECT query (recursive query using the name [with a termination condition])
)
SELECT * from CTE_name;

The above is the pseudo-code of the recursive SQL query. Let’s take a deeper dive into it.

the with Clause

with [Recursive] CTE(user_defined name) AS

The with clause of SQL is used at the start, along with the Recursive keyword of SQL. The AS keyword is then used, followed by the CTE, a user-defined name.

This syntax of keywords constitutes the base case of our query.

CTE is called common table expression, a temporary named result set defined by the user to be used later in a subsequent SELECT statement.

the Base Query

SELECT query (Non Recursive query or the Base query)

This is the first of the two queries called the Base query. It is a non-recursive query, and the base input will be provided here based on which the recursion would happen.

the Union Clause

UNION

The Union operator is used in the middle to merge our first and second queries.

the Recursive Query

SELECT query (recursive query using the name [with a termination condition])

This is the recursive query where we need to provide the CTE table that we had created earlier using the with clause and also provide the termination condition, which, when it becomes true, would terminate the recursion.

The Base and Recursive query are written inside the parentheses (), as shown in the above pseudo-code.

SELECT * from CTE_name

This query will view the table created using this recursive technique.

Implement Recursive Query in MySQL

Let’s understand the working of this query by implementing it properly.

with recursive number_printer AS
(
    SELECT 1 AS digit
    UNION
    SELECT digit+1 FROM number_printer WHERE digit<5
)
SELECT * FROM number_printer

The above table is the result of our program execution. Here’s how it got executed.

The query starts using the with clause and recursive keyword, and we used print_number as our CTE. As soon as we execute the code, the program looks for a base case as it understands that it is a recursive query.

In the base query, we started the base case from 1, and the alias used here is digit. In the first iteration of execution, the output will be the result returned from the base query.

The initial records would be the output of the base query.

The result returned from the base query would become the input for the recursive query. In the second iteration, the recursive query uses the output data from the previous query and checks for the termination condition.

If the termination condition is met, the iteration stops; otherwise, it enters the third iteration. The third iteration uses the output returned in the second iteration as the input.

This is the only logic based on which recursion happens.

The third iteration executes, and the resultant output of this iteration is used as input for the fourth iteration. The fourth iteration then adds the output of the base query with its input.

It keeps happening recursively until a termination condition is met.

The table, namely number_printer, is of sterling importance here. It is mandatory to use the table to make this query recursive. For the completion of the query, the termination condition is written using the WHERE clause.

Now, let’s have another example regarding the recursive query.

Example 2: Hierarchy

Suppose we have an organization that has some hierarchy. There is a manager at the top, and two managers are under the direct supervision of that manager, and each of these two managers has a manager under their direct supervision.

Now, we have created a table called network with columns such as id, name, and ManagerID. These define the ID of the employee, its name, and the manager ID it has, respectively.

It is shown in the following table:

In the above table, we got the names and manager IDs of the employees, but we want to properly assign ranks to these managers based on the hierarchy we discussed earlier. For that reason, we use the following code:

WITH RECURSIVE hierarchy AS
( SELECT id, name, 1 AS level FROM network WHERE id = 1
UNION
SELECT n.id, n.name, h.level+1 FROM hierarchy h
JOIN network n on h.id = n.ManagerID
)
SELECT * FROM hierarchy

The above recursive query code would solve the problem as we wanted. Let’s dive in and understand what this code does.

The With clause is used along with the Recursive keyword as per the syntax, and the CTE here is named hierarchy.

In the base query, it selects the id and name from the network table and fetches the value with id equal to 1. Then, it will set its value AS level to integer 1.

The level is used here to showcase the manager’s position in the hierarchy.

We are done with the base query; now, the code merges the base query with the recursive query using the UNION clause.

The recursive query selects id and name from the network table. The n here is an alias for the network table, and what this dot (.) does here is that it bounds the value to be fetched from the specific table we give before it as input.

h is an alias for the hierarchy table, and clause level+1 ensures the addition of 1 to the integer value fetched from the hierarchy table.

This part of the recursive query takes name and id from the network table, the integer value comes from hierarchy, the addition of 1 is done to it, and the output is saved in level. This query updates and define the level of the managers.

In the next part of a query, the JOIN clause is used, which operates as Inner join by default value. Here, the ManagerID from the network table is matched with the id from the hierarchy table and is inner joined with the above query.

The logic is that when the manager id is matched with the ID, the level to that entity is assigned by adding 1 to the level of the manager. In this way, the ranking of supervision is assigned.