JIYIK CN >

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

MySQL recursive query

A 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;

Above is the pseudo code of a recursive SQL query. Let's look into it more deeply.

with clause

with [Recursive] CTE(user_defined name) AS

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

This keyword syntax forms the basis of our queries.

CTE, known as Common Table Expression, is a user-defined temporary named result set that is later used in a subsequent SELECT statement.

Basic Query

SELECT query (Non Recursive query or the Base query)

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

UNION Statement

UNION

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

Recursive query

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

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

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

SELECT * from CTE_name

This query looks at the table created using this recursive technique.

Implementing recursive queries in MySQL

Let's understand how this works by implementing this query correctly.

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. This is how it executes.

The query starts with the with clause and the recursive keyword and we use print_number as our CTE. Once we execute the code, the program looks for a base case because it knows that this is a recursive query.

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

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

The results returned from the base query become the input to the recursive query. In the second iteration, the recursive query uses the output data from the previous query and checks 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 by the second iteration as input.

This is the only logic where recursion occurs.

The third iteration is performed and the resulting output of this iteration is used as the input for the fourth iteration. The fourth iteration then adds the output of the base query to its input.

It happens recursively until the termination condition is met.

This table, number_printer, is very important here. This table must be used to make this query recursive. For the completion of the query, the termination condition is written using the WHERE clause.

Now, let's take another example of recursive query.

Example 2: Hierarchy

Suppose we have an organization with a certain hierarchy. At the top there is a manager, two managers are directly supervised by that manager, and each of those two managers has a manager who is directly supervised by them.

Now, we have created a table called network with columns like id, name and ManagerID. They define the employee's ID, name and manager ID respectively.

As shown in the following table:

In the above table, we have obtained the name and manager ID of the employees, but we want to assign the grade to these managers correctly according to the hierarchy we discussed earlier. Therefore, 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 recursive query code above will solve the problem as we wish. Let’s take a closer look at what this code does.

WithThe clause Recursiveis used with the keyword according to the syntax, where the CTE is named the hierarchy.

In the basic query, it selects id and name from the network table and gets the value where id equals 1. It then sets its value AS level to the integer 1.

Levels are used here to show the manager's position in the hierarchy.

We have completed the basic query; now the code uses the UNION clause to combine the basic query with the recursive query.

The recursive query selects id and name from the network table. Here n is the alias of the network table and the dot (.) here acts to restrict the values ​​to be fetched from the particular table that we provide as input before it.

h is an alias for the level table, and the clause level+1 ensures that the integer value retrieved from the level table is incremented by 1.

This part of the recursive query takes the name and id from the network table, the integer value from the hierarchy, adds 1 to it, and stores the output in level. This query updates and defines the level of the manager.

In the next part of the query, the JOIN clause is used and by default it runs as an inner join. Here, the ManagerID from the network table is matched with the ID from the hierarchy table and inner joined with the above query.

The logic is that when a manager ID matches the ID, the rank of that entity is assigned by adding the manager's rank by 1. In this way, the rank of supervision is assigned.