 | This article may be too technical for most readers to understand. (April 2018) |
A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.
In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions (CTEs). Unlike Oracle's earlier connect-by clause, recursive CTEs were designed with fixpoint semantics from the beginning.[1] Recursive CTEs from the standard were relatively close to the existing implementation in IBM DB2 version 2.[1] Recursive CTEs are also supported by Microsoft SQL Server (since SQL Server 2008 R2),[2] Firebird 2.1,[3] PostgreSQL 8.4+,[4] SQLite 3.8.3+,[5] IBM Informix version 11.50+, CUBRID, MariaDB 10.2+ and MySQL 8.0.1+.[6] Tableau has documentation describing how CTEs can be used. TIBCO Spotfire does not support CTEs, while Oracle 11g Release 2's implementation lacks fixpoint semantics.
Without common table expressions or connected-by clauses it is possible to achieve hierarchical queries with user-defined recursive functions.[7]
- ^ a b Jim Melton; Alan R. Simon (2002). SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. ISBN 978-1-55860-456-8.
- ^ Microsoft. "Recursive Queries Using Common Table Expressions". Retrieved 2009-12-23.
- ^ Helen Borrie (2008-07-15). "Firebird 2.1 Release Notes". Retrieved 2015-11-24.
- ^ "WITH Queries". 10 February 2022. PostgreSQL
- ^ "WITH Clause". SQLite
- ^ "MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs)". Archived from the original on 2019-08-16. Retrieved 2017-12-20. mysqlserverteam.com
- ^ Paragon corporation: Using PostgreSQL User-Defined Functions to solve the Tree Problem, February 15, 2004, accessed September 19, 2015