Source

It builds a hierarchical query.

There are 2 components to is:

“start with” — this identifies all LEVEL=1 nodes in the tree

“connect by” — describes how to walk from the parent nodes above to their children and their childrens children.

Easiest to use an example on emp. If we start with “where mgr is NULL”, we generate the set of employees that have no mgr (they are the top of the tree). If we

CONNECT BY PRIOR EMPNO = /* current */ MGR

that will take all of the PRIOR records (the start with at first) and find all records such that the MGR column equals their EMPNO (find all the records of people managed by the people we started with).

Using EMP, the start with SET is:

scott@ORA8I.WORLD> select ename, empno, mgr from emp
2 where mgr is null;

ENAME EMPNO MGR
———- ———- ———-
KING 7839

Now, if we do the “connect by manually” we would find:

scott@ORA8I.WORLD> select ename, empno, mgr
2 from emp where mgr = 7839;

ENAME EMPNO MGR
———- ———- ———-
JONES 7566 7839
BLAKE 7698 7839
CLARK 7782 7839

scott@ORA8I.WORLD>

KINGS empno is the prior empno. If we build the entire hierarch — we have:

scott@ORA8I.WORLD> select lpad(‘ ‘,level*2,’ ‘)||ename ename, empno, mgr
2 from emp
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR
5 /

ENAME EMPNO MGR
————— ———- ———-
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782

14 rows selected.

So, KING is the start with set then JONES BLAKE and CLARK fall under him. Each of them becomes the PRIOR record in turn and their trees are expanded.