Recursive SQL Query: Difference between revisions

From GM-RKB
Jump to navigation Jump to search
m (Text replacement - "<ref name=[\w\d\:\s\"]{1,26}\/>" to " ")
m (Text replacement - "sions]]" to "sion]]s")
 
Line 9: Line 9:
=== 2016 ===
=== 2016 ===
* (Wikipedia, 2016) ⇒ http://wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL Retrieved:2016-4-7.
* (Wikipedia, 2016) ⇒ http://wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL Retrieved:2016-4-7.
** A '''hierarchical query''' is a type of [[Select (SQL)|SQL query]] that handles [[hierarchical model]] data. They are special case of more general recursive fixpoint queries, which compute [[transitive closure]]s.        <P>        In standard [[SQL:1999]] hierarchical queries are implemented by way of recursive ''[[#Common table expression|common table expressions]]'' (CTEs). Unlike the Oracle extension described below, the recursive CTEs were designed with [[fixpoint]] semantics from the beginning. The 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]], [[Firebird (database server)|Firebird 2.1]], [[PostgreSQL|PostgreSQL 8.4+]], [[SQLite|SQLite 3.8.3+]],  Oracle 11g Release 2, [[IBM Informix]] version 11.50+ and [[CUBRID]]. An alternative syntax is the non-standard <code>CONNECT BY</code> 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. Without Common-table-expressions or a connected-by clause it is possible to achieve hierarchical queries with user-defined recursive functions. <ref> [http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=24 Paragon corporation: Using PostgreSQL User-Defined Functions to solve the Tree Problem], February 15, 2004, accessed September 19, 2015 </ref>
** A '''hierarchical query''' is a type of [[Select (SQL)|SQL query]] that handles [[hierarchical model]] data. They are special case of more general recursive fixpoint queries, which compute [[transitive closure]]s.        <P>        In standard [[SQL:1999]] hierarchical queries are implemented by way of recursive ''[[#Common table expression|common table expression]]s'' (CTEs). Unlike the Oracle extension described below, the recursive CTEs were designed with [[fixpoint]] semantics from the beginning. The 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]], [[Firebird (database server)|Firebird 2.1]], [[PostgreSQL|PostgreSQL 8.4+]], [[SQLite|SQLite 3.8.3+]],  Oracle 11g Release 2, [[IBM Informix]] version 11.50+ and [[CUBRID]]. An alternative syntax is the non-standard <code>CONNECT BY</code> 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. Without Common-table-expressions or a connected-by clause it is possible to achieve hierarchical queries with user-defined recursive functions. <ref> [http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=24 Paragon corporation: Using PostgreSQL User-Defined Functions to solve the Tree Problem], February 15, 2004, accessed September 19, 2015 </ref>
<references/>
<references/>


Latest revision as of 03:27, 28 April 2024

A Recursive SQL Query is a recursive query that is a SQL query.

References

2016

  • (Wikipedia, 2016) ⇒ http://wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL Retrieved:2016-4-7.
    • A hierarchical query is a type of SQL query that handles hierarchical model data. They are special case 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 the Oracle extension described below, the recursive CTEs were designed with fixpoint semantics from the beginning. The 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, Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, Oracle 11g Release 2, IBM Informix version 11.50+ and CUBRID. 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. Without Common-table-expressions or a connected-by clause it is possible to achieve hierarchical queries with user-defined recursive functions. [1]

  1. ↑ Paragon corporation: Using PostgreSQL User-Defined Functions to solve the Tree Problem, February 15, 2004, accessed September 19, 2015
Retrieved from "http://www.gabormelli.com/RKB/index.php?title=Recursive_SQL_Query&oldid=874808"