Open In App

PostgreSQL – Recursive Query

Improve
Improve
Like Article
Like
Save
Share
Report

PostgreSQL provides the WITH statement that supports the designing of auxiliary queries also known as CTEs (Common Table Expressions). A recursive query is a query that refers to a recursive CTE. The recursive queries are helpful in many circumstances such as for querying hierarchical data like organizational structure, tracking lineage, etc.

Syntax:

WITH RECURSIVE cte_name AS(
    CTE_query_definition <-- non-recursive term
    UNION [ALL]
    CTE_query definition  <-- recursive term
) SELECT * FROM cte_name;

Let's analyze the above syntax:

  • The non-recursive term is a CTE query definition that forms the base result set of the CTE structure.
  • The recursive term can be one or more CTE query definitions joined with the non-recursive term through the UNION or UNION ALL operator. The recursive term references the CTE name itself.
  • The recursion stops when no rows are returned from the previous iteration.

First, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    manager_id INT
);

Then we insert data into our employee table as follows:

INSERT INTO employees (
    employee_id,
    full_name,
    manager_id
)
VALUES
    (1, 'M.S Dhoni', NULL),
    (2, 'Sachin Tendulkar', 1),
    (3, 'R. Sharma', 1),
    (4, 'S. Raina', 1),
    (5, 'B. Kumar', 1),
    (6, 'Y. Singh', 2),
    (7, 'Virender Sehwag ', 2),
    (8, 'Ajinkya Rahane', 2),
    (9, 'Shikhar Dhawan', 2),
    (10, 'Mohammed Shami', 3),
    (11, 'Shreyas Iyer', 3),
    (12, 'Mayank Agarwal', 3),
    (13, 'K. L. Rahul', 3),
    (14, 'Hardik Pandya', 4),
    (15, 'Dinesh Karthik', 4),
    (16, 'Jasprit Bumrah', 7),
    (17, 'Kuldeep Yadav', 7),
    (18, 'Yuzvendra Chahal', 8),
    (19, 'Rishabh Pant', 8),
    (20, 'Sanju Samson', 8);

Now that the table is ready we can look into some examples.

Example 1:
The below query returns all subordinates of the manager with the id 3.

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 3
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;

Output:

Example 2:
The below query returns all subordinates of the manager with the id 4.

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 4
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads