CTE — WITH Clause

A CTE (Common Table Expression) is a named temporary result set defined before the main query using the WITH keyword. It creates a named intermediate result that can be referenced anywhere in the subsequent query — in the main SELECT, in subsequent CTEs, in JOIN conditions, in WHERE clauses, and in subqueries within the main query. A subquery or derived table is defined inline — inside the WHERE, FROM, or another clause — and exists only at that single location in the query.

The key practical differences: CTEs can be referenced multiple times in the same query; a derived table can only be used once at its inline position. CTEs appear before the main query and read top-to-bottom in logical order; derived tables are read inside-out which degrades readability for complex logic. CTEs give each step a descriptive name that documents what it computes; derived tables have aliases but the computation is interleaved with the outer query's structure. CTEs support recursive queries (WITH RECURSIVE) for hierarchical data; derived tables cannot be recursive.

Computationally, CTEs and derived tables produce identical results — both define a temporary result set the outer query uses as a virtual table. The choice is primarily about readability and reuse. Use a derived table for a simple, short, single-use pre-aggregation where the inline position makes context clear. Use a CTE for anything complex, multi-step, reused more than once, or deserving of a descriptive name that documents its purpose.

Multiple CTEs are chained with commas — each CTE definition is separated from the next by a comma. The WITH keyword appears only once at the very beginning: WITH first_cte AS (...), second_cte AS (...), third_cte AS (...) SELECT ... The chain ends with the main query — no comma before the final SELECT.

The dependency rule: a CTE can reference any CTE that was defined before it in the chain. second_cte can reference first_cte. third_cte can reference both first_cte and second_cte. But first_cte cannot reference second_cte — references must go forward only (except in recursive CTEs, which have their own rules). The main SELECT at the end can reference any CTE in the chain.

The naming scope rule: CTE names are scoped to the entire WITH block. A CTE name defined in the WITH block shadows any base table with the same name for the duration of the query — be careful not to accidentally reuse a base table name as a CTE alias. Column names within each CTE are independent — two CTEs can have columns with the same name without conflict, as long as the outer query references them with the correct CTE prefix. The practical pattern: define CTEs in the order of their dependencies — base computations first, enrichments next, final transformations last. The main SELECT reads the final result from the last CTE or assembles from multiple CTEs.

The "top N per group" query (find the top 2 orders per store, or the highest-paid employee per department) is typically solved with a window function (ROW_NUMBER or RANK) inside a CTE or derived table, followed by filtering on the rank in the outer query. The CTE and window function work together — the CTE enables filtering on the window function result.

A CTE is needed because window functions cannot be referenced in WHERE of the same query where they are defined — WHERE runs before SELECT in the logical execution order. The pattern: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_amount DESC) AS rn FROM orders WHERE order_status = 'Delivered') SELECT * FROM ranked WHERE rn <= 2. The CTE computes the rank, the outer query filters by it.

A correlated subquery alternative exists (WHERE (SELECT COUNT(*) FROM orders o2 WHERE o2.store_id = o.store_id AND o2.total_amount > o.total_amount) < 2) but is generally slower for large tables — it runs once per outer row. The CTE + window function approach makes a single pass through the data with the window function, then one filtering pass — O(n log n) for the sort inside the window function, O(n) for the filter. For large tables, the CTE + window function approach is significantly faster than the correlated subquery alternative.

In PostgreSQL 12 and later, CTEs default to NOT MATERIALIZED behaviour — the optimiser treats the CTE as an inline view, exactly like a derived table. It can push predicates from the outer query into the CTE, reorder joins involving the CTE, and optimise the CTE's execution as part of the overall query plan. This is equivalent to writing the CTE as a derived table inline in the FROM clause.

MATERIALIZED forces the CTE to be computed once, stored in memory, and reused for all references. The CTE becomes a fence for optimisation — predicates from the outer query cannot be pushed into it. The result is computed in full and cached. This is the PostgreSQL behaviour before version 12 (and is still the default in some other databases).

When to use each: force MATERIALIZED when the CTE is referenced multiple times in the query and the computation is expensive — materialising once prevents the expensive computation from being repeated once per reference. Use MATERIALIZED when you want the CTE to be an optimisation boundary — for example, when you want to ensure the CTE computes its result on the full data before outer-query filters are applied (important for correctness when the CTE has side effects or when the optimiser is making bad decisions). Force NOT MATERIALIZED when the CTE has a highly selective outer query filter that the optimiser should be able to push in — for example, when the outer query filters by customer_id = 5 and you want the CTE to only scan orders for customer 5 rather than all customers. In practice, most CTEs work well with the default (NOT MATERIALIZED / inlined) behaviour — only reach for these hints when EXPLAIN ANALYZE shows the optimiser making a suboptimal choice.

In PostgreSQL, CTEs can contain DML statements (INSERT, UPDATE, DELETE) using the RETURNING clause to capture the affected rows. This enables atomic multi-step operations — the canonical example being archive-then-delete: delete rows from one table and simultaneously insert them into another, all in one transaction.

The pattern: WITH deleted_rows AS (DELETE FROM source_table WHERE condition RETURNING *) INSERT INTO archive_table SELECT *, NOW() AS archived_at FROM deleted_rows. The DELETE CTE runs first, capturing all deleted rows via RETURNING *. The main INSERT then uses those captured rows to populate the archive table. Because the entire statement executes in a single transaction, either both the DELETE and INSERT succeed together or neither does — there is no risk of deleting without archiving.

The critical constraint: DML CTEs can only appear in PostgreSQL (and SQL Server with different syntax) — MySQL does not support DML in CTEs. Also, a DML CTE always executes even if the main query references it zero times — it has side effects by definition. This is different from SELECT CTEs which are only computed when referenced. The archive-then-delete pattern is the safest approach for data retention cleanup jobs: wrap the statement in an explicit transaction for additional protection, log the operation to an audit table, and verify the RETURNING clause captures the expected rows before deploying to production.

Cause: CTE syntax is not supported in the database version being used, or the WITH keyword is in the wrong position. MySQL added CTE support in version 8.0 — versions before MySQL 8.0 do not support WITH. Also, if WITH appears inside a subquery in some database configurations, it may not be recognised (standard SQL requires WITH at the outermost level, though PostgreSQL allows it in subqueries).

Fix: Check MySQL version: SELECT VERSION() — if below 8.0, upgrade or rewrite using derived tables (inline subqueries in FROM). For subquery-level CTEs, move the WITH to the outermost query level and use derived tables or additional CTEs for the inner logic. PostgreSQL 8.4+ and SQL Server 2005+ fully support CTEs. DuckDB (this playground) fully supports CTEs.

Cause: A CTE references another CTE that appears later in the WITH chain. CTEs can only reference CTEs defined before them — the reference order is forward-only. Or the CTE name is misspelled in the reference, causing the database to look for a base table with that name instead.

Fix: Reorder CTEs so dependencies come first: if second_cte references first_cte, first_cte must appear before second_cte in the WITH chain. Check spelling — CTE names are case-sensitive in some databases. If two CTEs need to reference each other (circular dependency), this is not possible with standard CTEs — restructure the logic to break the cycle, or use a recursive CTE if the dependency is hierarchical.

Cause: In PostgreSQL 12+ with NOT MATERIALIZED (default), the CTE is treated as an inline view — if the optimiser determines the CTE should be computed separately for each reference point, it may recompute it multiple times. This is unusual but can happen with complex query shapes or when the optimiser's cost estimates are off.

Fix: Force MATERIALIZED to ensure the CTE is computed once: WITH my_cte AS MATERIALIZED (SELECT ...). This guarantees the CTE is computed exactly once regardless of how many times it is referenced. Use EXPLAIN ANALYZE to verify execution — look for the CTE node in the plan. If the CTE appears multiple times in the plan without MATERIALIZED, adding it will prevent the recomputation. Only add MATERIALIZED when EXPLAIN ANALYZE confirms the problem exists — unnecessary materialisation can reduce performance by preventing predicate pushdown.

Cause: The outer query references a column name that was not included in the CTE's SELECT list, or the column was computed inside the CTE but not given an alias that matches the outer query's reference. CTE columns are only accessible through the names given in the CTE's SELECT list — any expression must be aliased for the outer query to reference it.

Fix: Add the missing column to the CTE's SELECT list: WITH cte AS (SELECT col1, col2, computed_expression AS alias_name FROM ...). The outer query then references it as cte.alias_name. If the CTE already includes the column but the reference fails, check for typos and case sensitivity. Remember that CTE column names come from the CTE's SELECT list — if you alias a column differently in the CTE than how you reference it in the outer query, the reference will fail.

Cause: DML statements in CTEs always execute unconditionally — they are not lazy. Unlike SELECT CTEs which are only computed when referenced, a DELETE or UPDATE CTE runs as soon as the statement begins execution, even if the main query has a WHERE condition that would produce zero rows. This can cause unexpected data modifications.

Fix: Always preview DML CTEs before running: temporarily replace DELETE FROM ... WITH ... RETURNING * with SELECT * FROM table WHERE condition to verify which rows would be affected. Wrap DML CTEs in explicit transactions and verify the result before COMMIT: BEGIN; WITH deleted AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM deleted; SELECT COUNT(*) FROM archive WHERE archived_at >= NOW() - INTERVAL '5 seconds'; COMMIT. This lets you verify the operation's effect before making it permanent.

Write a query using multiple CTEs that produces a complete store health report. The report should include for each store: store_id, city, monthly_target, delivered_revenue, delivered_order_count, avg_order_value (rounded to 2 decimal places), employee_count (0 if none), total_payroll (0 if none), revenue_per_employee (delivered_revenue / employee_count, NULL if no employees), target_achievement_pct (delivered_revenue / monthly_target × 100, rounded to 1 decimal), and a health_status: 'Excellent' if target >= 120%, 'On Track' if target >= 80%, 'Needs Attention' if target >= 50%, 'Critical' otherwise. Use a separate CTE for each dimension (order metrics, employee metrics) and a third CTE for the combined store data before the final SELECT adds the health status. Include all stores even those with no orders or employees.