A hierarchical query is a type of
SQL query that handles
hierarchical model
A hierarchical database model is a data model in which the data are organized into a tree-like structure. The data are stored as records which are connected to one another through links. A record is a collection of fields, with each field containin ...
data. They are special cases of more general recursive
fixpoint
A fixed point (sometimes shortened to fixpoint, also known as an invariant point) is a value that does not change under a given transformation. Specifically, in mathematics, a fixed point of a function is an element that is mapped to itself by th ...
queries, which compute
transitive closure
In mathematics, the transitive closure of a binary relation on a set is the smallest relation on that contains and is transitive. For finite sets, "smallest" can be taken in its usual sense, of having the fewest related pairs; for infinite ...
s.
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.
Recursive CTEs from the standard were relatively close to the existing implementation in IBM DB2 version 2.
Recursive CTEs are also supported by
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
(since SQL Server 2008 R2),
Firebird 2.1,
PostgreSQL 8.4+,
SQLite 3.8.3+,
IBM Informix
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose ...
version 11.50+,
CUBRID
CUBRID ( "cube-rid") is an open-source SQL-based relational database management system (RDBMS) with object extensions developed by CUBRID Corp. for OLTP. The name CUBRID is a combination of the two words ''cube'' and ''bridge'', ''cube'' standi ...
,
MariaDB 10.2+ and
MySQL 8.0.1+Tableau has documentationdescribing 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.
Common table expression
A common table expression, or CTE, (in
SQL) is a temporary named result set, derived from a simple query and defined within the execution scope of a
SELECT
,
INSERT
,
UPDATE
, or
DELETE
statement.
CTEs can be thought of as alternatives to derived tables (
subquery),
views
A view is a sight or prospect or the ability to see or be seen from a particular place.
View, views or Views may also refer to:
Common meanings
* View (Buddhism), a charged interpretation of experience which intensely shapes and affects thou ...
, and inline user-defined functions.
Common table expressions are supported by
Teradata
Teradata Corporation is an American software company that provides cloud database and analytics-related software, products, and services. The company was formed in 1979 in Brentwood, California, as a collaboration between researchers at Caltech ...
(starting with version 14),
IBM Db2,
Informix
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose ...
(starting with version 14.1),
Firebird
Firebird and fire bird may refer to:
Mythical birds
* Phoenix (mythology), sacred firebird found in the mythologies of many cultures
* Bennu, Egyptian firebird
* Huma bird, Persian firebird
* Firebird (Slavic folklore)
Bird species
''Various sp ...
(starting with version 2.1),
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
(starting with version 2005),
Oracle (with recursion since 11g release 2),
PostgreSQL (since 8.4),
MariaDB
MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the ori ...
(since 10.2),
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database ...
(since 8.0),
SQLite
SQLite (, ) is a database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it belongs to the family of embedded databases. It is the m ...
(since 3.8.3),
HyperSQL,
Informix
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose ...
(since 14.10), Google
BigQuery
BigQuery is a fully managed, serverless data warehouse that enables scalable analysis over petabytes of data. It is a ''Platform as a Service'' ( PaaS) that supports querying using ANSI SQL. It also has built-in machine learning capabilities. B ...
,
Sybase (starting with version 9),
Vertica
Vertica Systems is an analytic database management software company. Vertica was founded in 2005 by the database researcher Michael Stonebraker, with Andrew Palmer as the founding CEO. Ralph Breslauer and Christopher P. Lynch served as later ...
,
H2 (experimental), and
many others
Many may refer to:
* grammatically plural in number
*an English quantifier used with count nouns indicating a large but indefinite number of; at any rate, more than a few
;Place names
* Many, Moselle, a commune of the Moselle department in Franc ...
. Oracle calls CTEs "subquery factoring".
The syntax for a CTE (which may or may not be recursive) is as follows:
WITH ECURSIVEwith_query ...SELECT ...
where
with_query
‘s syntax is:
query_name (column_name_[,..._.html"_;"title="....html"_;"title="(column_name_[,...">(column_name_[,..._">....html"_;"title="(column_name_[,...">(column_name_[,..._AS_(SELECT_...)
Recursive_CTEs_can_be_used_to_traverse_relations_(as_graphs_or_trees)_although_the_syntax_is_much_more_involved_because_there_are_no_automatic_pseudo-columns_created_(like_
LEVEL
_#Pseudo-columns.html" ;"title="...">(column_name_[,..._.html" ;"title="....html" ;"title="(column_name [,...">(column_name [,... ">....html" ;"title="(column_name [,...">(column_name [,... AS (SELECT ...)
Recursive CTEs can be used to traverse relations (as graphs or trees) although the syntax is much more involved because there are no automatic pseudo-columns created (like
LEVEL
#Pseudo-columns">below); if these are desired, they have to be created in the code. See MSDN documentation
or IBM documentation for tutorial examples.
The
RECURSIVE
keyword is not usually needed after WITH in systems other than PostgreSQL.
In SQL:1999 a recursive (CTE) query may appear anywhere a query is allowed. It's possible, for example, to name the result using
CREATE
[
RECURSIVE
]
VIEW
. Using a CTE inside an
INSERT INTO
, one can populate a table with data generated from a recursive query; random data generation is possible using this technique without using any procedural statements.
Some Databases, like PostgreSQL, support a shorter CREATE RECURSIVE VIEW format which is internally translated into WITH RECURSIVE coding.
An example of a recursive query computing the
factorial of numbers from 0 to 9 is the following:
WITH recursive temp (n, fact) AS
(
SELECT 0,
1 -- Initial Subquery
UNION ALL
SELECT n+1,
(n+1)*fact
FROM temp -- Recursive Subquery
WHERE n < 9)
SELECT *
FROM temp;
CONNECT BY
An alternative syntax is the non-standard
CONNECT BY
construct; it was introduced by Oracle in the 1980s. Prior to Oracle 10g, the construct was only useful for traversing acyclic graphs because it returned an error on detecting any cycles; in version 10g Oracle introduced the NOCYCLE feature (and keyword), making the traversal work in the presence of cycles as well.
CONNECT BY
is supported by
Snowflake
A snowflake is a single ice crystal that has achieved a sufficient size, and may have amalgamated with others, which falls through the Earth's atmosphere as snow.Knight, C.; Knight, N. (1973). Snow crystals. Scientific American, vol. 228, no. ...
,
EnterpriseDB
EnterpriseDB (EDB), a privately held company based in Massachusetts, provides software and services based on the open-source database PostgreSQL (also known as Postgres), and is one of the largest contributors to Postgres. EDB develops and int ...
,
Oracle database,
CUBRID
CUBRID ( "cube-rid") is an open-source SQL-based relational database management system (RDBMS) with object extensions developed by CUBRID Corp. for OLTP. The name CUBRID is a combination of the two words ''cube'' and ''bridge'', ''cube'' standi ...
,
IBM Informix
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose ...
Hierarchical Clause
IBM Informix and IBM Db2 although only if it is enabled as a compatibility mode. The syntax is as follows:
SELECT select_list
FROM table_expression
WHERE ... START WITH start_expression CONNECT BY OCYCLE
_DESC_ _DESC_[,_column2_[_ASC_">_DESC_.html" ;"title="_column2_[_ASC_.html" ;"title="ASC_.html" ;"title="ORDER SIBLINGS BY column1 [ ASC "> DESC [, column2 [ ASC "> DESC ">_column2_[_ASC_.html" ;"title="ASC_.html" ;"title="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)
Pseudo-columns
*
*
*
*
Unary operators
The following example returns the last name of each employee in department 10, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:
SELECT ename "Employee", CONNECT_BY_ROOT ename "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
WHERE LEVEL > 1 and deptno = 10
CONNECT BY PRIOR empno = mgr
ORDER BY "Employee", "Manager", "Pathlen", "Path";
Functions
* SYS_CONNECT_BY_PATH
See also
* Datalog
Datalog is a declarative logic programming language. While it is syntactically a subset of Prolog, Datalog generally uses a bottom-up rather than top-down evaluation model. This difference yields significantly different behavior and properties ...
also implements fixpoint queries
* Deductive database
A deductive database is a database system that can make deductions (i.e. conclude additional facts) based on rules and facts stored in the (deductive) database. Datalog is the language typically used to specify facts, rules and queries in deductiv ...
s
* Hierarchical model
A hierarchical database model is a data model in which the data are organized into a tree-like structure. The data are stored as records which are connected to one another through links. A record is a collection of fields, with each field containin ...
* Reachability
In graph theory, reachability refers to the ability to get from one Vertex (graph theory), vertex to another within a graph. A vertex s can reach a vertex t (and t is reachable from s) if there exists a sequence of Glossary of graph theory#Basics, ...
* Transitive closure
In mathematics, the transitive closure of a binary relation on a set is the smallest relation on that contains and is transitive. For finite sets, "smallest" can be taken in its usual sense, of having the fewest related pairs; for infinite ...
* Tree structure
A tree structure, tree diagram, or tree model is a way of representing the hierarchical nature of a structure in a graphical form. It is named a "tree structure" because the classic representation resembles a tree, although the chart is genera ...
References
Further reading
*
Academic textbooks. Note that these cover only the SQL:1999 standard (and Datalog), but not the Oracle extension.
*
* Chapter 24.
*
External links
* https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring
* http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/
* https://web.archive.org/web/20131114094211/http://gennick.com/with.html
* http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf
* http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html
{{DEFAULTSORT:Hierarchical Query
Database management systems
SQL
Articles with example SQL code
Recursion