Handling Recursive Queries and Edge Cases in Hierarchical Data with SQL

Hierarchical data is commonly used to represent relationships like organizational charts, file directories, or category trees. SQL provides a powerful feature called Common Table Expressions (CTEs), specifically recursive CTEs, to query such hierarchical structures efficiently. In this tutorial, you will learn how to write recursive SQL queries to fetch hierarchical data, along with practical tips to handle edge cases such as cyclic references and missing parents.

Let's start by considering a simple table called `employees`, which contains an `id`, a `name`, and a `manager_id`. The `manager_id` references another employee in the same table, creating a hierarchy where each employee reports to their manager.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT NULL
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),  -- CEO
(2, 'Bob', 1),       -- Bob reports to Alice
(3, 'Charlie', 2),   -- Charlie reports to Bob
(4, 'Diana', 2),     -- Diana reports to Bob
(5, 'Eve', 3);       -- Eve reports to Charlie

To get the full hierarchy starting from the CEO (Alice), you can write a recursive CTE that begins with the root employee and recursively adds all direct reports.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: start with the CEO (manager_id IS NULL)
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member: get employees who report to the current level
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

This query produces the entire employee hierarchy with a `level` column indicating how deep each employee is in the tree. However, in real-world data, you might face some edge cases that can cause problems with recursive queries.

One common edge case is a **cycle** in the data. For example, if an employee mistakenly has themselves or one of their descendants as their manager, the recursive query could loop indefinitely. To prevent this, tracking visited nodes and stopping recursion when a cycle is detected is important.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id, 0 AS level, ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, eh.level + 1, path || e.id
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    WHERE NOT e.id = ANY(path)  -- Prevent cycles by checking if id already in path
)
SELECT * FROM EmployeeHierarchy;

In this example, we add a `path` array that keeps track of all visited employee IDs in the current recursion branch. The `WHERE NOT e.id = ANY(path)` condition stops the recursion if a cycle is detected.

Another edge case is having employees with **missing parents** where `manager_id` references a non-existent employee. This can cause your recursive query to miss those employees. To find these, you can run a simple query checking for orphans.

SELECT *
FROM employees
WHERE manager_id IS NOT NULL
  AND manager_id NOT IN (SELECT id FROM employees);

If your data contains such orphans, you might want to either clean the data or adjust your queries to include them. For example, starting the recursion from all employees with missing or null managers ensures you don't miss any part of the hierarchy.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id, 0 AS level, ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL OR manager_id NOT IN (SELECT id FROM employees)

    UNION ALL

    SELECT e.id, e.name, e.manager_id, eh.level + 1, path || e.id
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    WHERE NOT e.id = ANY(path)
)
SELECT * FROM EmployeeHierarchy;

In summary, recursive CTEs in SQL are powerful for handling hierarchical data. Always consider edge cases like cycles and missing parents to make your queries robust and reliable.