How does SQL handle hierarchical data?

SQL handles hierarchical data through several modeling techniques and query methods designed to represent parent-child relationships. The most common approaches include adjacency lists, nested sets, materialized paths, and recursive Common Table Expressions (CTEs). Each method has trade-offs in terms of query efficiency, ease of maintenance, and support across database systems. Hierarchical data structures, like organizational charts or category trees, require these specialized techniques because traditional relational operations (e.g., simple joins) struggle with variable-depth relationships.

One widely used method is the adjacency list, where each row stores a reference to its parent (e.g., a parent_id column). For example, an employees table might include employee_id and manager_id columns to represent reporting hierarchies. While simple to implement, querying multiple levels deep often requires recursive queries. PostgreSQL’s WITH RECURSIVE syntax allows traversing adjacency lists by iteratively joining a table to itself until all levels are processed. Another approach is the nested set model, which assigns numerical ranges (left and right values) to each node to represent its position in a tree. This allows efficient subtree queries (e.g., WHERE node.left > parent.left AND node.right < parent.right) but complicates updates. Materialized paths store the full path to a node as a string (e.g., /1/3/7/), enabling pattern-matching queries with LIKE or specialized string functions, though maintaining path integrity can be error-prone.

The choice of method depends on use-case requirements. Adjacency lists are straightforward for shallow hierarchies but inefficient for deep traversals. Nested sets optimize read-heavy scenarios but are cumbersome for frequent updates. Materialized paths work well with fixed-depth queries but struggle with reorganizations. Modern databases like SQL Server, Oracle, and PostgreSQL support recursive CTEs, which simplify querying adjacency lists by allowing iterative traversal. For example, WITH RECURSIVE cte AS (SELECT * FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.* FROM employees e JOIN cte ON e.manager_id = cte.employee_id) builds a hierarchy in a single query. Developers should prioritize query patterns (e.g., frequent subtree lookups vs. updates) and database capabilities when choosing a method.