Recursive SQL Queries

While traditionally known for handling structured data, SQL has the powerful ability to work with hierarchical or recursive data.

Recursive SQL queries are useful when we query hierarchical data, such as organization structures, directory trees, or anything with a parent-child relationship. In SQL, a recursive query is typically done using a Common Tabel Expression(CTE) with the "WITH RECURSIVE" clause. Additionally, we need to have two separate questions and both of these need to be merged using the UNION ALL operator.

The first part of the query should be a non-recursive, also called the base query, this is basically where we provide our base input based on which the recursion should happen. The second part of the query that is written under the union all operator is the recursive query that we need to write and this is where we need to use the cte table that we have created using with clause. After the second part of the query, we need to provide a termination condition, based on which when the condition fails the recursion will come out that is the recursion recursive SQL query will stop.

If we don't provide a proper termination condition, then the recursion execution will keep on executing infinitely.

Basic Structre of a Recursive CTE

WITH RECURSIVE cte_name AS

(

SELECT query (Non Recursive query or the Base query)

UNION ALL

SELECT query (Recursive query using cte_name [with a termination condition])

)

SELECT * FROM cte_name

Let's solve some problems, where we will see how we can use recursive query

Q1): Display number from 1 to 10 without using any in built functions.

Q 2: Find the hierarchy of employees under a given manager "Asha"

We need to write a query to see how many employees are working under Asha.

Let's create a table employee;

CREATE table employee(

id INT PRIMARY KEY,

manager_id INT,

name VARCHAR(100),

salary DECIMAL(10,2),

designation VARCHAR(100),

FOREIGN KEY (manager_id)

REFERENCES employee(id)

);

INSERT INTO employee(id, manager_id,name,salary,designation)

VALUES(1,2, 'Ram', 10000,'Manager'),

(2,NULL, 'Asha', 30000,'VP Product'),

(3,2, 'Bob', 20000,'Senio Manager'),

(4,3, 'John', 1000,'Intern');

SELECT * FROM employee;

We can see in the employee table, that there are two employees Ram and Bob who are working under Asha, who is VP of Product and Bob who is a Senior Manager manages intern John. As per the Q2, we need to get a list of all the hierarchy of employees who are working under Asha.

Query to get the hierarchy of the employees

WITH RECURSIVE emp_hierarchy as

(

SELECT id, name, manager_id, designation, 1 as lvl FROM employee --- Added 1 as a column

WHERE name = 'Asha' ---- this will be a base query and return all the details realted to asha

UNION ALL

SELECT e.id, e.name, e.manager_id, e.designation, h.lvl+1 as lvl_now FROM emp_hierarchy h

---- output of base query act as an input of this recursive queryacts

JOIN employee e----- we are using join to fetch all the other details related to acts to asha on join condition

ON h.id = e.manager_id ---- this will also act as a termination condition

)

SELECT * FROM emp_hierarchy

In the org chart hierarchy, we can see Asha holds the number one position, Ram and Bob hold the second position and John holds the third.

Q3: Find the hierarchy of manager for a given employee John

We need to write a query to find one employee John and see who are the managers above him.

WITH RECURSIVE emp_hierarchy as

(

SELECT id, name, manager_id, designation, 1 as lvl FROM employee

WHERE name = 'John'

UNION ALL

SELECT e.id, e.name, e.manager_id, e.designation, h.lvl+1 as lvl_now

FROM emp_hierarchy h

JOIN employee e

ON h.manager_id = e.id

)

SELECT * FROM emp_hierarchy

Recursive queries in SQL are an elegant solution for dealing with hierarchical data, allowing us to traverse relationships and generate complex datasets efficiently. From organizational hierarchies to more abstract examples like Fibonacci sequences, recursion can open new possibilities in SQL.

Understanding and utilizing recursive queries can significantly enhance our data processing capabilities, especially when working with multi-level data structures. However, always ensure our recursion is well-defined to avoid infinite loops or excessive resource consumption.