CONNECT BY

CONNECT BY, PRIOR and START WITH

There are two mandatory keywords to build a hierarchy, CONNECT BYand PRIOR. A hierarchy is built when one row is the parent of another row. START WITHdefines the first ancestor.

SELECT
   ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH

Jones and his employees are returned. Adams is an employee of Scott and Scott is an employee of Jones so Adams is also returned. 

LEVEL

The pseudo-column level returns the depth of the hierarchy. The first level is the root:

SELECT
   ENAME
FROM
   EMP
WHERE
   LEVEL=2
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

ENAME
--------
SCOTT
FORD

Only the direct employees of Jones are returned. Jones is the first ancestor and has a level of 1. Adams and Smith are one level below the direct employees and belong to the third level started by Jones.

The tree is displayed with the children indented under their parents by using padding with a number of spaces proportional to LEVEL.

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL;

ENAME
------------------
KING
   JONES
      SCOTT
         ADAMS
      FORD
         SMITH
   BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
   CLARK
      MILLER

Starting with the top manager, the names of the employees are padded with white spaces according to their level.

ORDER SIBLINGS BY

The rows in a hierarchical query are returned as a tree, the children following the parent. ORDER SIBLINGS BYpreserves the hierarchy and orders the children of each parent.

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL
ORDER SIBLINGS BY
   EMP.ENAME;

ENAME
----------------
KING
   BLAKE
      ALLEN
      JAMES
      MARTIN
      TURNER
      WARD
   CLARK
      MILLER
   JONES
      FORD
         SMITH
      SCOTT
         ADAMS

Clark comes after Blake and before Jones; they are under King and ordered by their name. Their children are sorted and the hierarchical appearance is preserved.

ORDER BYwithout SIBLINGSdestroys the hierarchy:

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL
ORDER BY
   EMP.ENAME;

ENAME
--------------
         ADAMS
      ALLEN
   BLAKE
   CLARK
      FORD
      JAMES
   JONES
KING
      MARTIN
      MILLER
      SCOTT
         SMITH
      TURNER
      WARD