WITH

The WITH clause defines a common table expression (CTE), which is a temporary named result set. The definition of a CTE includes its name, an optional list of column names, and a query expression (that is, a SELECT statement).

For more information about SELECT statements, see SELECT.

Syntax

[ WITH <cte_name> [ ( <cte_column1>, <cte_column2>, ... ) ]
    AS ( <query> ) 
]
SELECT ...

Parameters

[ WITH <cte_name> [ ( <cte_column1>, <cte_column2>, ... ) ] AS ( <query> ) ] String Optional

A temporary named result set for use in the statement that defines the CTE.

<cte_name>: The name of the CTE you are defining. The CTE must have a unique name within a given query.
<cte_column#>: The names of the columns from the query that defines the CTE.
AS <query>: The query (SELECT) statement that defines the CTE.

Examples

Query an existing table using a CTE clause

WITH cte_quantity (Total)
  AS (
      SELECT SUM(passenger_count) as Total
      FROM Samples."samples.dremio.com"."NYC-taxi-trips" where passenger_count > 2
      GROUP BY pickup_datetime
      )
SELECT AVG(Total) average_pass
FROM cte_quantity

Recursive CTE

You can use a recursive CTE (R-CTE) to query hierarchical or graph-structured data, such as organizational charts, category trees, or bills of materials. An R-CTE adds an anchor SELECT, a recursive SELECT that references the CTE, and a UNION ALL between them, so it uses its own syntax.

Syntax

[ WITH [ RECURSIVE ]
    <cte_name> ( <cte_column1>, <cte_column2>, ... )
    AS ( anchor UNION ALL recursiveTerm )
]
SELECT ...

Where anchor and recursiveTerm are both SELECT statements:

Anchor and recursive term structure

-- anchor
SELECT ( <cte_column1>, <cte_column2>, ... ) FROM ...

-- recursiveTerm
SELECT ( <cte_column1>, <cte_column2>, ... ) FROM ... [ JOIN ... ]

Parameters

anchor String Required

The starting point from which the recursive term builds. The anchor runs only once and returns the first part of the query's output. The anchor cannot contain a recursive reference to the CTE.

recursiveTerm String Required

The iterative term in the query. Each iteration consumes the previous iteration's output and can be joined back to it. Iteration continues until the recursive term returns no rows.

Examples

The following examples show common R-CTE patterns.

Walk an organizational hierarchy

WITH RECURSIVE org(empno, ename, level) AS (
  SELECT empno, ename, 0
  FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, o.level + 1
  FROM emp e JOIN org o ON e.mgr = o.empno
)
SELECT level, ename
FROM org ORDER BY level, ename

Output:

level	ename
0	KING
1	BLAKE
1	CLARK
1	JONES
2	ALLEN
2	FORD
2	JAMES
2	MARTIN
2	MILLER
2	SCOTT
2	TURNER
2	WARD
3	ADAMS
3	SMITH

Accumulate a path through a category tree

WITH RECURSIVE paths(id, path) AS (
  SELECT id, CAST(name AS VARCHAR)
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id,
         CAST(p.path || ' > ' || c.name AS VARCHAR)
  FROM categories c
  JOIN paths p ON c.parent_id = p.id
)
SELECT path FROM paths ORDER BY path

Output:

path
Electronics
Electronics > Audio
Electronics > Audio > Headphones
Electronics > Audio > Speakers
Electronics > Video
Electronics > Video > Projectors
Electronics > Video > TVs

Roll up a bill of materials

WITH RECURSIVE bom(component, qty) AS (
  SELECT component, quantity
  FROM parts WHERE parent = 'Bicycle'
  UNION ALL
  SELECT p.component, p.quantity * b.qty
  FROM parts p JOIN bom b ON p.parent = b.component
)
SELECT component, SUM(qty) AS total
FROM bom
WHERE component NOT IN (SELECT parent FROM parts)
GROUP BY component ORDER BY total

Output:

component	total
Seat	1
Stem	1
Rim	2
Tire	2
Grip	2
Axle	2
Tube	3
Bearing	4
Joint	4
Spoke	64

Traverse a VARIANT column with an R-CTE

WITH RECURSIVE emp_tree(node, depth) AS (
  SELECT org_data, 0
  FROM employees
  UNION ALL
  SELECT FLATTEN(VARIANT_GET(e.node, '$.reports' AS ARRAY(VARIANT))),
         e.depth + 1
  FROM emp_tree e
  WHERE VARIANT_GET(e.node, '$.reports' AS ARRAY(VARIANT)) IS NOT NULL
)
SELECT VARIANT_GET(node, '$.id' AS INT) AS id,
       VARIANT_GET(node, '$.name' AS VARCHAR) AS name,
       VARIANT_GET(node, '$.title' AS VARCHAR) AS title,
       depth
FROM emp_tree
ORDER BY depth, id;

Given the following VARIANT value in org_data:

Example org_data VARIANT value

{
  "id": 1, "name": "Alice", "title": "CEO",
  "reports": [
    {"id": 2, "name": "Bob", "title": "VP Eng",
     "reports": [
       {"id": 4, "name": "David", "title": "Eng Mgr"},
       {"id": 5, "name": "Eve",   "title": "Dev Mgr"}
     ]},
    {"id": 3, "name": "Carol", "title": "VP Sales",
     "reports": [
       {"id": 6, "name": "Frank", "title": "Sales Mgr"}
     ]}
  ]
}

Output:

id	name	title	depth
1	Alice	CEO	0
2	Bob	VP Eng	1
3	Carol	VP Sales	1
4	David	Eng Mgr	2
5	Eve	Dev Mgr	2
6	Frank	Sales Mgr	2

Limitations

The default maximum number of recursions is 1000. When a query exceeds this limit, it fails with an error rather than returning partial results. To raise the limit, set the session option planner.recursive_cte_max_iterations.
RIGHT, CROSS, and FULL OUTER joins in the recursive term are not supported.
Only UNION ALL is supported. UNION DISTINCT (cycle detection) is not supported.
Aggregation (GROUP BY, DISTINCT) inside the recursive term is not supported.
Window functions inside the recursive term are not supported.
The SEARCH and CYCLE clauses are not supported.
Nested R-CTEs in the recursive term are not supported.
Mutual or indirect recursion (two R-CTEs that reference each other) is not supported. This surfaces as a SQL name-resolution error rather than an R-CTE-specific message.
Subqueries inside the recursive term that reference the recursive CTE itself are not supported. Other correlated subqueries against non-recursive sources are allowed.
The recursive term cannot reference the recursive CTE more than once. For example, self-joining the recursive name in the recursive term is not supported.

Performance Considerations

Referencing the same recursive CTE multiple times in the outer query causes the recursion to execute once per reference. The planner duplicates the recursive subtree per use, so a two-reference join runs the recursion twice during planning.

If the recursion is expensive and the outer query references the R-CTE multiple times, persist the intermediate result before reading it again. You have two options:

Use CREATE TABLE AS (CTAS) to persist the rows to a table.
Define a view backed by a Reflection. A plain view does not materialize on its own because Dremio inlines the view query during planning.

The following example references the R-CTE twice, which triggers the recursion twice during planning:

R-CTE referenced multiple times in the outer query

WITH RECURSIVE org(empno, ename, level) AS (
  SELECT empno, ename, 0
  FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, o.level + 1
  FROM emp e JOIN org o ON e.mgr = o.empno
)
-- Pairs of employees at the same org level
SELECT a.ename AS employee, b.ename AS peer, a.level
FROM org AS a
JOIN org AS b
  ON a.level = b.level AND a.empno < b.empno

To avoid the duplicated work, materialize the recursion into a table once, then read from the table:

Materialize the R-CTE with CTAS, then reuse the result

CREATE TABLE my_catalog.my_schema.org_levels AS
WITH RECURSIVE org(empno, ename, level) AS (
  SELECT empno, ename, 0
  FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, o.level + 1
  FROM emp e JOIN org o ON e.mgr = o.empno
)
SELECT empno, ename, level FROM org;

SELECT a.ename AS employee, b.ename AS peer, a.level
FROM my_catalog.my_schema.org_levels AS a
JOIN my_catalog.my_schema.org_levels AS b
  ON a.level = b.level AND a.empno < b.empno

The recursion runs once during the CTAS. Subsequent queries scan the materialized table.

Parameters​

Examples​

Recursive CTE​

Parameters​

Examples​

Limitations​

Performance Considerations​

Parameters

Examples

Recursive CTE

Parameters

Examples

Limitations

Performance Considerations