Quick Tip: WITH Clause Recursion

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!