Quick Tip: WITH Clause Recursion

Posted June 11, 2021 by Jim Knicely, Vertica Field Chief Technologist

SQL

Starting with version 10.1.x, Vertica supports recursive queries. These are incredibly useful in modeling and working with self-referential hierarchical data.

Let’s take a look at a natural instance of this form of data: the manager-subordinate relationship.

Example:
The following table contains employees (EMP_NAME), their IDs (EMP_ID), and the IDs of their managers (EMP_MGR_ID).

For example, the manager of “Kristen B” is identified by EMP_MGR_ID “2,” which refers to the employee with EMP_ID “2”: “Paul C.”

verticademos=> SELECT * FROM manager_employee;
 emp_id | emp_name  | emp_mgr_id
--------+-----------+------------
      2 | Paul C    |          1
      3 | Kristen B |          2
      4 | Curtis B  |          2
      5 | Dave T    |          2
      6 | Lenoy J   |          2
      7 | Mike L    |          2
      8 | Mohan KS  |          2
      9 | Scott S   |          2
     10 | Theresa M |          2
     11 | Jim K     |          2
     16 | Max N     |          3
     14 | Pranj S   |          3
     12 | Bryan H   |          3
     13 | Bryan W   |          3
     15 | Robert B  |          3
      1 | Colin M   |          1
(16 rows)

To view the employees that report to “Kristen B,” we could run the following recursive query:

verticademos=> WITH RECURSIVE mgrs (emp_id, emp_name, emp_mgr_id) AS (
verticademos(>   SELECT emp_id, emp_name, emp_mgr_id
verticademos(>     FROM manager_employee
verticademos(>    WHERE emp_name = 'Kristen B'
verticademos(>    UNION
verticademos(>   SELECT e.emp_id, e.emp_name, e.emp_mgr_id
verticademos(>     FROM manager_employee e
verticademos(>     JOIN mgrs m ON m.emp_id = e.emp_mgr_id)
verticademos-> SELECT * FROM mgrs;

 emp_id | emp_name  | emp_mgr_id
--------+-----------+------------
      3 | Kristen B |          2
     15 | Robert B  |          3
     14 | Pranj S   |          3
     13 | Bryan W   |          3
     16 | Max N     |          3
     12 | Bryan H   |          3
(6 rows)

To view the managers of “Jim K,” we can traverse the table in the opposite direction:

verticademos=> WITH RECURSIVE emp (emp_id, emp_name, emp_mgr_id) AS (
verticademos(>   SELECT emp_id, emp_name, emp_mgr_id
verticademos(>     FROM manager_employee
verticademos(>    WHERE emp_name = 'Jim K'
verticademos(>    UNION
verticademos(>   SELECT e.emp_id, e.emp_name, e.emp_mgr_id
verticademos(>     FROM manager_employee e
verticademos(>     JOIN emp ON emp.emp_mgr_id = e.emp_id)
verticademos-> SELECT * FROM emp;

 emp_id | emp_name | emp_mgr_id
--------+----------+------------
     11 | Jim K    |          2
      2 | Paul C   |          1
      1 | Colin M  |          1
(3 rows)

Have Fun!