This clause is used to obtain a set of data organized in a hierarchy. The START WITH ... CONNECT BY clause is used in combination with the SELECT clause in the following form.
SELECT column_list
FROM table_joins | tables
[WHERE join_conditions and/or filtering_conditions]
[START WITH condition]
CONNECT BY [NOCYCLE] condition
The START WITH clause will filter the rows from which the hierarchy will start. The rows that satisfy the START WITH condition will be the root nodes of the hierarchy. If START WITH is omitted, then all the rows will be considered as root nodes.
Note If START WITH clause is omitted or the rows that satisfy the START WITH condition does not exist, all of rows in the table are considered as root nodes; which means that hierarchy relationship of sub rows which belong each root is searched. Therefore, some of results can be duplicate.
For the following samples, you will need the following structures:
tree Table
ID |
MgrID |
Name |
BirthYear |
---|---|---|---|
1 |
NULL |
Kim |
1963 |
2 |
NULL |
Moy |
1958 |
3 |
1 |
Jonas |
1976 |
4 |
1 |
Smith |
1974 |
5 |
2 |
Verma |
1973 |
6 |
2 |
Foster |
1972 |
7 |
6 |
Brown |
1981 |
tree_cycle table
ID |
MgrID |
Name |
---|---|---|
1 |
NULL |
Kim |
2 |
11 |
Moy |
3 |
1 |
Jonas |
4 |
1 |
Smith |
5 |
3 |
Verma |
6 |
3 |
Foster |
7 |
4 |
Brown |
8 |
4 |
Lin |
9 |
2 |
Edwin |
10 |
9 |
Audrey |
11 |
10 |
Stone |
-- Creating tree table and then inserting data
CREATE TABLE tree(ID INT, MgrID INT, Name VARCHAR(32), BirthYear INT);
INSERT INTO tree VALUES (1,NULL,'Kim', 1963);
INSERT INTO tree VALUES (2,NULL,'Moy', 1958);
INSERT INTO tree VALUES (3,1,'Jonas', 1976);
INSERT INTO tree VALUES (4,1,'Smith', 1974);
INSERT INTO tree VALUES (5,2,'Verma', 1973);
INSERT INTO tree VALUES (6,2,'Foster', 1972);
INSERT INTO tree VALUES (7,6,'Brown', 1981);
-- Creating tree_cycle table and then inserting data
CREATE TABLE tree_cycle(ID INT, MgrID INT, Name VARCHAR(32));
INSERT INTO tree_cycle VALUES (1,NULL,'Kim');
INSERT INTO tree_cycle VALUES (2,11,'Moy');
INSERT INTO tree_cycle VALUES (3,1,'Jonas');
INSERT INTO tree_cycle VALUES (4,1,'Smith');
INSERT INTO tree_cycle VALUES (5,3,'Verma');
INSERT INTO tree_cycle VALUES (6,3,'Foster');
INSERT INTO tree_cycle VALUES (7,4,'Brown');
INSERT INTO tree_cycle VALUES (8,4,'Lin');
INSERT INTO tree_cycle VALUES (9,2,'Edwin');
INSERT INTO tree_cycle VALUES (10,9,'Audrey');
INSERT INTO tree_cycle VALUES (11,10,'Stone');
-- Executing a hierarchy query with CONNECT BY clause
SELECT id, mgrid, name
FROM tree
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name
======================
1 null Kim
2 null Moy
3 1 Jonas
3 1 Jonas
4 1 Smith
4 1 Smith
5 2 Verma
5 2 Verma
6 2 Foster
6 2 Foster
7 6 Brown
7 6 Brown
7 6 Brown
-- Executing a hierarchy query with START WITH clause
SELECT id, mgrid, name
FROM tree
START WITH mgrid IS NULL
CONNECT BY prior id=mgrid
ORDER BY id;
id mgrid name
=============================
1 null Kim
2 null Moy
3 1 Jonas
4 1 Smith
5 2 Verma
6 2 Foster
7 6 Brown