SQL CTEs (WITH Clause) – Readable, Reusable Subqueries

What Is a CTE?

A CTE is defined with the WITH keyword before the main SELECT. It creates a named, temporary result set that exists only for the duration of the query. Think of it as giving a subquery a name so you can reference it like a table.

ℹ️

CTE vs Temporary Table vs View

A CTE lives only for the current query. A temporary table persists for the session. A view is stored in the database permanently. CTEs are ideal for breaking a complex query into readable steps without creating persistent objects.

CTE Syntax

SQL

WITH cte_name AS (
    -- This is the CTE body: any valid SELECT
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
)
-- Main query: references the CTE like a table
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN cte_name d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal
ORDER BY e.salary DESC;

Compared to an equivalent derived table, the CTE version is much more readable:

SQL

-- Same query as derived table (harder to read)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees GROUP BY dept_id
) d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

Multiple CTEs

Chain multiple CTEs with commas. Later CTEs can reference earlier ones:

SQL

WITH
-- Step 1: compute department salary stats
dept_stats AS (
    SELECT
        department,
        AVG(salary)  AS avg_sal,
        MAX(salary)  AS max_sal,
        COUNT(*)     AS headcount
    FROM employees
    GROUP BY department
),
-- Step 2: find high-value departments (large headcount AND high average)
high_value_depts AS (
    SELECT department
    FROM dept_stats
    WHERE headcount >= 5
      AND avg_sal > 75000
)
-- Main query: employees in high-value departments
SELECT e.name, e.department, e.salary
FROM employees e
JOIN high_value_depts hvd ON e.department = hvd.department
ORDER BY e.department, e.salary DESC;

Recursive CTEs

A recursive CTE references itself to traverse hierarchical data. It has two parts separated by UNION ALL: an anchor member (base case) and a recursive member:

SQL

-- Traverse an org chart: find all reports under manager id = 1
WITH RECURSIVE org_tree AS (
    -- Anchor: start with the root manager
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 1

    UNION ALL

    -- Recursive member: find direct reports
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, manager_id, level
FROM org_tree
ORDER BY level, name;

⚠️

Recursive CTEs need a termination condition

The recursion stops when the recursive member returns zero rows. If your data has cycles (e.g., manager A reports to B who reports to A), the query will loop forever. Add a depth limit: WHERE ot.level < 10 in the recursive member, or use cycle detection features if your database supports them (PostgreSQL 14+ has CYCLE clause).

Generate a series of numbers (useful for date ranges or test data):

SQL

-- Generate numbers 1 through 10
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

-- Generate a date range (last 30 days)
WITH RECURSIVE date_range AS (
    SELECT CURRENT_DATE - INTERVAL '29 days' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day' FROM date_range WHERE dt < CURRENT_DATE
)
SELECT dt FROM date_range ORDER BY dt;

CTE vs Subquery

Feature	CTE (WITH)	Subquery (inline)
Readability	High — named, top-level	Lower — buried inline
Reusability in same query	Yes — reference by name multiple times	No — must repeat
Recursive?	Yes (WITH RECURSIVE)	No
Materialized?	Database-dependent	Usually not
Scope	Single query only	Single query only

Summary

📋 Summary

A CTE names a subquery using WITH name AS (...) before the main SELECT.
CTEs make complex queries readable by breaking them into named steps.
Multiple CTEs are separated by commas; later CTEs can reference earlier ones.
Recursive CTEs (WITH RECURSIVE) traverse hierarchies and trees using anchor + recursive member separated by UNION ALL.
CTEs can be referenced multiple times in the main query — no need to repeat the subquery.
CTEs exist only for the duration of the query — they do not create permanent objects.

FAQ

Is WITH RECURSIVE supported in all databases? +

PostgreSQL, SQL Server (just WITH, not WITH RECURSIVE), Oracle 11g+ (WITH ... CYCLE), MySQL 8.0+, and SQLite 3.35+ all support recursive CTEs. In SQL Server, you write WITH cte AS (...) — the RECURSIVE keyword is not used; recursion is implicit when the CTE references itself. MySQL 5.x and older SQLite do not support recursive CTEs.

Can I use a CTE in an INSERT, UPDATE, or DELETE statement? +

Yes. CTEs can precede any DML statement: WITH cte AS (...) INSERT INTO ... SELECT FROM cte, or WITH cte AS (...) DELETE FROM ... WHERE id IN (SELECT id FROM cte). This is particularly useful for complex conditional updates or bulk inserts based on derived logic.

Are CTEs always materialised (computed once)? +

Not necessarily. The SQL standard leaves this to the database. PostgreSQL materialises CTEs by default (computes once, caches result) but you can override with WITH cte AS NOT MATERIALIZED (...) to let the optimiser inline it. MySQL and SQL Server typically inline CTEs like derived tables. The behaviour affects performance when the CTE is referenced multiple times.