The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for testing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
SQL> SELECT level, empno, ename, mgr
2 FROM emp
3 CONNECT BY PRIOR empno = mgr
4 START WITH mgr IS NULL
5 /
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
...
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
SQL> SELECT LPAD(' ', LEVEL * 2) || ename
2 FROM emp
3 CONNECT BY PRIOR empno = mgr
4 START WITH mgr IS NULL;
LPAD(,LEVEL*2)||ENAME
------------------------------------------------------
KING
JONES
SCOTT
...
Use the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
NOTE: Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.
