Hierarchical Queries in SQL: Navigating Tree Structures with CONNECT BY and START WITH

Navigating Parent‑Child Relationships in Relational Databases

In enterprise systems, it is extremely common to encounter data that has a hierarchical parent‑child structure. Classic examples include corporate organograms (where an employee reports to a manager, who in turn reports to a director), product category trees, bills of materials (BOM) in manufacturing, or logistics route maps.

Querying these structures using only common JOINs can become a complex and rigid challenge, especially when the depth of the data tree is unknown or variable. To solve this problem with maximum elegance and performance, the Oracle database provides the native START WITH and CONNECT BY clauses.

The Concept of Relational Hierarchy

A table stores hierarchical data when it has a foreign key column that points to its own primary key (a reflexive or self‑referencing relationship). For example, in an employees table, we might have the columns:

• employee_id (primary key of the employee)
• full_name (employee name)
• manager_id (foreign key pointing to the employee_id of the direct manager)

The Fundamental Syntax of CONNECT BY

The structure of a hierarchical query in Oracle uses special directional operators to traverse the tree branches:

• START WITH: Defines the root condition (the absolute starting point of the tree, such as the company CEO or the top of a category).
• CONNECT BY: Defines the relationship rule that connects the parent record to the child record using the PRIOR operator.
• PRIOR: Specifies which side of the relationship belongs to the upper level (parent).

Practical corporate organogram example:

SELECT LEVEL AS nivel_hierarquico, LPAD(' ', 2 * (LEVEL - 1)) || full_name AS organograma_visual, job_title, employee_id, manager_id FROM employees START WITH manager_id IS NULL -- Starts at the top (e.g., the president who has no boss) CONNECT BY PRIOR employee_id = manager_id;

Auxiliary Functions for Hierarchical Analysis

Oracle provides built‑in pseudocolumns and functions that are extremely useful for enriching navigation through data trees:

• LEVEL: Returns an integer indicating the depth of the node in the tree. The root level receives 1, direct subordinates receive 2, and so on.
• SYS_CONNECT_BY_PATH(column, separator): Builds a string concatenating the entire path from the root to the current node (e.g., /Directorate/Finance/Management/Analyst).
• CONNECT_BY_ISCYCLE: Returns 1 if the current record creates an infinite loop of corrupted data in the hierarchy, or 0 otherwise.

Practical example using SYS_CONNECT_BY_PATH:

SELECT employee_id, full_name, SYS_CONNECT_BY_PATH(full_name, ' -> ') AS caminho_hierarquico FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id;

Avoiding Infinite Loops with NOCYCLE

In legacy or poorly maintained databases, data inconsistencies can cause an employee to point to themselves as a manager or create a closed cycle (where a subordinate is the boss of their own boss). When this occurs, a regular hierarchical query will fail with the well‑known ORA‑01436: CONNECT BY loop in user data error.

To protect your application against this scenario, always use the CONNECT BY NOCYCLE clause combined with safety checks:

SELECT employee_id, full_name FROM employees START WITH manager_id IS NULL CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

Conclusion

Mastering hierarchical queries with START WITH and CONNECT BY empowers the developer to extract complex information from tree structures in a clean, fast, and structured way directly in the database.