how to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entries show how to implement all the CONNECT BY features using the new recursive WITH clause.

---

Basic recursive query

CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):

SELECT e.ename, e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
/

Same result using the recursive WITH clause:

WITH r_emps (ename, empno, mgr)          AS (SELECT e.ename, e.empno, e.mgr
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT e.ename, e.empno, e.mgr
  FROM r_emps e
/

The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the
anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.

---

Formatting the result set

CONNECT BY queries often use the LEVEL pseudo column to format the output.

SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
     , e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
/

The same can be achieved using the following recursive WITH query:

WITH r_emps (ename, empno, mgr, lvl)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
            SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
      ,e.empno, e.mgr
  FROM r_emps e
/

To simulate the LEVEL pseudo column using the recursive WITH clause, one has to add a virtual level column on the root elements, which will be incremented along the hierarchy.

---

Sorting the result set

The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.

SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
     , e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
 ORDER SIBLINGS BY e.ename
/

Same result can be achieved using the recursive WITH clause with the SEARCH clause.

WITH r_emps (ename, empno, mgr, lvl)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
     , e.empno ,e.mgr
  FROM r_emps e
 ORDER BY e.sorting
/

SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children, the ename column is used as the order attribute within the hierarchy and assigned to the attribute sorting which can afterwards be used in the outermost query.