Retrieving the Root Node with CONNECT_BY_ROOT v12
CONNECT_BY_ROOT
is a unary operator that can be used to qualify a column in order to return the column’s value of the row considered to be the root node in relation to the current row.
Note
A unary operator operates on a single operand, which in the case of CONNECT_BY_ROOT
, is the column name following the CONNECT_BY_ROOT
keyword.
In the context of the SELECT
list, the CONNECT_BY_ROOT
operator is shown by the following.
SELECT [... ,] CONNECT_BY_ROOT <column> [, ...] FROM <table_expression> ...
The following are some points to note about the CONNECT_BY_ROOT
operator.
- The
CONNECT_BY_ROOT
operator can be used in theSELECT
list, theWHERE
clause, theGROUP BY
clause, theHAVING
clause, theORDER BY
clause, and theORDER SIBLINGS BY
clause as long as theSELECT
command is for a hierarchical query. - The
CONNECT_BY_ROOT
operator cannot be used in theCONNECT BY
clause or theSTART WITH
clause of the hierarchical query. - It is possible to apply
CONNECT_BY_ROOT
to an expression involving a column, but to do so, the expression must be enclosed within parentheses.
The following query shows the use of the CONNECT_BY_ROOT
operator to return the employee number and employee name of the root node for each employee listed in the result set based on trees starting with employees BLAKE, CLARK
, and JONES
.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr, CONNECT_BY_ROOT empno "mgr empno", CONNECT_BY_ROOT ename "mgr ename" FROM emp START WITH ename IN ('BLAKE','CLARK','JONES') CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY ename ASC;
Note that the output from the query shows that all of the root nodes in columns mgr empno
and mgr ename
are one of the employees, BLAKE, CLARK
, or JONES
, listed in the START WITH
clause.
level | employee | empno | mgr | mgr empno | mgr ename ------+-----------+-------+------+-----------+----------- 1 | BLAKE | 7698 | 7839 | 7698 | BLAKE 2 | ALLEN | 7499 | 7698 | 7698 | BLAKE 2 | JAMES | 7900 | 7698 | 7698 | BLAKE 2 | MARTIN | 7654 | 7698 | 7698 | BLAKE 2 | TURNER | 7844 | 7698 | 7698 | BLAKE 2 | WARD | 7521 | 7698 | 7698 | BLAKE 1 | CLARK | 7782 | 7839 | 7782 | CLARK 2 | MILLER | 7934 | 7782 | 7782 | CLARK 1 | JONES | 7566 | 7839 | 7566 | JONES 2 | FORD | 7902 | 7566 | 7566 | JONES 3 | SMITH | 7369 | 7902 | 7566 | JONES 2 | SCOTT | 7788 | 7566 | 7566 | JONES 3 | ADAMS | 7876 | 7788 | 7566 | JONES (13 rows)
The following is a similar query, but producing only one tree starting with the single, top-level, employee where the mgr
column is null.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr, CONNECT_BY_ROOT empno "mgr empno", CONNECT_BY_ROOT ename "mgr ename" FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY ename ASC;
In the following output, all of the root nodes in columns mgr empno
and mgr ename
indicate KING
as the root for this particular query.
level | employee | empno | mgr | mgr empno | mgr ename ------+-------------+-------+------+-----------+----------- 1 | KING | 7839 | | 7839 | KING 2 | BLAKE | 7698 | 7839 | 7839 | KING 3 | ALLEN | 7499 | 7698 | 7839 | KING 3 | JAMES | 7900 | 7698 | 7839 | KING 3 | MARTIN | 7654 | 7698 | 7839 | KING 3 | TURNER | 7844 | 7698 | 7839 | KING 3 | WARD | 7521 | 7698 | 7839 | KING 2 | CLARK | 7782 | 7839 | 7839 | KING 3 | MILLER | 7934 | 7782 | 7839 | KING 2 | JONES | 7566 | 7839 | 7839 | KING 3 | FORD | 7902 | 7566 | 7839 | KING 4 | SMITH | 7369 | 7902 | 7839 | KING 3 | SCOTT | 7788 | 7566 | 7839 | KING 4 | ADAMS | 7876 | 7788 | 7839 | KING (14 rows)
By contrast, the following example omits the START WITH
clause thereby resulting in fourteen trees.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr, CONNECT_BY_ROOT empno "mgr empno", CONNECT_BY_ROOT ename "mgr ename" FROM emp CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY ename ASC;
The following is the output from the query. Each node appears at least once as a root node under the mgr empno
and mgr ename
columns since even the leaf nodes form the top of their own trees.
level | employee | empno | mgr | mgr empno | mgr ename ------+-------------+-------+------+-----------+----------- 1 | ADAMS | 7876 | 7788 | 7876 | ADAMS 1 | ALLEN | 7499 | 7698 | 7499 | ALLEN 1 | BLAKE | 7698 | 7839 | 7698 | BLAKE 2 | ALLEN | 7499 | 7698 | 7698 | BLAKE 2 | JAMES | 7900 | 7698 | 7698 | BLAKE 2 | MARTIN | 7654 | 7698 | 7698 | BLAKE 2 | TURNER | 7844 | 7698 | 7698 | BLAKE 2 | WARD | 7521 | 7698 | 7698 | BLAKE 1 | CLARK | 7782 | 7839 | 7782 | CLARK 2 | MILLER | 7934 | 7782 | 7782 | CLARK 1 | FORD | 7902 | 7566 | 7902 | FORD 2 | SMITH | 7369 | 7902 | 7902 | FORD 1 | JAMES | 7900 | 7698 | 7900 | JAMES 1 | JONES | 7566 | 7839 | 7566 | JONES 2 | FORD | 7902 | 7566 | 7566 | JONES 3 | SMITH | 7369 | 7902 | 7566 | JONES 2 | SCOTT | 7788 | 7566 | 7566 | JONES 3 | ADAMS | 7876 | 7788 | 7566 | JONES 1 | KING | 7839 | | 7839 | KING 2 | BLAKE | 7698 | 7839 | 7839 | KING 3 | ALLEN | 7499 | 7698 | 7839 | KING 3 | JAMES | 7900 | 7698 | 7839 | KING 3 | MARTIN | 7654 | 7698 | 7839 | KING 3 | TURNER | 7844 | 7698 | 7839 | KING 3 | WARD | 7521 | 7698 | 7839 | KING 2 | CLARK | 7782 | 7839 | 7839 | KING 3 | MILLER | 7934 | 7782 | 7839 | KING 2 | JONES | 7566 | 7839 | 7839 | KING 3 | FORD | 7902 | 7566 | 7839 | KING 4 | SMITH | 7369 | 7902 | 7839 | KING 3 | SCOTT | 7788 | 7566 | 7839 | KING 4 | ADAMS | 7876 | 7788 | 7839 | KING 1 | MARTIN | 7654 | 7698 | 7654 | MARTIN 1 | MILLER | 7934 | 7782 | 7934 | MILLER 1 | SCOTT | 7788 | 7566 | 7788 | SCOTT 2 | ADAMS | 7876 | 7788 | 7788 | SCOTT 1 | SMITH | 7369 | 7902 | 7369 | SMITH 1 | TURNER | 7844 | 7698 | 7844 | TURNER 1 | WARD | 7521 | 7698 | 7521 | WARD (39 rows)
The following illustrates the unary operator effect of CONNECT_BY_ROOT
. As shown in this example, when applied to an expression that is not enclosed in parentheses, the CONNECT_BY_ROOT
operator affects only the term, ename
, immediately following it. The subsequent concatenation of || ' manages ' || ename
is not part of the CONNECT_BY_ROOT
operation, hence the second occurrence of ename
results in the value of the currently processed row while the first occurrence of ename
results in the value from the root node.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr, CONNECT_BY_ROOT ename || ' manages ' || ename "top mgr/employee" FROM emp START WITH ename IN ('BLAKE','CLARK','JONES') CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY ename ASC;
The following is the output from the query. Note the values produced under the top mgr/employee
column.
level | employee | empno | mgr | top mgr/employee ------+-----------+-------+------+---------------------- 1 | BLAKE | 7698 | 7839 | BLAKE manages BLAKE 2 | ALLEN | 7499 | 7698 | BLAKE manages ALLEN 2 | JAMES | 7900 | 7698 | BLAKE manages JAMES 2 | MARTIN | 7654 | 7698 | BLAKE manages MARTIN 2 | TURNER | 7844 | 7698 | BLAKE manages TURNER 2 | WARD | 7521 | 7698 | BLAKE manages WARD 1 | CLARK | 7782 | 7839 | CLARK manages CLARK 2 | MILLER | 7934 | 7782 | CLARK manages MILLER 1 | JONES | 7566 | 7839 | JONES manages JONES 2 | FORD | 7902 | 7566 | JONES manages FORD 3 | SMITH | 7369 | 7902 | JONES manages SMITH 2 | SCOTT | 7788 | 7566 | JONES manages SCOTT 3 | ADAMS | 7876 | 7788 | JONES manages ADAMS (13 rows)
The following example uses the CONNECT_BY_ROOT
operator on an expression enclosed in parentheses.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr, CONNECT_BY_ROOT ('Manager ' || ename || ' is emp # ' || empno) "top mgr/empno" FROM emp START WITH ename IN ('BLAKE','CLARK','JONES') CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY ename ASC;
The following is the output of the query. Note that the values of both ename
and empno
are affected by the CONNECT_BY_ROOT
operator and as a result, return the values from the root node as shown under the top mgr/empno
column.
level | employee | empno | mgr | top mgr/empno ------+-----------+-------+------+----------------------------- 1 | BLAKE | 7698 | 7839 | Manager BLAKE is emp # 7698 2 | ALLEN | 7499 | 7698 | Manager BLAKE is emp # 7698 2 | JAMES | 7900 | 7698 | Manager BLAKE is emp # 7698 2 | MARTIN | 7654 | 7698 | Manager BLAKE is emp # 7698 2 | TURNER | 7844 | 7698 | Manager BLAKE is emp # 7698 2 | WARD | 7521 | 7698 | Manager BLAKE is emp # 7698 1 | CLARK | 7782 | 7839 | Manager CLARK is emp # 7782 2 | MILLER | 7934 | 7782 | Manager CLARK is emp # 7782 1 | JONES | 7566 | 7839 | Manager JONES is emp # 7566 2 | FORD | 7902 | 7566 | Manager JONES is emp # 7566 3 | SMITH | 7369 | 7902 | Manager JONES is emp # 7566 2 | SCOTT | 7788 | 7566 | Manager JONES is emp # 7566 3 | ADAMS | 7876 | 7788 | Manager JONES is emp # 7566 (13 rows)