WITH queries — Common Table Expressions (CTE), hierarchical and recursive queries in SQL

A hierarchical query is a type of SQL query that processes data organized in a hierarchical model. These are special cases of the more general fixed-point recursive queries, which compute transitive closures.

In standard SQL:1999, hierarchical queries are implemented using recursive common table expressions (CTEs). Unlike Oracle's earlier CONNECT BY clause, recursive CTEs were designed from the outset with fixed-point semantics. The standard's recursive CTEs were relatively close to the existing implementation in IBM DB2 version 2. Recursive CTEs are also supported by Microsoft SQL Server (starting with SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, IBM Informix version 11.50+, CUBRID, and MySQL 8.0.1+. Tableau and TIBCO Spotfire do not support CTEs, while Oracle 11g Release 2's implementation lacks fixed-point semantics.

Without common table expressions or CONNECT BY clauses, it is still possible to perform hierarchical queries using custom recursive functions.

WITH provides a way to write auxiliary statements for use in a larger query. These statements, also called common table expressions (CTEs), can be thought of as defining temporary tables that exist only for a single query. The auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE, and the WITH clause itself is attached to a primary statement, which can also be a SELECT, INSERT, UPDATE, or DELETE.

A CTE plays the role of an SQL view that is created within the scope of a single query and is not persisted as a schema object.

A common table expression (CTE) can be thought of as a temporary result set defined within the scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and is only in effect for the duration of the query. However, unlike a derived table, a CTE can be self-referencing and can be referenced multiple times within the same query.

Everything works similarly to using a view, except for the required parentheses enclosing the query; formally, it is enough to replace CREATE VIEW with WITH. As with a view, the parentheses after the CTE name may contain a list of columns, if we need to include only some of them from the underlying query and/or rename them

A CTE can be used to:

• Create a recursive query.
• Substitute for a view when the general reusability of a view is not required; that is, you don't need to store the definition in metadata.
• Enable grouping by a column derived from a scalar subquery, or by a function that is non-deterministic or has external access.
• Reference a derived table multiple times within the same statement.

Using a CTE offers the benefits of improved readability and easier maintenance of complex queries. A query can be broken up into separate, simple, logical building blocks. These simple blocks can then be used to build up more complex temporary CTEs until the final result set is produced.

A common table expression, or CTE, (in SQL) is a temporary named result set derived from a simple query and defined within the scope of the execution of a SELECT, INSERT, UPDATE, or DELETE statement.

A CTE can be thought of as an alternative to derived tables (subqueries), views, and inline user-defined functions.

Common table expressions are supported by Teradata, DB2, [[Firebird], Microsoft SQL Server, Oracle (with recursion starting in version 11g Release 2), PostgreSQL (starting with 8.4), MariaDB (starting with 10.2), MySQL (starting with 8.0), SQLite (starting with 3.8.3), HyperSQL, and H2 (experimental). Oracle calls CTEs «subquery factoring».

The syntax for a recursive CTE is as follows:

WITH [RECURSIVE] with_query [, ...]
SELECT...

where the syntax of with_query is:

query_name [ (column_name [,...]) ] AS (SELECT ...)

Recursive CTEs (or «recursive subquery factoring»[10] in Oracle's terminology) can be used to traverse relationships (represented as graphs or trees), although the syntax is considerably more complex, since no automatic pseudo-columns are generated (such as LEVEL below); if these are desired, they must be created in code. See the MSDN documentation or IBM documentation[11] for tutorial examples.

The keyword RECURSIVE is generally not required after WITH in systems other than PostgreSQL.[12]

In SQL:1999, a recursive (CTE) query may appear anywhere a query is allowed. For example, one can name the result using CREATE [RECURSIVE] VIEW. Using a CTE inside INSERT INTO, a table can be populated with data generated from a recursive query; random data generation is possible using this technique without resorting to procedural statements.[13]

Some databases, such as PostgreSQL, support a shorter CREATE RECURSIVE VIEW form, which is internally translated into WITH RECURSIVE syntax. [14]

An example of a recursive query that computes the factorial of the numbers from 0 to 9 is as follows:

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;

An alternative, non-standard syntax is the CONNECT BY construct; it was introduced by Oracle in the 1980s. Before Oracle 10g, this construct was only useful for traversing acyclic graphs, since it returned an error when any cycles were detected; in version 10g, Oracle introduced the NOCYCLE function (and keyword), thanks to which traversal works even in the presence of cycles.[15]

CONNECT BY is supported by EnterpriseDB, Oracle Database,[16] CUBRID,[17] IBM Informix, and DB2, though only when enabled as a compatibility mode. The syntax is as follows:

 SELECT select_list
 FROM table_expression
 [ WHERE ... ]
 [ START WITH start_expression ]
 CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
 [ 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 of the above query would look as follows:

 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)

• LEVEL
• CONNECT_BY_ISLEAF
• CONNECT_BY_ISCYCLE
• CONNECT_BY_ROOT

The following example returns the surname of each employee in department 10, each manager above that employee in the hierarchy, the number of levels between the manager and the employee, and the path between them:

   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";

• SYS_CONNECT_BY_PATH

Recursive CTEs

Recursive common table expressions (CTEs) were an implementation of the SQL:1999 standard for hierarchical queries. The first implementations of recursive CTEs began to appear in 2007. The standard's recursive CTEs were relatively close to the existing implementation in IBM DB2 version 2. Recursive CTEs were eventually supported by Microsoft SQL Server (starting with SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, Oracle 11g Release 2, and IBM Informix version 11.50+.

Without common table expressions or CONNECT BY expressions, it is still possible to perform hierarchical queries using custom recursive functions, but these tend to result in very complex SQL.

CTEs in MariaDB

In MariaDB, a non-recursive CTE is essentially treated as a local view for the query, whose syntax is more readable than a nested FROM (SELECT…). A CTE can reference another and can itself be referenced from multiple places.

Thus, CTEs are similar to derived tables. For example:

SQL with a derived table:

SQL with a CTE:

SQL is generally poor at recursion. One of the advantages of CTEs is that they allow a query to reference itself, and thus enable recursive SQL. A recursive CTE will repeatedly execute against subsets of the data until it obtains the complete result set. This makes it especially useful for processing hierarchical or tree-structured data.

With recursive CTEs, you can accomplish things that would be very difficult to do with standard SQL, and with higher execution speed. They can help solve many types of business problems and even simplify some complex SQL/application logic down to a simple recursive database call.

Some example use cases for recursive CTEs are: finding gaps in data, generating organizational charts, and generating test data.

WITH RECURSIVE denotes a recursive CTE. It is given a name, followed by a body (the main query):

Below is a recursive CTE that counts from 1 to 50.
  

The above statement prints a series of numbers from 1 to 49.

CTEs in MySQL

MySQL 8.0 adds CTEs via the standard WITH keyword, in much the same way as it is implemented in competing products.

To specify common table expressions, use a WITH clause containing one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set and associates a name with the subquery. The following example defines CTEs named cte1 and cte2 in the WITH clause and references them in the top-level SELECT that follows the WITH clause:

Immediately before SELECT for statements that include a SELECT statement:

• INSERT … WITH … SELECT …
• REPLACE … WITH … SELECT …
• CREATE TABLE … WITH … SELECT …
• CREATE VIEW … WITH … SELECT …
• DECLARE CURSOR … WITH … SELECT …
• EXPLAIN … WITH … SELECT …

A recursive common table expression – is an expression having a subquery that refers to its own name. For example:

  

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

See also

• SQL subqueries
• [[b9359]]

See also

• Window functions in SQL