To query data as a hierarchical structure, we use the CONNECT BY clause in the query. CONNECT BY takes an expression, where one operator needs to be the PRIOR unary operator. PRIOR refers to the parent row, so that an expression like PRIOR parentId refers to the column parentId in the parent row, not in the current row. Essentially, CONNECT BY together with PRIOR defines a join condition between a parent row and its child rows. It is also possible to user more than one condition with CONNECT BY.
Also, we need the START WITH clause which essentially is a WHERE condition to select the starting rows (the root node or nodes).
A hierarchical query automatically adds some pseudo columns to the result set, of which we will introduce two here: The LEVEL pseudo column contains the indentation level of the row (the number of levels from the starting row), and the CONNECT_BY_ISLEAF column adds a flag which indicates whether a row has children or not (i.e. whether it is a leaf node). A complete sample query looks like this (the substr function is only used to make the result display nicely in SQL*Plus, and the LPAD() function generates a number of spaces depending on the current level in front of the Label, to nicely indent it):
SELECT LEVEL, CONNECT_BY_ISLEAF, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent;
PRIOR is a unary operator, means it applies to the expression after it (“Id”, in our case). We would get the same result if we switch the two sides of the equal sign (but we still need to apply the PRIOR operator to the “Id” column to identify the column which makes up the parent row in the query):
... CONNECT BY Parent = PRIOR Id;
In either case, the result looks like this:
LEVEL CONNECT_BY_ISLEAF ID NODE
---------- ----------------- ---------- ------------------------
1 0 1 Root Node
2 0 2 Node 1
3 1 3 Node 1.1
3 0 4 Node 1.2
4 0 5 Node 1.2.1
5 1 6 Node 1.2.1.1
2 0 7 Node 2
3 1 8 Node 2.1
3 1 9 Node 2.3
3 1 10 Node 2.4
2 1 11 Node 3
2 0 12 Node 4
3 1 13 Node 4.1
The first column contains the level of the node within the tree with the root node starting at 1. The second column contains a flag indicating whether the node has children or not (i.e. whether it is a leaf node).