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:

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:

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:

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:

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

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

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!