Oracle SQL - CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH

  1. HOME
  2. Oracle SQL
  3. Select
  4. Hierarchical Queries

Introduction

If you use START WITH and CONNECT BY to define a hierarchical query, you can use two hierarchical operators in the SELECT clause:

Operators Description
CONNECT_BY_ROOTconnect each row with its own root.
SYS_CONNECT_BY_PATH display the full path from the current row to its root.

The following code uses both operators. START WITH clause creates three separate tree structures: one for each manager. 

select  ename
,       connect_by_root ename            as manager
,       sys_connect_by_path(ename,' > ') as full_path
from    emp
start   with job = 'MANAGER'
connect by prior empno = mgr;

You can specify additional conditions in the CONNECT BY clause, thus eliminating entire subtree structures.

Those conditions filter only individual rows.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  ww  w . j  a  v  a 2 s. c  o m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> select  ename
  2  ,       connect_by_root ename            as manager
  3  ,       sys_connect_by_path(ename,' > ') as full_path
  4  from    emp
  5  start   with job = 'MANAGER'
  6  connect by prior empno = mgr;

ENAME    | MANAGER
-------- | --------
FULL_PATH
--------------------------------------------------------------------------------
JACK     | JACK
 > JACK

SCOTT    | JACK
 > JACK > SCOTT

ADAMS    | JACK
 > JACK > SCOTT > ADAMS


ENAME    | MANAGER
-------- | --------
FULL_PATH
--------------------------------------------------------------------------------
FORD     | JACK
 > JACK > FORD

SMITH    | JACK
 > JACK > FORD > SMITH

BLAKE    | BLAKE
 > BLAKE


ENAME    | MANAGER
-------- | --------
FULL_PATH
--------------------------------------------------------------------------------
ALLEN    | BLAKE
 > BLAKE > ALLEN

WARD     | BLAKE
 > BLAKE > WARD

BROWN    | BLAKE
 > BLAKE > BROWN


ENAME    | MANAGER
-------- | --------
FULL_PATH
--------------------------------------------------------------------------------
BREAD    | BLAKE
 > BLAKE > BREAD

JONES    | BLAKE
 > BLAKE > JONES

CLARK    | CLARK
 > CLARK


ENAME    | MANAGER
-------- | --------
FULL_PATH
--------------------------------------------------------------------------------
MARY     | CLARK
 > CLARK > MARY


13 rows selected.

SQL>

Related Topic