Hierarchical Query¶
Hierarchical Query 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.
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 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.
The following example shows how to execute hierarchical 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 hierarchical 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 hierarchical 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'
Hierarchical Query Execution¶
Hierarchical Query for Table Join¶
When target table is joined in SELECT statement, WHERE clause can include not only searching conditions but also joining conditions. At this time, CUBRID applies the joining conditions in WHERE clause at first, then conditions in CONNECT BY clause; at last, the left searching conditions.
When specifying joining conditions and searching conditions together in WHERE clause, joining conditions can be applied as searching conditions even if there was no intention; so the operating order can be different; therefore, we recommend that you specify the table joining conditions in FROM clause, not in WHERE 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.
The example illustrates how joins can be used in CONNECT BY queries.
-- 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
Ordering Data with the Hierarchical Query¶
The ORDER SIBLINGS BY clause will cause the ordering of the rows while preserving the hierarchy ordering so that the child nodes with the same parent will be stored according to the column list.
ORDER SIBLINGS BY col_1 [ASC|DESC] [, col_2 [ASC|DESC] [...[, col_n [ASC|DESC]]...]]
The following example shows how to display information about seniors and subordinates in a company in the order of birth year.
The result with hierarchical query shows parent and child nodes in a row according to the column list specified in ORDER SIBLINGS BY statement by default. Sibling nodes that share the same parent node have outputted in a specified order.
-- Outputting a parent node and its child nodes, which sibling nodes that share the same parent are sorted in the order of birthyear.
SELECT id, mgrid, name, birthyear, level
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY birthyear;
id mgrid name birthyear level
==================================================================
2 NULL 'Moy' 1958 1
6 2 'Foster' 1972 2
7 6 'Brown' 1981 3
5 2 'Verma' 1973 2
1 NULL 'Kim' 1963 1
4 1 'Smith' 1974 2
3 1 'Jonas' 1976 2
The following example shows how to display information about seniors and subordinates in a company in the order of joining. For the same level, the employee ID numbers are assigned in the order of joining. id indicates employee ID numbers (parent and child nodes) and mgrid indicates the employee ID numbers of their seniors.
-- Outputting a parent node and its child nodes, which sibling nodes that share the same parent are sorted in the order of id.
SELECT id, mgrid, name, LEVEL
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id;
id mgrid name level
=====================================================
1 NULL 'Kim' 1
3 1 'Jonas' 2
4 1 'Smith' 2
2 NULL 'Moy' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 3
Pseudo Columns for Hierarchical Query¶
LEVEL¶
LEVEL is a pseudocolumn representing depth of hierarchical queries. The LEVEL of root node is 1 and the LEVEL of its child node is 2.
The LEVEL (pseudocolumn) can be used in the WHERE clause, ORDER BY clause, and GROUP BY … HAVING clause of the SELECT statement. And it can also be used in the statement using aggregate functions.
The following example shows how to retrieve the LEVEL value to check level of node.
-- Checking the LEVEL value
SELECT id, mgrid, name, LEVEL
FROM tree
WHERE LEVEL=2
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name level
=====================================================
3 1 'Jonas' 2
4 1 'Smith' 2
5 2 'Verma' 2
6 2 'Foster' 2
The following example shows how to add LEVEL conditions after the CONNECT BY statement.
SELECT LEVEL FROM db_root CONNECT BY LEVEL <= 10;
level
=============
1
2
3
4
5
6
7
8
9
10
Note that the format of “CONNECT BY expr(LEVEL) < expr”, for example “CONNECT BY LEVEL +1 < 5”) is not supported.
CONNECT_BY_ISLEAF¶
CONNECT_BY_ISLEAF is a pseudocolumn representing that the result of hierarchical query is leaf node. If the current row is a leaf node, it returns 1; otherwise, it returns 0.
The following example shows how to retrieve the CONNECT_BY_ISLEAF value to check whether it is a leaf node or not.
-- Checking a CONNECT_BY_ISLEAF value
SELECT id, mgrid, name, CONNECT_BY_ISLEAF
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name connect_by_isleaf
=============================================================
1 NULL 'Kim' 0
2 NULL 'Moy' 0
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 1
6 2 'Foster' 0
7 6 'Brown' 1
CONNECT_BY_ISCYCLE¶
CONNECT_BY_ISCYCLE is a pseudocolumn representing that a cycle was detected while processing the node, meaning that a child was also found to be an ancestor. A value of 1 for a row means a cycle was detected; the pseudo-column’s value is 0, otherwise.
The CONNECT_BY_ISCYCLE pseudo-column can be used in the WHERE, ORDER BY and GROUP BY … HAVING clauses of the SELECT statement. It can also be used in aggregate functions.
Note
This pseudocolumn is available only when the NOCYCLE keyword is used in the statement.
The following example shows how to retrieve the CONNECT_BY_ISCYCE value to check a row that occurs loop.
-- Creating a tree_cycle table and 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');
-- Checking a CONNECT_BY_ISCYCLE value
SELECT id, mgrid, name, CONNECT_BY_ISCYCLE
FROM tree_cycle
START WITH name in ('Kim', 'Moy')
CONNECT BY NOCYCLE PRIOR id=mgrid
ORDER BY id;
id mgrid name connect_by_iscycle
==================================================
1 NULL 'Kim' 0
2 11 'Moy' 0
3 1 'Jonas' 0
4 1 'Smith' 0
5 3 'Verma' 0
6 3 'Foster' 0
7 4 'Brown' 0
8 4 'Lin' 0
9 2 'Edwin' 0
10 9 'Audrey' 0
11 10 'Stone' 1
Operators for Hierarchical Query¶
CONNECT_BY_ROOT¶
The CONNECTION_BY_ROOT operator returns the value of a root row as a column value. This operator can be used in the WHERE and ORDER BY clauses of the SELECT statement.
The following example shows how to retrieve the root row’s id value.
-- Checking the id value of a root row for each row
SELECT id, mgrid, name, CONNECT_BY_ROOT id
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name connect_by_root id
=============================================================
1 NULL 'Kim' 1
2 NULL 'Moy' 2
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 2
PRIOR¶
The PRIOR operator returns the value of a parent row as a column value and returns NULL for the root row. This operator can be used in the WHERE, ORDER BY and CONNECT BY clauses of the SELECT statement.
The following example shows how to retrieve the parent row’s id value.
-- Checking the id value of a parent row for each row
SELECT id, mgrid, name, PRIOR id as "prior_id"
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name prior_id
=====================================================
1 NULL 'Kim' NULL
2 NULL 'Moy' NULL
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 6
Functions for Hierarchical Query¶
SYS_CONNECT_BY_PATH¶
The SYS_CONNECT_BY_PATH function returns the hierarchical path from a root to the specified row in string. The column and separator specified as an argument must be a character type. Each path separated by specified separator will be displayed consecutively. This function can be used in the WHERE and ORDER BY clauses of the SELECT statement.
SYS_CONNECT_BY_PATH (column_name, separator_char)
The following example shows how to retrieve path from a root to the specified row.
-- Executing a hierarchical query with SYS_CONNECT_BY_PATH function
SELECT id, mgrid, name, SYS_CONNECT_BY_PATH(name,'/') as [hierarchy]
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name hierarchy
==============================================================
1 NULL 'Kim' '/Kim'
2 NULL 'Moy' '/Moy'
3 1 'Jonas' '/Kim/Jonas'
4 1 'Smith' '/Kim/Smith'
5 2 'Verma' '/Moy/Verma'
6 2 'Foster' '/Moy/Foster'
7 6 'Brown' '/Moy/Foster/Brown'
Examples of Hierarchical Query¶
The examples in this page shows how to write hierarchical queries by specifying the CONNECT BY clause within the SELECT statement.
A table that have relationship with recursive reference is create and the table consists of two columns named ID and ParentID; assume that ID is a primary key for the table and ParentID is a foreign key for the same table. In this context, the root node will have a ParentID value of NULL.
Once a table is create, you can get the entire data with hierarchical structure and a value of LEVEL by using the UNION ALL as shown below.
CREATE TABLE tree_table (ID int PRIMARY KEY, ParentID int, name VARCHAR(128));
INSERT INTO tree_table VALUES (1,NULL,'Kim');
INSERT INTO tree_table VALUES (2,1,'Moy');
INSERT INTO tree_table VALUES (3,1,'Jonas');
INSERT INTO tree_table VALUES (4,1,'Smith');
INSERT INTO tree_table VALUES (5,3,'Verma');
INSERT INTO tree_table VALUES (6,3,'Foster');
INSERT INTO tree_table VALUES (7,4,'Brown');
INSERT INTO tree_table VALUES (8,4,'Lin');
INSERT INTO tree_table VALUES (9,2,'Edwin');
INSERT INTO tree_table VALUES (10,9,'Audrey');
INSERT INTO tree_table VALUES (11,10,'Stone');
SELECT L1.ID, L1.ParentID, L1.name, 1 AS [Level]
FROM tree_table AS L1
WHERE L1.ParentID IS NULL
UNION ALL
SELECT L2.ID, L2.ParentID, L2.name, 2 AS [Level]
FROM tree_table AS L1
INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID
WHERE L1.ParentID IS NULL
UNION ALL
SELECT L3.ID, L3.ParentID, L3.name, 3 AS [Level]
FROM tree_table AS L1
INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID
INNER JOIN tree_table AS L3 ON L2.ID=L3.ParentID
WHERE L1.ParentID IS NULL
UNION ALL
SELECT L4.ID, L4.ParentID, L4.name, 4 AS [Level]
FROM tree_table AS L1
INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID
INNER JOIN tree_table AS L3 ON L2.ID=L3.ParentID
INNER JOIN tree_table AS L4 ON L3.ID=L4.ParentID
WHERE L1.ParentID IS NULL;
ID ParentID name Level
=====================================================
1 NULL 'Kim' 1
2 1 'Moy' 2
3 1 'Jonas' 2
4 1 'Smith' 2
9 2 'Edwin' 3
5 3 'Verma' 3
6 3 'Foster' 3
7 4 'Brown' 3
8 4 'Lin' 3
10 9 'Audrey' 4
Because you do not know how many levels exist in the data, you can rewrite the query above as a stored procedure that loops until no new row is retrieved.
However, the hierarchical structure should be checked every step while looping, specify the CONNECT BY clause within the SELECT statement as follows; the example below shows how to get the entire data with hierarchical structure and the level of each row in the hierarchy.
SELECT ID, ParentID, name, Level
FROM tree_table
START WITH ParentID IS NULL
CONNECT BY ParentID=PRIOR ID;
ID ParentID name level
=====================================================
1 NULL 'Kim' 1
2 1 'Moy' 2
9 2 'Edwin' 3
10 9 'Audrey' 4
11 10 'Stone' 5
3 1 'Jonas' 2
5 3 'Verma' 3
6 3 'Foster' 3
4 1 'Smith' 2
7 4 'Brown' 3
8 4 'Lin' 3
You can specify NOCYCLE to prevent an error from occurring as follows. When you run the following query, a loop does not appear; therefore, the result is the same as the above.
SELECT ID, ParentID, name, Level
FROM tree_table
START WITH ParentID IS NULL
CONNECT BY NOCYCLE ParentID=PRIOR ID;
CUBRID judges that this hierarchical query has a loop if the same row is found during searching process on the query. The below is an example that the loop exists; we define NOCYCLE to end the additional searching process if a loop exists.
CREATE TABLE tbl(seq INT, id VARCHAR(10), parent VARCHAR(10));
INSERT INTO tbl VALUES (1, 'a', null);
INSERT INTO tbl VALUES (2, 'b', 'a');
INSERT INTO tbl VALUES (3, 'b', 'c');
INSERT INTO tbl VALUES (4, 'c', 'b');
INSERT INTO tbl VALUES (5, 'c', 'b');
SELECT seq, id, parent, LEVEL,
CONNECT_BY_ISCYCLE AS iscycle,
CAST(SYS_CONNECT_BY_PATH(id,'/') AS VARCHAR(10)) AS idpath
FROM tbl
START WITH PARENT is NULL
CONNECT BY NOCYCLE PARENT = PRIOR id;
seq id parent level iscycle idpath
=============================================================================
1 'a' NULL 1 0 '/a'
2 'b' 'a' 2 0 '/a/b'
4 'c' 'b' 3 0 '/a/b/c'
3 'b' 'c' 4 1 '/a/b/c/b'
5 'c' 'b' 5 1 '/a/b/c/b/c'
5 'c' 'b' 3 0 '/a/b/c'
3 'b' 'c' 4 1 '/a/b/c/b'
4 'c' 'b' 5 1 '/a/b/c/b/c'
The below shows to output dates of March, 2013(201303) with a hierarchical query.
SELECT TO_CHAR(base_month + lvl -1, 'YYYYMMDD') h_date
FROM (
SELECT LEVEL lvl, base_month
FROM (
SELECT TO_DATE('201303', 'YYYYMM') base_month FROM db_root
)
CONNECT BY LEVEL <= LAST_DAY(base_month) - base_month + 1
);
h_date
======================
'20130301'
'20130302'
'20130303'
'20130304'
'20130305'
'20130306'
'20130307'
'20130308'
'20130309'
'20130310'
'20130311'
'20130312'
'20130313'
'20130314'
'20130315'
'20130316'
'20130317'
'20130318'
'20130319'
'20130320'
'20130321'
'20130322'
'20130323'
'20130324'
'20130325'
'20130326'
'20130327'
'20130328'
'20130329'
'20130330'
'20130331'
31 rows selected. (0.066175 sec) Committed.
Performance of Hierarchical Query¶
Although this form is shorter and clearer, please keep in mind that it has its limitations regarding speed.
If the result of the query contains all the rows of the table, the CONNECT BY form might be slower as it has to do additional processing (such as cycle detection, pseudo-column bookkeeping and others). However, if the result of the query only contains a part of the table rows, the CONNECT BY form might be faster.
For example, if we have a table with 20,000 records and we want to retrieve a sub-tree of roughly 1,000 records, a SELECT statement with a START WITH … CONNECT BY clause will run up to 30% faster than an equivalent UNION ALL with SELECT statements.