Oracle hierarchical queries allow you to traverse tree-like data structures using the CONNECT BY clause. These queries are particularly useful for organizational charts, bill of materials, menu structures, and any parent-child relationships.

The following table structure demonstrates a self-referencing hierarchy where each row can have a parent row:

CREATE TABLE table_connect_by
(
    id1 NUMBER(2,0)
,   parent_id1 NUMBER(2,0)
,   code VARCHAR(50)
,   CONSTRAINT pk_table_connect_by PRIMARY KEY (id1)
,   CONSTRAINT fk_table_connect_by FOREIGN KEY (parent_id1) REFERENCES table_connect_by (id1)
);

The sample data creates two hierarchical trees: one starting with node 11 (A_11) and another with node 21 (B_21). The hierarchy depth varies, with some branches extending to level 4:

INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (11, null, 'A_11');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (12, 11, 'A_11_12');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (13, 11, 'A_11_13');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (14, 12, 'A_11_12_14');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (15, 12, 'A_11_12_15');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (16, 14, 'A_11_12_14_16');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (17, 14, 'A_11_12_14_17');

INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (21, null, 'B_21');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (22, 21, 'B_21_22');
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (23, 21, 'B_21_23');

COMMIT;

Hierarchical Structure of table_connect_by:

11 (A_11) [ROOT]
├── 12 (A_11_12)
│   ├── 14 (A_11_12_14)
│   │   ├── 16 (A_11_12_14_16)
│   │   └── 17 (A_11_12_14_17)
│   └── 15 (A_11_12_15)
└── 13 (A_11_13)

21 (B_21) [ROOT]
├── 22 (B_21_22)
└── 23 (B_21_23)

Legend:
- Numbers represent the id1 values
- Text in parentheses shows the code values
- [ROOT] indicates top-level nodes (parent_id1 = NULL)

The CONNECT_BY_ROOT operator identifies the top-level ancestor for each row in the hierarchical query result. This query traverses all possible hierarchical relationships in the table, starting from every node and including all its descendants.

The CONNECT BY PRIOR clause defines the parent-child relationship direction. The syntax "PRIOR t1.id1 = t1.parent_id1" means "find children where the parent's id1 equals the child's parent_id1":

SELECT
        CONNECT_BY_ROOT -- specifies the column that will be the parent
        t1.id1 AS root_parent_id -- the main parent
,       t1.id1 AS child_id -- children of the main parent
,       NVL(t1.parent_id1, 0) AS parent_id1 -- direct parent of the selected child
,       t1.code AS code
,       LEVEL AS level_parent_child -- distance between the main parent and selected child
FROM    table_connect_by t1
CONNECT BY PRIOR t1.id1 = t1.parent_id1;

The LEVEL pseudocolumn indicates the depth in the hierarchy, starting from 1 for root nodes. To organize the results in a meaningful order, wrap the query in a subquery and apply ORDER BY:

SELECT  *
FROM
(
        SELECT
                CONNECT_BY_ROOT -- specifies the column that will be the parent
                t1.id1 AS root_parent_id -- the main parent
        ,       t1.id1 AS child_id -- children of the main parent
        ,       NVL(t1.parent_id1, 0) AS parent_id1 -- direct parent of the selected child
        ,       t1.code AS code
        ,       LEVEL AS level_parent_child -- distance between the main parent and selected child
        FROM    table_connect_by t1
        CONNECT BY PRIOR t1.id1 = t1.parent_id1
)
ORDER BY root_parent_id, parent_id1, child_id;

Query result:

11    11    0     A_11             1
11    12    11    A_11_12          2
11    13    11    A_11_13          2
11    14    12    A_11_12_14       3
11    15    12    A_11_12_15       3
11    16    14    A_11_12_14_16    4
11    17    14    A_11_12_14_17    4

12    12    11    A_11_12          1
12    14    12    A_11_12_14       2
12    15    12    A_11_12_15       2
12    16    14    A_11_12_14_16    3
12    17    14    A_11_12_14_17    3

13    13    11    A_11_13          1

14    14    12    A_11_12_14       1
14    16    14    A_11_12_14_16    2
14    17    14    A_11_12_14_17    2

15    15    12    A_11_12_15       1

16    16    14    A_11_12_14_16    1

17    17    14    A_11_12_14_17    1

21    21    0     B_21             1
21    22    21    B_21_22          2
21    23    21    B_21_23          2

22    22    21    B_21_22          1
23    23    21    B_21_23          2

The START WITH clause allows you to begin the hierarchical traversal from a specific node rather than all possible starting points. This is useful when you want to explore only a particular subtree within your hierarchy.

In this example, we start from node 12 and retrieve all its descendants. The query will only return the subtree rooted at the specified starting node:

SELECT
        CONNECT_BY_ROOT -- specifies the column that will be the parent
        t1.id1 AS root_parent_id -- the main parent
,       t1.id1 AS child_id -- children of the main parent
,       NVL(t1.parent_id1, 0) AS parent_id1 -- direct parent of the selected child
,       t1.code AS code
,       LEVEL AS level_parent_child -- distance between the main parent and selected child
FROM    table_connect_by t1
START WITH t1.id1 = 12 -- specify a main parent to start the selection of sub-children
CONNECT BY PRIOR t1.id1 = t1.parent_id1;

Query result:

12    12    11    A_11_12          1
12    14    12    A_11_12_14       2
12    15    12    A_11_12_15       2
12    16    14    A_11_12_14_16    3
12    17    14    A_11_12_14_17    3

The SYS_CONNECT_BY_PATH function constructs a string that shows the complete path from the root to each node in the hierarchy.

The function takes two parameters: the column to include in the path and a delimiter character. The path is built cumulatively as the query traverses down the hierarchy:

SELECT
        CONNECT_BY_ROOT -- specifies the column that will be the parent
        t1.id1 AS root_parent_id -- the main parent
,       t1.id1 AS child_id -- children of the main parent
,       NVL(t1.parent_id1, 0) AS parent_id1 -- direct parent of the selected child
,       t1.code AS code
,       LEVEL AS level_parent_child -- distance between the main parent and selected child
,       SYS_CONNECT_BY_PATH(t1.parent_id1, '/') AS path_parent
,       SYS_CONNECT_BY_PATH(t1.id1, '/') AS path_child
FROM    table_connect_by t1
START WITH t1.id1 = 12 -- specify a main parent to start the selection of sub-children
CONNECT BY PRIOR t1.id1 = t1.parent_id1;

Query result:

12    12    11    A_11_12          1    /11          /12
12    14    12    A_11_12_14       2    /11/12       /12/14
12    15    12    A_11_12_15       2    /11/12       /12/15
12    16    14    A_11_12_14_16    3    /11/12/14    /12/14/16
12    17    14    A_11_12_14_17    3    /11/12/14    /12/14/17

The WHERE clause in hierarchical queries allows you to filter the results after the hierarchy has been traversed. Unlike CONNECT BY conditions which affect the traversal itself, WHERE conditions are applied to the final result set.

This example demonstrates filtering by hierarchy level and excluding specific nodes. The LEVEL pseudocolumn can be used in WHERE clauses to limit results to certain depths in the hierarchy:

SELECT
        CONNECT_BY_ROOT -- specifies the column that will be the parent
        t1.id1 AS root_parent_id -- the main parent
,       t1.id1 AS child_id -- children of the main parent
,       NVL(t1.parent_id1, 0) AS parent_id1 -- direct parent of the selected child
,       t1.code AS code
,       LEVEL AS level_parent_child -- distance between the main parent and selected child
FROM    table_connect_by t1
WHERE   LEVEL <= 2 AND t1.id1 != 14 -- add conditions
START WITH t1.id1 = 12 -- specify a main parent to start the selection of sub-children
CONNECT BY PRIOR t1.id1 = t1.parent_id1;

Query result:

12    12    11    A_11_12          1
12    15    12    A_11_12_15       2