Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 | 

Scenario of Using Hierarchical Query

First of all let's start by giving a rough SQL translation of the SELECT statement with a CONNECT BY clause. For this we can consider that we have a table that contains a recurrent reference. We can consider that table to have two columns named ID and ParentID; ID is the primary key for the table and ParentID is a foreign-key to the same table. Naturally, the root nodes will have a ParentID value of NULL.

Now let us consider the fact that we want to get the full rows and a column with the level of the row in the hierarchy tree. For this we can write something similar to by querying with UNION ALL.

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 ...

The problem with our approach is that we do not know how many levels we have. This could be rewritten in a stored procedure with a cycle until no new rows are retrieved, but we will have to check the tree for cycles at every step. Using a SELECT statement with a CONNECT BY clause we can rewrite this as follows.

This query will return the full hierarchy with 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

If we want to avoid the potential error caused by cycles we can write it as follows:

SELECT ID, ParentID, ..., Level

    FROM tree_table

    START WITH ParentID IS NULL

    CONNECT BY NOCYCLE ParentID=PRIOR ID