PostgreSQL Recursive Queries

Not convoluted at all

Common Table Expression (CTE) is a form of recursive SQL that makes a temporary result set of a query. It is an alternative to subqueries, views and user-defined functions that is useful for generating summary reports of a query. You can look at this article if you have any questions about what recursion is.

I just threw a lot of words at you. I think the best way to learn recursive queries is to look at an example. Let’s look at a CTE in PostgreSQL.

This is the structure of a recursive query:

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

A CTE consists of 3 elements:

• Non-recursive term: the base result set of the CTE structure.
• Recursive term: one or more CTE query definitions joined with the non-recursive term using the UNION or UNION ALL operator. The recursive term references to the CTE name itself.
• Termination check: the recursion stops when no rows are returned from the previous iteration.

Let’s build a test case:

INSERT INTO employees (
   employee_id,
   full_name,
   manager_id
)
VALUES
   (1, 'Michael North', NULL),
   (2, 'Megan Berry', 1),
   (3, 'Sarah Berry', 1),
   (4, 'Zoe Black', 1),
   (5, 'Tim James', 1),
   (6, 'Bella Tucker', 2),
   (7, 'Ryan Metcalfe', 2),
   (8, 'Max Mills', 2),
   (9, 'Benjamin Glover', 2),
   (10, 'Carolyn Henderson', 3),
   (11, 'Nicola Kelly', 3),
   (12, 'Alexandra Climo', 3),
   (13, 'Dominic King', 3),
   (14, 'Leonard Gray', 4),
   (15, 'Eric Rampling', 4),
   (16, 'Piers Paige', 7),
   (17, 'Ryan Henderson', 7),
   (18, 'Frank Tucker', 8),
   (19, 'Nathan Ferguson', 8),
   (20, 'Kevin Rampling', 8);

This is a recursive query that selects all subordinates of the manager with the id of 2:

WITH RECURSIVE subordinates AS (
   SELECT
      employee_id,
      manager_id,
      full_name
   FROM
      employees
   WHERE
      employee_id = 2
   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;

Let’s break this down!

This is the non-recursive term that selects the employee id of 2.

SELECT
      employee_id,
      manager_id,
      full_name
   FROM
      employees
   WHERE
      employee_id = 2

This is the recursive term that selects the employees that are subordinates of the manager_id of 2 (meaning that they have a manager_id that is a child of the manager_id of 2)until the termination check is reached where nothing is returned (where there are no children of manager_id 2).

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

Lastly, this is where we display what is being returned in the CTE.

SELECT
   *
FROM
   subordinates;

The results:

employee_id | manager_id |    full_name
-------------+------------+-----------------
           2 |          1 | Megan Berry
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling

Here we are displaying all of the subordinates of the manager_id 2.

The cool thing about CTE’s are that we can drill down to specific aggregate functions that we may find useful in a summary report.

One example:

WITH RECURSIVE subordinates AS (
   SELECT
      employee_id,
      manager_id,
      full_name
   FROM
      employees
   WHERE
      employee_id = 2
   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
   manager_id, COUNT(employee_id)
FROM
   subordinates
GROUP BY manager_id;

This gives us a count of all of the employees that are under each manager that is under manager_id 2.

manager_id | count 
------------+-------
          2 |     4
          7 |     2
          1 |     1
          8 |     3

Second example:

WITH RECURSIVE subordinates AS (
   SELECT
      employee_id,
      manager_id,
      full_name
   FROM
      employees
   WHERE
      employee_id = 2
   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
   COUNT(employee_id) as "subordinate count"
FROM
   subordinates;

This gives us a count of all the employees under manager_id 2.

subordinate count 
------------------------
                     10

I hope that you found this useful. Thanks for reading!