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(>    WHERE emp_name = 'Kristen B'
verticademos(>     JOIN mgrs m ON m.emp_id = e.emp_mgr_id)

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(>    WHERE emp_name = 'Jim K'
verticademos(>     JOIN emp ON emp.emp_mgr_id = e.emp_id)

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

Have Fun!