 |
|
Advanced Oracle SQL CONNECT BY Clause
Oracle Tips by Laurent Schneider
|
Laurent
Schneider is considered one of the top Oracle SQL experts, and
he is the author of the book "Advanced
Oracle SQL Programming" by Rampant TechPress. The following
is an excerpt from the book.
PRIOR
The PRIOR
keyword in the CONNECT BYclause
defines the relationship between the parent and the child. PRIOR
identifies the column of the child row where the value matches
another column of the parent row.
PRIOR is also useful to select some child and
some parent values simultaneously.
SELECT
EMPNO,
ENAME,
PRIOR EMPNO MGR_EMPNO,
PRIOR ENAME MGR_ENAME
FROM
EMP
WHERE
LEVEL=2
CONNECT BY
MGR=PRIOR EMPNO
ORDER BY
ENAME;
EMPNO ENAME
MGR_EMPNO MGR_ENAME
---------- ---------- ---------- ----------
7876 ADAMS 7788 SCOTT
7499 ALLEN 7698 BLAKE
7698 BLAKE 7839 KING
7782 CLARK 7839 KING
7902 FORD 7566 JONES
7900 JAMES 7698 BLAKE
7566 JONES 7839 KING
7654 MARTIN 7698 BLAKE
7934 MILLER 7782 CLARK
7788 SCOTT 7566 JONES
7369 SMITH 7902 FORD
7844 TURNER 7698 BLAKE
7521 WARD 7698 BLAKE
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | FILTER | |
|* 3 | CONNECT BY WITHOUT FILTERING| |
| 4 | TABLE ACCESS FULL | EMP |
-----------------------------------------------
Each employee is selected with his manager. The
execution planreveals that the
table is selected only once.
SYS_CONNECT_BY_PATH
The function SYS_CONNECT_BY_PATHreturns the hierarchy from the first ancestor to the
current row. Each name is separated by a specific character.
SELECT
SYS_CONNECT_BY_PATH(ENAME, '/')
FROM
EMP
START WITH
MGR IS NULL
CONNECT BY
PRIOR EMPNO=MGR;
SYS_CONNECT_BY_PATH(ENAME,'/')
--------------------------------
/KING
/KING/JONES
/KING/JONES/SCOTT
/KING/JONES/SCOTT/ADAMS
/KING/JONES/FORD
/KING/JONES/FORD/SMITH
/KING/BLAKE
/KING/BLAKE/ALLEN
/KING/BLAKE/WARD
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER
/KING/BLAKE/JAMES
/KING/CLARK
/KING/CLARK/MILLER
The ancestors are concatenated into a slash
separated string. The second argument of SYS_CONNECT_BY_PATH must be
a single character literal.
CONNECT_BY_ROOT
The function CONNECT_BY_ROOTreturns the first ancestor:
SELECT
CONNECT_BY_ROOT ENAME as ANCESTOR,
ENAME
FROM
EMP
WHERE
LEVEL>1
CONNECT BY
PRIOR EMPNO=MGR;
ANCESTOR ENAME
---------- ---------
SCOTT ADAMS
FORD SMITH
JONES SCOTT
JONES ADAMS
JONES FORD
JONES SMITH
BLAKE ALLEN
BLAKE WARD
BLAKE MARTIN
BLAKE TURNER
BLAKE JAMES
CLARK MILLER
KING JONES
KING SCOTT
KING ADAMS
KING FORD
KING SMITH
KING BLAKE
KING ALLEN
KING WARD
KING MARTIN
KING TURNER
KING JAMES
KING CLARK
KING MILLER
Each parent-child relation is defined. For each
employee, a list of all his subordinates is returned. Root rows are
discarded since they represent the ancestor himself. This
revolutionary new capability has been introduced in 10g.
In Oracle 9i, the CONNECT_BY_ROOTis simulated by using SUBSTR
and SYS_CONNECT_BY_PATH:
SELECT
SUBSTR
(
SYS_CONNECT_BY_PATH(ENAME,'/'),
2,
INSTR
(
SYS_CONNECT_BY_PATH(ENAME,'/'),
'/',
2
)-2
) ANCESTOR,
ENAME
FROM
EMP
WHERE
LEVEL>1
CONNECT BY
PRIOR EMPNO=MGR;
ANCESTOR ENAME
---------- ---------
SCOTT ADAMS
FORD SMITH
JONES SCOTT
JONES ADAMS
JONES FORD
JONES SMITH
BLAKE ALLEN
BLAKE WARD
BLAKE MARTIN
BLAKE TURNER
BLAKE JAMES
CLARK MILLER
KING JONES
KING SCOTT
KING ADAMS
KING FORD
KING SMITH
KING BLAKE
KING ALLEN
KING WARD
KING MARTIN
KING TURNER
KING JAMES
KING CLARK
KING MILLER
In each path, the first ancestor is selected by
using SUBSTR + INSTR.
CONNECT_BY_ROOT allows subtrees to be aggregated and analyzed.
SELECT
ANCESTOR,
COUNT(NULLIF(ENAME,ANCESTOR)) COUNT,
XMLAGG(DECODE(ENAME,ANCESTOR,NULL,XMLELEMENT(ENAME,ENAME)))
EMPLOYEES
FROM
(
SELECT
CONNECT_BY_ROOT ENAME as ANCESTOR,
ENAME
FROM
EMP
CONNECT BY
PRIOR EMPNO=MGR
)
GROUP BY
ANCESTOR
ORDER BY
ANCESTOR;
ANCESTOR COUNT EMPLOYEES
---------- -----
-------------------------------------------------------------
ADAMS 0
ALLEN 0
BLAKE 5 <ENAME>ALLEN</ENAME><ENAME>JAMES</ENAME><ENAME>MARTIN</ENAME>
<ENAME>TURNER</ENAME><ENAME>WARD</ENAME>
CLARK 1 <ENAME>MILLER</ENAME>
FORD 1 <ENAME>SMITH</ENAME>
JAMES 0
JONES 4 <ENAME>ADAMS</ENAME><ENAME>FORD</ENAME><ENAME>SCOTT</ENAME>
<ENAME>SMITH</ENAME>
KING 13 <ENAME>ADAMS</ENAME><ENAME>ALLEN</ENAME><ENAME>BLAKE</ENAME>
<ENAME>CLARK</ENAME><ENAME>FORD</ENAME><ENAME>JAMES</ENAME>
<ENAME>JONES</ENAME><ENAME>MARTIN</ENAME><ENAME>MILLER</ENAME>
<ENAME>SCOTT</ENAME><ENAME>SMITH</ENAME><ENAME>TURNER</ENAME>
<ENAME>WARD</ENAME>
MARTIN 0
MILLER 0
SCOTT 1 <ENAME>ADAMS</ENAME>
SMITH 0
TURNER 0
WARD 0
For each employee, the count of his employees
and an XML instance containing the name of the employees is
returned.
