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