START WITH ... CONNECT BY Clause

Description

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.

Syntax

SELECT column_list

    FROM table_joins | tables

    [WHERE join_conditions and/or filtering_conditions]

    [START WITH condition]

    CONNECT BY [NOCYCLE] condition

START WITH Clause

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.

CONNECT BY [NOCYCLE] or PRIOR Clause
Example

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