
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!