Example of Using Hierarchy Query

The example in this page shows how to write hierarchical queries by specifying the CONNECT BY clause within the SELECT statement.

A table that have relationship with recursive reference is create and the table consists of two columns named ID and ParentID; assume that ID is a primary key for the table and ParentID is a foreign key for the same table. In this context, the root node will have a ParentID value of NULL.

Once a table is create, you can get the entire data with hierarchical structure and a value of LEVEL by using the UNION ALL as shown below.

SELECT L1.ID, L1.ParentID, ..., 1 AS [Level]

    FROM tree_table AS L1

    WHERE L1.ParentID IS NULL

UNION ALL

SELECT L2.ID, L2.ParentID, ..., 2 AS [Level]

    FROM tree_table AS L1

        INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID

    WHERE L1.ParentID IS NULL

UNION ALL

SELECT L3.ID, L3.ParentID, ..., 3 AS [Level]

    FROM tree_table AS L1

        INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID

        INNER JOIN tree_table AS L3 ON L2.ID=L3.ParentID

    WHERE L1.ParentID IS NULL

UNION ALL ...

Because you do not know how many levels exist in the data, you can rewrite the query above as a stored procedure that loops until no new row is retrieved.

However, the hierarchical structure should be checked every step while looping, specify the CONNECT BY clause within the SELECT statement as follows; the example below shows how to get the entire data with hierarchical structure and the level of each row in the hierarchy.

SELECT ID, ParentID, ..., Level

    FROM tree_table

    START WITH ParentID IS NULL

    CONNECT BY ParentID=PRIOR ID

You can specify NOCYCLE to prevent an error from occurring as follows:

SELECT ID, ParentID, ..., Level

    FROM tree_table

    START WITH ParentID IS NULL

    CONNECT BY NOCYCLE ParentID=PRIOR ID