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