Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 | 

Hierarchical Query for Table Joins

Join Conditions

The table joins are evaluated first using the join conditions, if any. The conditions found in the WHERE clause are classified as join conditions or filtering conditions. All the conditions in the FROM clause are classified as join conditions. Only the join conditions are evaluated; the filtering conditions are kept for later evaluation. We recommended placing all join conditions in the FROM clause only so that conditions that are intended for joins are not mistakenly classified as filtering conditions.

Query Results

The resulting rows of the table joins are filtered according to the START WITH condition to obtain the root nodes for the hierarchy. If no START WITH condition is specified, then all the rows resulting from the table joins will be considered as root nodes.
After the root nodes are obtained, CUBRID will select the child rows for the root nodes. These are all nodes from the table joins that respect the CONNECT BY condition. This step will be repeated for the child nodes to determine their child nodes and so on until no more child nodes can be added.
In addition, CUBRID evaluates the CONNECT BY clause first and all the rows of the resulting hierarchy tress by using the filtering condition in the WHERE clause.

Example

The example illustrates how joins can be used in CONNECT BY queries. The joins are evaluated before the CONNECT BY condition and the join result will be the starting table on which the two clauses (START WITH clause and CONNECT BY clause).

-- Creating tree2 table and then inserting data

CREATE TABLE tree2(id int, treeid int, job varchar(32));

 

INSERT INTO tree2 VALUES(1,1,'Partner');

INSERT INTO tree2 VALUES(2,2,'Partner');

INSERT INTO tree2 VALUES(3,3,'Developer');

INSERT INTO tree2 VALUES(4,4,'Developer');

INSERT INTO tree2 VALUES(5,5,'Sales Exec.');

INSERT INTO tree2 VALUES(6,6,'Sales Exec.');

INSERT INTO tree2 VALUES(7,7,'Assistant');

INSERT INTO tree2 VALUES(8,null,'Secretary');

 

-- Executing a hierarchical query onto table joins

SELECT t.id,t.name,t2.job,level

    FROM tree t

        inner join tree2 t2 on t.id=t2.treeid

    START WITH t.mgrid is null

    CONNECT BY prior t.id=t.mgrid

    ORDER BY t.id;


id    name         job          level
================================================
1     Kim         Partner         1

2     Moy         Partner         1

3     Jonas       Developer       2

4     Smith       Developer       2

5     Verma       Sales Exec.     2

6     Foster      Sales Exec.     2

7     Brown       Assistant       3