CONNECT_BY_ROOT is a unary operator that qualifies a column to return a value in this column. The value in the row that is regarded as the root node in relation to the current row.

A unary operator operates on a single operand. In the case of CONNECT_BY_ROOT, the single operand is the column name following the CONNECT_BY_ROOT keyword.

The following example shows the CONNECT_BY_ROOT operator in the context of the SELECT list:

SELECT [... ,] CONNECT_BY_ROOT column [, ...]
  FROM table_expression ...

When you use the CONNECT_BY_ROOT operator, follow these rules:

  • The CONNECT_BY_ROOT operator can be used in the SELECT list, the WHERE clause, the GROUP BY clause, the HAVING clause, the ORDER BY clause, and the ORDER SIBLINGS BY clause if the SELECT statement is used for a hierarchical query.
  • The CONNECT_BY_ROOT operator cannot be used in the CONNECT BY clause or the START WITH clause of a hierarchical query.
  • The CONNECT_BY_ROOT operator can be used in an expression involving a column. The expression must be enclosed within parentheses.

The following query shows how to use the CONNECT_BY_ROOT operator to return the result set based on trees starting with employees BLAKE, CLARK, and JONES. The result set includes the employee number and employee name of the root node for each employee listed.

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 the columns including mgr empno and mgr ename are one of the employees, including BLAKE, CLARK, and 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 example shows a similar query. In this query, only one tree starting with the single top-level employee is generated. The mgr column must be 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 the mgr empno and mgr ename columns 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 and generates 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 example shows the output of this query. Each node appears at least once as a root node for the mgr empno and mgr ename columns. 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 example illustrates the unary operator effect of CONNECT_BY_ROOT. When used in an expression that is not enclosed in parentheses, the CONNECT_BY_ROOT operator affects only the ename term that immediately follows the operator. The subsequent concatenation of || ' manages ' || ename is not part of the CONNECT_BY_ROOT operation. Therefore, the second occurrence of ename results in the value of the current row. 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 example shows the output of this query. The values are generated for 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)

In the following example, the CONNECT_BY_ROOT operator is used in an expression that is 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 example shows the output of this query. The values of both ename and empno are affected by the CONNECT_BY_ROOT operator. The top mgr/empno column shows the values returned from the root node.

 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)