WITH (Common Table Expressions)

Non-recursive (standard) CTE:

WITH cte [, ...]

cte:
    expression_name[(column_name, ...)]
    AS
    select_statement

Recursive CTE:

WITH RECURSIVE cte [, ...]

cte:
    expression_name [(column_name, ...)] AS (
    <base_case> 
    UNION ALL
    <recursive_case>
    ) select_statement;

A non-recursive standard CTE does not reference itself within the statement it is executed in. Non-recursive CTEs are inline view definitions that appear at the start of a query. Also, a non-recursive CTE can be used to simplify a query by isolating subselects out of the main SQL statement.

A recursive CTE references itself. They are useful when working with hierarchical data because the CTE continues to execute until the query returns the desired portion of the hierarchy.

A common example of hierarchical data is an organization chart where employees are listed under their direct managers.

This shows, in a graphical form, that a recursive CTE can retrieve from the database by recursively expanding the result set starting at the top with Maggie. The result set will still be in the form of a table.

A recursive CTE contains two SELECT statements joined by a UNION ALL clause. The first SELECT statement is the base case which is executed once. The second SELECT statement is the recursive case where the CTE references itself, expanding repeatedly until the termination condition is met. The termination condition does not evaluate to TRUE or FALSE. The termination condition is not specific syntax; rather, the RCTE terminates when the recursive case does not return any new rows.

The recursive CTE will use the data types from the columns in the base case if the columns in the recursive case have different data types.

Non-recursive CTEs:

WITH myCTE AS
(select col from myTable)
select col from myCTE;

WITH myCTE (colAlias) AS
(select col from myTable)
select colAlias from myCTE;

WITH orderCTE AS
(select o_orderkey from orders),
lineitemCTE AS
(select l_orderkey from lineitem)
select count(*) from orderCTE join lineitemCTE on o_orderkey = l_orderkey;

WITH foo AS
(WITH bar AS
(SELECT * FROM t) SELECT * FROM bar)
SELECT * FROM foo;

WITH EmpSal(averageSal) AS
(SELECT AVG(Salary) FROM Employee)
SELECT EmpID, Name, Salary FROM Employee, EmpSal 
WHERE Employee.Salary > EmpSal.averageSal;

WITH ObjectCTE (Name, Id, Date) AS
(SELECT objname, objectid, invoicedate FROM Inventory)
INSERT INTO Itemlist(Name, objectid, createdDate)
SELECT Name, Id, Date FROM ObjectCTE; 

SELECT * FROM Itemlist;

Recursive CTEs:

Using the organization chart described above, this example illustrates how recursive CTEs simplify the retrieval of hierarchical data.

CREATE TABLE cte_emp(id INT, mgr_id INT, name VARCHAR(30));
INSERT INTO cte_emp VALUES(1, NULL, "Maggie"), (2, 1, "John"), (3, 1, "Felix"), 
                      (4, 2, "Joan"), (5, 2, "Richard"), (6, 3, "Andy"), (7, 3, "Jill");

WITH RECURSIVE org_chart (emp_id, mgr_id, name, level) AS (         
    SELECT cte_emp.id, cte_emp.mgr_id, cte_emp.name, 0 AS level                  
    FROM cte_emp          
    WHERE cte_emp.mgr_id is NULL               
    UNION ALL     
    SELECT cte_emp.id, cte_emp.mgr_id, cte_emp.name, level+1           
    FROM cte_emp           
    INNER JOIN org_chart                
    ON cte_emp.mgr_id = org_chart.emp_id    
) SELECT * FROM org_chart;
****
+------+--------+---------+-------+
| id   | mgr_id | name    | level |
+------+--------+---------+-------+
|    1 |   NULL | Maggie  |     0 |
|    3 |      1 | Felix   |     1 |
|    2 |      1 | John    |     1 |
|    5 |      2 | Richard |     2 |
|    4 |      2 | Joan    |     2 |
|    7 |      3 | Jill    |     2 |
|    6 |      3 | Andy    |     2 |
+------+--------+---------+-------+

Another example is a graph of connected nodes. A recursive CTE can retrieve the nodes and their relationships to one other. The id column is the node id and pr is the parent node to the node in the id column.

CREATE TABLE cte_g (id INT, pr INT);
INSERT INTO cte_g VALUES (0, null), (1, 0), (2, 0),(3, 1), (4, 1), (5, 2), (6, 2);

WITH RECURSIVE depths AS (  
    SELECT 0 depth, * 
    FROM cte_g 
    WHERE id = 0      
    UNION ALL     
    SELECT depth+1, cte_g.id, cte_g.pr 
    FROM cte_g, depths 
    WHERE cte_g = depths.id
) 
SELECT depth, id FROM depths;
****
+-------+------+
| depth | id   |
+-------+------+
|     0 |    0 |
|     1 |    1 |
|     1 |    2 |
|     2 |    5 |
|     2 |    3 |
|     2 |    4 |
|     2 |    6 |
+-------+------+

The following examples show the recursive CTE will use the data types from the columns in the base case if the columns in the recursive case have different data types.

CREATE TABLE cte_g (id INT, pr INT, SHARD KEY(id));
INSERT INTO cte_g VALUES (0, null), (1, 0), (2, 0),(3, 1), (4, 1), (5, 2), (6, 2);

WITH RECURSIVE depths AS (
    SELECT 0 depth, id, pr
    FROM cte_g    
    WHERE pr IS NULL
    UNION ALL
    SELECT CAST((depth+1) AS DECIMAL(10, 2)), cte_g.id, cte_g.pr
    FROM cte_g, depths
    WHERE cte_g.pr = depths.id
)
SELECT depth, id FROM depths;
****
+-------+------+
| depth | id   |
+-------+------+
|     0 |    0 |
|     1 |    2 |
|     2 |    5 |
|     2 |    4 |
|     1 |    1 |
|     2 |    3 |
|     2 |    6 |
+-------+------+

WITH RECURSIVE routes (id, path) AS (
    SELECT id, cast ( 1 as char(30) )
    FROM cte_g
    WHERE pr IS NULL
    UNION ALL
    SELECT t.id, CONCAT ( routes.path, '-->', t.id )
    FROM cte_g
    JOIN routes
    ON routes.id = cte_g.pr
)
SELECT * FROM routes;
****
+----+-------------------------+
| id |	cast ( 1 as char(30) ) |
+----+-------------------------+
| 1  |	1-->1                  |
| 3  |	1-->1-->3              |
| 0  |	1                      |
| 2  |	1-->2                  | 
| 5  |	1-->2-->5              | 
| 4  |	1-->1-->4              |  
+----+-------------------------+

By using the variable materialize_ctes, common table expressions (CTEs) are materialized when queries with CTEs are run. The materialized results can then be referenced multiple times by the parent query. The CTE queries are cached, which results in optimized performance. This is particularly useful for running queries that contain an expensive CTE that produces a relatively small number of rows and is used two or more times.

The materializing CTEs functionality is enabled by default.

Enable or disable this functionality by setting the materialize_ctes engine variable, or setting it per session via the MATERIALIZE_CTES session variable:

SET MATERIALIZE_CTES = { 'ALL' | 'AUTO' | 'OFF' };

The following settings are available:

If users can't or don't want to use engine variables, the materialize_cte can be set on a per query basis by adding an OPTION expression at the end of the query:

SELECT * FROM foo OPTION(materialize_ctes="OFF");
SELECT * FROM foo OPTION(materialize_ctes="AUTO");
SELECT * FROM foo OPTION(materialize_ctes="ALL");

Materialization can also be turned off per CTE:

WITH foo as (select with(materialize=off) * from titanic), 
     bar as (select * from titanic) 
     select * from foo, bar;

WITH foo AS (SELECT * FROM t)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

WITH foo AS (SELECT * FROM t WHERE t.a = 1 OR t.b = 2)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;