Hierarchical query
From Wikipedia, the free encyclopedia
 |
It has been suggested that Common table expressions be merged into this article or section. (Discuss) Proposed since February 2009. |
|
A hierarchical query is a type of SQL query that handles hierarchical model data.
Standard SQL specifies hierarchical queries by way of recursive common table expressions (CTEs). Recursive CTEs are supported by systems including IBM DB2[citation needed], Microsoft SQL Server,[1] Firebird 2.1[2] , PostgreSQL 8.4[3], Oracle 11g and CUBRID.
An alternative syntax is the non-standard CONNECT BY construct.
[edit] CONNECT BY
"CONNECT BY" is supported by EnterpriseDB[4], Oracle database[5] and CUBRID. Example query:
SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ...
[ GROUP BY ... ]
[ HAVING ... ]
...
- For example
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager" FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;
The output from the above query would look like:
level | employee | empno | manager
-------+-------------+-------+---------
1 | KING | 7839 |
2 | JONES | 7566 | 7839
3 | SCOTT | 7788 | 7566
4 | ADAMS | 7876 | 7788
3 | FORD | 7902 | 7566
4 | SMITH | 7369 | 7902
2 | BLAKE | 7698 | 7839
3 | ALLEN | 7499 | 7698
3 | WARD | 7521 | 7698
3 | MARTIN | 7654 | 7698
3 | TURNER | 7844 | 7698
3 | JAMES | 7900 | 7698
2 | CLARK | 7782 | 7839
3 | MILLER | 7934 | 7782
(14 rows)
[edit] Pseudocolumns
- LEVEL
- CONNECT_BY_ISLEAF
- CONNECT_BY_ISCYCLE
[edit] unary operators
- CONNECT_BY_ROOT
[edit] Functions
- SYS_CONNECT_BY_PATH
[edit] See also
[edit] References
- ^ Microsoft. "Recursive Queries Using Common Table Expressions". http://msdn.microsoft.com/en-us/library/ms186243.aspx. Retrieved 2009-12-23.
- ^ Helen Borrie (2008-07-15). "Firebird 2.1 Release Notes". http://www.firebirdsql.org/devel/doc/rlsnotes/html/rlsnotes210.html#rnfb210-cte. Retrieved 2009-02-07.
- ^ "WITH Queries". http://www.postgresql.org/docs/8.4/interactive/queries-with.html. PostgreSQL
- ^ Hierarchical Queries, EnterpriseDB
- ^ Hierarchical Queries, Oracle
|
||||||||||||||||||||
