Common table expressions
   HOME

TheInfoList



Click Here for Items Related To - Common table expressions
OR:
Common table expressions on:   [Wikipedia]   [Google]   [Amazon]

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 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 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.


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 ma ...
), views, 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 ...
(starting with version 14.1), Firebird (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 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 ...
(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 mo ...
(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 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) *Less than *Temperatures below freezing *Hell or underworld People with the surname *Ernst von Below (1863–1955), German World War I general *Fred 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 In mathematics, the factorial of a non-negative denoted is the product of all positive integers less than or equal The factorial also equals the product of n with the next smaller factorial: \begin n! &= n \times (n-1) \times (n-2) \ ...
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 Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online ...
,
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 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 ...
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