Hierarchical Queries

CONNECT BY and START WITH Clauses

The syntax for the CONNECT BY and START WITH clauses of a SELECT statement is


SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

LEVEL, a pseudo column, tells you how far way from the root. LEVEL returns 1 for a root node, 2 for a child of the root, and so on. start_condition specifies where to start the hierarchical query. prior_condition specifies the relationship between the parent and child rows.


CREATE TABLE employees
( employee_id          number(10)      not null,
  last_name            varchar2(50)      not null,
  job_id               varchar2(30),
  department_id        number(10),
  salary               number(6),
  manager_id           number(6)
);
insert into employees( employee_id, last_name, job_id, salary,department_id ,manager_id)
values(                1001,        'Lawson',  'MGR',  30000, 1,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1002,        'Wells',   'DBA',  20000, 2,             1005 );
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1003,        'Bliss',   'PROG', 24000, 3,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1004,        'Kyte',    'MGR',  25000 ,4,             1005);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1005,        'Viper',   'PROG', 20000, 1,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id,manager_id)
values(                1006,        'Beck',    'PROG', 20000, 2,             null);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1007,        'Java',    'PROG', 20000, 3,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1008,        'Oracle',  'DBA',  20000, 4,             1006);



SQL> SELECT employee_id, manager_id, last_name
  2  FROM employees
  3       START WITH employee_id = 1004
  4       CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID MANAGER_ID LAST_NAME
----------- ---------- -------------------------------------
       1004       1005 Kyte
       1001       1004 Lawson
       1003       1004 Bliss

SQL>

Using the LEVEL Pseudo Column


SQL> SELECT LEVEL, employee_id, manager_id, last_name
  2  FROM employees
  3  START WITH employee_id = 1004
  4  CONNECT BY PRIOR employee_id = manager_id
  5  ORDER BY LEVEL;

     LEVEL EMPLOYEE_ID MANAGER_ID   LAST_NAME
---------- ----------- ------------------------
         1        1004       1005        Kyte
         2        1003       1004       Bliss
         2        1001       1004      Lawson


SQL>

Using the COUNT() function and LEVEL to get the number of levels:


SQL> SELECT COUNT(DISTINCT LEVEL)
  2  FROM employees
  3  START WITH employee_id = 1004
  4  CONNECT BY PRIOR employee_id = manager_id;

COUNT(DISTINCTLEVEL)
--------------------
                   2

SQL>

Home »
Oracle »
Select »

Hierarchical Queries: