Skip to main content

WITH (Common Table Expressions)

Specifies a temporary named result set, referred to as a Common Table Expression (CTE).


Non-recursive (standard) CTE:

WITH cte [, ...]

    expression_name[(column_name, ...)]

Recursive CTE:

WITH RECURSIVE cte [, ...]

    expression_name [(column_name, ...)] AS (
    ) select_statement;


  • WITH clauses are supported as part of SELECT, INSERT, UPDATE, DELETE, and REPLACE statements for non-recursive CTEs.

  • WITH RECURSIVE clauses defining recursive CTEs are only supported for SELECT statements for both the base case and the recursive case.

  • Non-recursive CTEs containing UNION ALL clauses are not materialized.

  • By default, SingleStoreDB attempts to materialize CTEs. See Materializing Common Table Expressions for more information.

  • If a CTE defined in the WITH clause is not referenced in the SELECT statement, it does not affect the execution of the query.

  • The engine variable internal_max_cte_depth defines the maximum number of common table expressions (CTEs) that can be nested. The default value of this variable is 128.

  • The engine variable max_recursive_cte_iterations sets the maximum number of times the recursive case is executed.

Non-recursive vs recursive CTEs

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.

Graphic of an organization chart demonstrating hierarchical relationships.

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.


There are several limitations on which operations are allowed in the SELECT statements for the recursive case of recursive CTEs. Please note, this list is subject to change upon future releases. These operations are not allowed in the SELECT in the recursive case:










  • Aggregate functions like COUNT, MAX, etc.

  • Window functions like RANK, DENSE RANK, etc.

Troubleshooting timed-out recursive CTEs

If the recursive CTE takes an inordinate amount of time to execute or times out, there can be an issue with the data’s structure or with the creation of the CTE itself. These issues prevent the recursive case of the CTE from terminating.

Your data structure may not be truly hierarchical. Using the organization chart example, if Maggie also reported to John, then the data structure contains a cycle. If your data contains cycles, either update the data to remove cycles, or use some other method to make the expansion terminate, like explicitly checking expansion depth.

Your recursive CTE query can be incorrectly coded and create an infinite loop. An infinite loop can occur when the SELECT clause in the recursive case outputs a value from the base case instead of the current iteration. This causes incorrect values to be used in the subsequent iteration’s JOIN operation.

Both the query itself and the data must allow the recursive case to terminate.

Known issue with recursive CTEs

If a recursive CTE results in the error "'Table'.'doesn't exist'", the materialize_ctes setting may be disabled. This usually happens when upgrading from a pre-7.8 version where the materialize_ctes setting was not enabled by default. See the Materializing Common Table Expressions page for information on how to enable the functionality.


Non-recursive CTEs:

(select col from myTable)
select col from myCTE;
WITH myCTE (colAlias) AS
(select col from myTable)
select colAlias from myCTE;
(select o_orderkey from orders),
lineitemCTE AS
(select l_orderkey from lineitem)
select count(*) from orderCTE join lineitemCTE on o_orderkey = l_orderkey;
(WITH bar AS
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.mgr_id,, 0 AS level                  
    FROM cte_emp          
    WHERE cte_emp.mgr_id is NULL               
    UNION ALL     
    SELECT, cte_emp.mgr_id,, 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 |
7 rows in set (0.58 sec)

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);
    SELECT 0 depth, * FROM cte_g WHERE id = 0      
    UNION ALL     
    SELECT depth+1,, FROM depths 
    JOIN cte_g ON =
) SELECT depth, id FROM depths;
| depth | id   |
|     0 |    0 |
|     1 |    1 |
|     1 |    2 |
|     2 |    5 |
|     2 |    3 |
|     2 |    4 |
|     2 |    6 |
7 rows in set (0.55 sec)