Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 | 

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 Operator
  • PRIOR
    The CONNECT BY condition is tested for a pair of rows. If it evaluates to true, the two rows satisfy the parent-child relationship of the hierarchy. We need to specify the columns that are used from the parent row and the columns that are used from the child row. We can use the PRIOR operator when applied to a column, which will refer to the value of the parent row for that column. If PRIOR is not used for a column, the value in the child row is used.
  • NOCYCLE
    In some cases, the resulting rows of the table joins may contain cycles, depending on the CONNECT BY condition. Because cycles cause an infinite loop in the result tree construction, CUBRID detects them and either returns an error doesn't expand the branches beyond the point where a cycle is found (if the NOCYCLE keyword is specified).
    This keyword may be specified after the CONNECT BY keywords. It makes CUBRID run a statement even if the processed data contains cycles.
    If a SELECT ... CONNECT BY statement causes a cycle at runtime and the NOCYCLE keyword is not specified, CUBRID will return an error and the statement will be canceled. When specifying the NOCYCLE keyword, if CUBRID detects a cycle while processing a hierarchy node, it will set the CONNECT_BY_ISCYCLE attribute for that node to the value of 1 and it will stop further expansion of that branch.
Example

For the following samples, you will need the following structures:

Table tree

ID

MgrID

Name

1

NULL

KIM

2

NULL

Moy

3

1

Jonas

4

1

Simth

5

2

Verma

6

2

Foster

7

6

Brown

Target tree_cycle

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));

 

INSERT INTO tree VALUES (1,NULL,'Kim');

INSERT INTO tree VALUES (2,NULL,'Moy');

INSERT INTO tree VALUES (3,1,'Jonas');

INSERT INTO tree VALUES (4,1,'Smith');

INSERT INTO tree VALUES (5,2,'Verma');

INSERT INTO tree VALUES (6,2,'Foster');

INSERT INTO tree VALUES (7,6,'Brown');

 

-- 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 *

    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 *

    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