Named temporary result sets that exist for the scope of a single query.
WITH [RECURSIVE] <name> [(<columns>)] AS [
[NOT] MATERIALIZED
] (
<query>
)
[, <name2> AS (...)]
<main_query>## Overview Common Table Expressions (CTEs) define named temporary result sets using the WITH clause. Each CTE is visible only within the scope of the statement that contains it. CTEs improve readability by breaking complex queries into logical steps, and they enable recursive queries that reference their own output to traverse hierarchical or graph-structured data. DeltaForge supports both non-recursive and recursive CTEs, along with MATERIALIZED and NOT MATERIALIZED hints that control whether the optimizer inlines the CTE or computes it as a separate intermediate result. ## Execution Order CTEs are logically evaluated before the main query. Multiple CTEs in a single WITH block are evaluated in declaration order; a later CTE may reference an earlier one. Recursive CTEs execute iteratively: the anchor member runs first, then the recursive member repeatedly references the growing result until no new rows are produced. ## Behavior - **Inline vs. materialized**: By default, the optimizer decides whether to inline a CTE (substituting its definition directly into each reference) or materialize it (computing the result once and reading it from memory for each reference). The MATERIALIZED keyword forces materialization; NOT MATERIALIZED forces inlining. - **Predicate pushdown**: When a CTE is inlined, predicates from the outer query can be pushed into the CTE definition, enabling partition pruning and file-level data skipping on the underlying tables. Materialized CTEs block predicate pushdown because the result is computed independently. - **Recursive CTEs**: The RECURSIVE keyword enables self-referencing queries. The anchor member (the non-recursive part before UNION ALL) seeds the result. The recursive member executes repeatedly, reading the new rows from the previous iteration, until it returns an empty set. DeltaForge enforces a configurable iteration limit to prevent infinite loops. - **Column aliasing**: An optional column list after the CTE name renames the output columns. This is required when the CTE body produces expressions without natural names. - **Multiple references**: A CTE defined once can be referenced multiple times in the main query. When the optimizer materializes the CTE, this avoids redundant computation. When it inlines the CTE, each reference becomes an independent subquery. - **NULL handling**: CTEs do not alter NULL semantics. NULLs flow through CTEs exactly as they would through equivalent subqueries. ## Compatibility CTEs conform to the SQL:1999 standard (WITH clause) and the SQL:2003 standard (RECURSIVE). The MATERIALIZED and NOT MATERIALIZED hints follow PostgreSQL syntax. DeltaForge supports all standard CTE features including multiple CTEs, inter-CTE references, and recursive CTEs with UNION ALL.
-- Basic CTE to simplify a filtered query
WITH active_users AS (
SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users WHERE region = 'US';-- Recursive CTE: traverse a hierarchy
WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 1 AS depth
FROM nodes
WHERE parent_id IS NULL
UNION ALL
SELECT n.id, n.parent_id, n.name, t.depth + 1
FROM nodes n
JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;-- Multiple CTEs referenced in the main query
WITH
monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
),
monthly_costs AS (
SELECT DATE_TRUNC('month', cost_date) AS month, SUM(cost) AS total_cost
FROM expenses
GROUP BY 1
)
SELECT
s.month,
s.revenue,
c.total_cost,
s.revenue - c.total_cost AS profit
FROM monthly_sales s
LEFT JOIN monthly_costs c ON s.month = c.month
ORDER BY s.month;-- CTE used as a subquery source in a join
WITH high_value_customers AS (
SELECT customer_id, SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 50000
)
SELECT c.name, hvc.lifetime_value
FROM customers c
INNER JOIN high_value_customers hvc ON c.id = hvc.customer_id
ORDER BY hvc.lifetime_value DESC;-- MATERIALIZED hint to force the CTE to be computed once
WITH expensive_calc AS MATERIALIZED (
SELECT product_id, AVG(unit_price) AS avg_price
FROM order_items
GROUP BY product_id
)
SELECT p.name, ec.avg_price
FROM products p
INNER JOIN expensive_calc ec ON p.id = ec.product_id
WHERE ec.avg_price > 100;-- NOT MATERIALIZED hint: inline the CTE for predicate pushdown
WITH recent_orders AS NOT MATERIALIZED (
SELECT * FROM orders WHERE order_date >= '2025-01-01'
)
SELECT * FROM recent_orders WHERE region = 'APAC';