Retrieving the root node with CONNECT_BY_ROOT v14

Suggest edits

CONNECT_BY_ROOT is a unary operator that you can use to qualify a column 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. In the case of CONNECT_BY_ROOT, it's 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> ...

Some points to note about the CONNECT_BY_ROOT operator:

You can use the CONNECT_BY_ROOT operator in the SELECT list, WHERE clause, GROUP BY clause, HAVING clause, ORDER BY clause, and ORDER SIBLINGS BY clause as long as the SELECT command is for a hierarchical query.
You can't use the CONNECT_BY_ROOT operator in the CONNECT BY clause or the START WITH clause of the hierarchical query.
You can apply CONNECT_BY_ROOT to an expression involving a column. To do so, you must enclose the expression in 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;

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 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, this example omits the START WITH clause, thereby resulting in 14 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 shows the unary operator effect of CONNECT_BY_ROOT. When applied to an expression that isn't enclosed in parentheses, the CONNECT_BY_ROOT operator affects only the term ename immediately following it. The subsequent concatenation of || ' manages ' || ename isn't 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)

This 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. The values of both ename and empno are affected by the CONNECT_BY_ROOT operator. As a result, they 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)

← Prev

Ordering the siblings

Retrieving a path with SYS_CONNECT_BY_PATH

Retrieving the root node with CONNECT_BY_ROOT v14

Note

← Prev

Next →