Hierarchical and recursive queries in SQL information
This article may be too technical for most readers to understand. Please help improve it to make it understandable to non-experts, without removing the technical details.(April 2018) (Learn how and when to remove this message)
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]
^ abJim 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.
A hierarchicalquery is a type of SQLquery that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which...
Structured Query Language (SQL) (pronounced S-Q-L; historically "sequel") is a domain-specific language used to manage data, especially in a relational...
PostgreSQL (/ˈpoʊstɡrɛs ˌkjuː ˈɛl/, POHST-gres kyoo el), also known as Postgres, is a free and open-source relational database management system (RDBMS)...
standard part of the SQL grammar. Insignificant whitespace is generally ignored inSQL statements andqueries, making it easier to format SQL code for readability...
page has been removed from MySQL.com) Hierarchical data in MySQL: parents and children in one query Create Hierarchy Chart from Hierarchical Database...
constructs, such as MySQL 5.x. However, recursiveSQLqueries can be expected to perform comparably for 'find immediate descendants' queries, and much faster for...
represented in a hierarchical database but not a SQL database, since SQL does not support nested tables. The automated evaluation of any query relating to...
for storing the data and retrieval procedures for answering queries. Most relational databases use the SQL data definition andquery language; these systems...
information (for example spatial data and engineering data) where access is inherently recursive: the mathematics underpinning SQL (specifically, first-order predicate...
tags/ and @/ is the actual query. The +/ operator joins the results of different sub-queriesin one single list. The @/ operator ends the query. To be...
database query languages, such as recursive WITH inSQL:1999. More expressive logics, like fixpoint logics, have therefore been studied in finite model...
declarative languages like SQLand Lex/Yacc use some elements of functional programming, such as not allowing mutable values. In addition, many other programming...
automatically by a parser. See also the UML class and object diagram below. Specialized database query languages such as SQL. Specialized computer languages that are...
CSIS (SEE-sis), and IUPAC (I-U-pak). Some are not universally pronounced either way, but by speaker's preference or by context, such as SQL (either "see-kwel"...
arranged in a hierarchy. At the top was the application itself, referenced via a null object descriptor. Other objects were referenced by (recursively) specifying...
and "?" to match a single character. Relics of this can be found today in the glob syntax for filenames, andin the SQL LIKE operator. Starting in 1997...
backward tracing queries for MapReduce dataflows, but are not generic to different DISC systems and do not perform efficient forward queries. Lipstick, a...
notion of recursive enumerability for fuzzy subsets is the adequate one. In order to solve this, an extension of the notions of fuzzy grammar and fuzzy Turing...
Global Information