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 the ...
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 ...
s.
In standard
SQL:1999 hierarchical queries are implemented by way of recursive ''
common table expressions
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 ...
'' (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 ma ...
(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 I ...
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
The SQL SELECT statement returns a result set of records, from one or more tables.
A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data man ...
),
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
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON a ...
,
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 I ...
(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 ma ...
(starting with version 2005),
Oracle
An oracle is a person or agency considered to provide wise and insightful counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. As such, it is a form of divination.
Description
The word '' ...
(with recursion since 11g release 2),
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
(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 o ...
(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 most ...
(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 I ...
(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. Big ...
,
Sybase
Sybase, Inc. was an enterprise software and services company. The company produced software to manage and analyze information in relational databases, with facilities located in California and Massachusetts. Sybase was acquired by SAP in 2010; ...
(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
Below may refer to:
*Earth
*Ground (disambiguation)
*Soil
*Floor
*Bottom (disambiguation)
Bottom may refer to:
Anatomy and sex
* Bottom (BDSM), the partner in a BDSM who takes the passive, receiving, or obedient role, to that of the top or ...
); if these are desired, they have to be created in the code. See MSDN documentation
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
. Using a CTE inside an
, 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
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.