How to write recursive queries in SQL?

Let’s say you want to find out all the subordinates to a manager recursively.

That is say Kiran reports to Guru and Gayathri reports to Kiran , then the subordinates of Guru are both Kiran and Gayathri.

This can be done using recursive queries.

Let’s see how to do it in PostgreSQL. This is supported in other relational tables as well though the syntax may vary a bit.

First,

Let’s create an employee table.

This has three columns:

  • the employee id
  • the employee name
  • the manager id (who is also an employee and hence will have a separate row entry with an employee id)

Here is the CREATE table query:



CREATE TABLE EMPLOYEE(
	EMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY, 
	EMPLOYEE_NAME VARCHAR(100),
	MANAGER_ID INTEGER);

I inserted 10 records in the table.

Here is the list of records:

employee_idemployee_namemanager_id
1VIJAY5
2KATHIR7
6KRITHIKA5
8BANU7
3RODRIQUES6
5KANNAN2
9MAHESH4
4GANESH9
7RAHUL4
10KATHERINE3

As you see,

the manager of employee Vijay is Kannan(employee id 5)

And the manager of employee Krithika is also Kannan

In turn Krithika has her own reportee Rodriques.

And Rodriques has his own reportee Katherine.

So the entire list of subordinates of the manager Kannan include :

Vijay,Krithika,Rodriques and Katherine.

Now let’s use recursive query also called Common Table Expressions to retrieve the subordinates.

Here is the query:


WITH RECURSIVE SUBORDINATES AS (

   	SELECT
	     *
   	FROM
	     EMPLOYEE 
   	WHERE 
	     EMPLOYEE_ID = 5
	
UNION
	
  	SELECT
	    E.* 
  	FROM 
	    EMPLOYEE E
	
  	INNER JOIN 
	
	    SUBORDINATES S
  	ON 
	    E.MANAGER_ID = S.EMPLOYEE_ID
	
) SELECT * FROM SUBORDINATES;

And here is the result:

employee_idemployee_namemanager_id
5KANNAN2
1VIJAY5
6KRITHIKA5
3RODRIQUES6
10KATHERINE3

All the subordinates along with the manager has been returned!

Let’s analyse the query.

The query has this high level format:

WITH RECURSIVE SUBORDINATES AS () SELECT * FROM SUBORDINATES;

SUBORDINATES is the Common Table Expression(CTE) we create to evaluate the subordinates. CTE is a temporary table created and used only during query execution.

Below is the query to create the CTE (passed as a parameter to () in the above query)

	SELECT
	     *
   	FROM
	     EMPLOYEE 
   	WHERE 
	     EMPLOYEE_ID = 5
	
UNION
	
  	SELECT
	    E.* 
  	FROM 
	    EMPLOYEE E
	
  	INNER JOIN 
	
	    SUBORDINATES S
  	ON 
	    E.MANAGER_ID = S.EMPLOYEE_ID

The CTE takes union of two queries.

The first query is the non recursive query:


   	SELECT
	     *
   	FROM
	     EMPLOYEE 
   	WHERE 
	     EMPLOYEE_ID = 5

and the second one the recursive query:

  	SELECT
	    E.* 
  	FROM 
	    EMPLOYEE E
	
  	INNER JOIN 
	
	    SUBORDINATES S
  	ON 
	    E.MANAGER_ID = S.EMPLOYEE_ID

The non recursive query is the base query which returns the manager record:

employee_idemployee_namemanager_id
5KANNAN2

Using the result of the non recursive query , the recursive query computes the subordinates of the manager recursively.

The recursive query creates an inner join on the CTE table.

The result is calculated in iterations .

In the first iteration the direct subordinates of the manager Kannan (id : 5) is returned:

employee_idemployee_namemanager_id
1VIJAY5
6KRITHIKA5

In the second iteration , the above results are used and the subordinates of the employee ids 1 and 6 are returned:

employee_idemployee_namemanager_id
3RODRIQUES6

In the third iteration the above result is used and the subordinates of the employee id 3 are returned:

employee_idemployee_namemanager_id
10KATHERINE3

And in the fourth iteration the subordinates of the above employee id 10 are queried. Since there are none an empty result is returned and the execution stops.

That’s it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s