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.

You can execute the queries by changing the order of two clauses like CONNECT BY … START WITH.

Syntax

SELECT column_list

    FROM table_joins | tables

    [WHERE join_conditions and/or filtering_conditions]

    [hierarchical_clause]

hierarchical_clause :

    [START WITH condition] CONNECT BY [NOCYCLE] condition

    | CONNECT BY [NOCYCLE] condition [START WITH 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

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

The following example shows how to execute hierarchy query.

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

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