CTE Referenced 3 Times — 50M Rows Scanned in SQL Server

Every production database eventually grows a query so complex it becomes a maintenance nightmare — subqueries nested inside subqueries, the same filter logic copy-pasted five times, and a WHERE clause that scrolls off the screen. CTEs were introduced precisely because real SQL work at scale demands readable, composable, and debuggable queries.

The core problem CTEs solve is scope and readability without sacrificing power. Before CTEs, your only option was derived tables (anonymous subqueries in FROM clauses) or views (which require DDL permissions and pollute your schema). CTEs give you a named, scoped, temporary result set that lives only for the duration of your query.

By the end of this article you'll write multi-step CTEs that replace tangled subquery nests, use recursive CTEs to walk org charts, understand exactly when your database materializes a CTE versus re-evaluates it, and avoid the three production gotchas that catch even experienced engineers off guard.

What a CTE Actually Is (and Isn't)

A Common Table Expression (CTE) is a named, temporary result set defined within a single SQL statement's execution scope. It is not a temp table, not a view, and not materialized by default — it's a syntactic construct that the query optimizer can rewrite, inline, or execute multiple times. In SQL Server, a CTE referenced three times in the same query can cause the underlying table to be scanned three separate times, leading to 50M rows scanned where 16M would suffice.

A CTE is defined with the WITH clause and exists only for the duration of the query that follows it. Crucially, SQL Server does not automatically cache or spool the CTE's result set. Each reference to the CTE name is expanded independently by the optimizer, meaning the same complex join or aggregation inside the CTE definition may be executed once per reference. This is the root cause of the "CTE multiplied scans" antipattern.

Use CTEs for readability and recursive queries, but never assume they optimize like temp tables. When the same CTE is referenced multiple times — especially against large tables — explicitly materialize it into a temp table or table variable. A single #temp table with a clustered index can collapse three full scans into one, cutting logical reads from 150,000 to 50,000.

The Anatomy of a CTE — Structure, Scope and Why It Beats Subqueries

A CTE is defined with the WITH keyword, followed by a name, an optional column list, and the query body in parentheses. Everything after that closing parenthesis is the outer query. The CTE only exists for the lifetime of that single outer query execution.

You can define multiple CTEs in one WITH clause by separating them with commas. Crucially, a later CTE can reference an earlier one — this is what makes chaining possible. Think of it as a pipeline: each CTE refines the data one step further before handing it to the next stage.

Why does this beat a subquery? Readability is only part of the answer. The bigger win is debuggability. When you have a three-level nested subquery and something goes wrong, you can't easily isolate which level is producing bad data. With CTEs, you SELECT directly from each named stage during development — comment out the outer query and inspect intermediate results. That feedback loop is invaluable on complex data pipelines.

Also note: a CTE referenced multiple times in the outer query does NOT automatically get computed once and cached. Whether it's materialized or re-evaluated depends on your database engine — a critical performance detail covered in Section 3.

-- Find customers who placed >3 orders in 2023 AND spent >$500
-- then rank them by total spend

WITH orders_2023 AS (
    SELECT customer_id, order_id, order_total
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2023
      AND order_status = 'completed'
),
customer_summary AS (
    SELECT customer_id,
           COUNT(order_id)   AS total_orders,
           SUM(order_total)  AS total_spend
    FROM orders_2023
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT customer_id, total_orders, total_spend
    FROM customer_summary
    WHERE total_orders > 3 AND total_spend > 500.00
)
SELECT c.full_name, c.email,
       hvc.total_orders, hvc.total_spend,
       RANK() OVER (ORDER BY hvc.total_spend DESC) AS spend_rank
FROM high_value_customers hvc
JOIN customers c ON c.customer_id = hvc.customer_id
ORDER BY spend_rank;

CTE Scope — Why You Can't Reference a CTE in Another Query

A CTE (common table expression) is defined within a single SQL statement. Its name only exists for the duration of that statement — from the WITH keyword to the final semicolon. Once the query finishes executing, the CTE disappears. This limited scope is by design: it allows the database to manage temporary names without creating any persistent objects.

Why can't you reference a CTE in a separate query? Because the database engine does not store the CTE's result anywhere persistent. It is part of the query plan that exists only during execution. If you try to use the CTE name in a different batch or even in a separate statement within the same script, you'll get an error like "invalid object name" or "relation does not exist." This is fundamentally different from a view or a temporary table, which both have a longer lifespan.

Consider two separate queries in a script: the first defines and uses a CTE; the second tries to reference that same CTE name. The second query fails immediately because the CTE was not defined in its own WITH clause. This is the most common "gotcha" for engineers new to CTEs — they expect the name to carry over like a variable in a programming language.

The fix is simple: if you need to reuse the same logic across multiple queries, create a view or a temporary table instead. CTEs are scoped to a single query for a reason — they encourage composability within that query without creating side effects that other queries might accidentally depend on.

Recursive CTEs — Walking Trees, Org Charts and Hierarchical Data

A recursive CTE has two parts separated by UNION ALL: the anchor member (the base case — where the recursion starts) and the recursive member (which references the CTE itself to walk one level deeper). The engine executes the anchor once, then keeps executing the recursive member against the previous iteration's results until it returns zero rows.

The classic use case is an organizational hierarchy stored as an adjacency list — each employee row has a manager_id pointing to another employee_id. Without a recursive CTE, flattening that requires N queries for N levels. With recursive CTEs, one query handles any depth.

Cycles in data are the most dangerous edge case. If employee A is the manager of B and B is the manager of A (data corruption happens), a naive recursive CTE loops until it hits the depth limit. Always track visited IDs and break when you detect a repeat.

-- Walk org chart from a given manager downward
-- Handles cycles by tracking visited IDs

WITH RECURSIVE org_hierarchy AS (
    -- ANCHOR: start at target employee (VP of Engineering)
    SELECT employee_id, full_name, manager_id, salary,
           0                    AS depth,
           ARRAY[employee_id]   AS visited_ids,
           full_name::TEXT      AS reporting_path
    FROM employees
    WHERE employee_id = 42

    UNION ALL

    -- RECURSIVE: find everyone reporting to previous level
    SELECT e.employee_id, e.full_name, e.manager_id, e.salary,
           oh.depth + 1,
           oh.visited_ids || e.employee_id,
           oh.reporting_path || ' -> ' || e.full_name
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
    -- CYCLE GUARD: stop if we've seen this employee before
    WHERE NOT (e.employee_id = ANY(oh.visited_ids))
)
SELECT depth,
       LPAD('', depth * 4, ' ') || full_name AS org_chart,
       salary, reporting_path
FROM org_hierarchy
ORDER BY depth, full_name;

Recursive CTE for Graph Traversal — Shortest Path in a Directed Graph

Recursive CTEs are not limited to tree hierarchies; they can also traverse arbitrary directed graphs. A common production need is finding the shortest path between two nodes in a graph stored as an adjacency list (edges table). The challenge is that a graph can have multiple paths to the same node, and we want the shortest (fewest edges). A recursive CTE with breadth-first exploration naturally finds the shortest path because it visits nodes in order of increasing depth — the first time a target node is reached gives the minimal edge count.

To make this work efficiently: track visited nodes to avoid infinite loops (cycles) and store the path taken so far. Use an array to accumulate visited node IDs, and break when the target is found. Because the recursive CTE processes each level before moving to the next (as long as the database uses a BFS strategy, which PostgreSQL and SQL Server do for recursive CTEs), the first appearance of the target node will be the shortest path.

Performance note: on large graphs, recursive CTEs can be slow because they are not indexed for graph traversal. Use a LIMIT clause to stop early once the target is found, and add a depth bound to prevent runaway queries.

-- Find shortest path from node A to node C in a directed graph
-- edges table: from_node, to_node

WITH RECURSIVE path_finder AS (
    -- ANCHOR: start at source node A
    SELECT
        'A'::TEXT AS current_node,
        ARRAY['A']::TEXT[] AS path,
        0 AS depth

    UNION ALL

    -- RECURSIVE: explore neighbors not yet visited
    SELECT
        e.to_node,
        pf.path || e.to_node,
        pf.depth + 1
    FROM path_finder pf
    JOIN edges e ON e.from_node = pf.current_node
    WHERE NOT e.to_node = ANY(pf.path)   -- avoid cycles
      AND pf.depth < 10                  -- safety limit
)
SELECT path, depth
FROM path_finder
WHERE current_node = 'C'
ORDER BY depth
LIMIT 1;

CTE Materialization vs. Re-evaluation — The Performance Truth Nobody Tells You

The SQL standard says nothing about whether a CTE must be materialized (computed once, stored) or re-evaluated (re-run every time it's referenced). Each database engine makes its own choice.

PostgreSQL prior to version 12 always materialized CTEs as optimization fences — the planner could not push WHERE predicates from the outer query into the CTE. PostgreSQL 12+ inlines non-recursive CTEs by default, allowing predicate pushdown. You can force materialization with the MATERIALIZED keyword.

SQL Server never guaranteed materialization — it treats CTEs as macro expansions. If you reference a CTE three times in a UNION ALL, SQL Server may execute that query three times. For expensive CTEs referenced repeatedly, load results into a #temp_table first.

MySQL 8.0+ inlines CTEs. Oracle materializes when it determines it's beneficial and accepts the WITH MATERIALIZE hint to force it.

-- PostgreSQL: force materialization to prevent volatile function re-execution
WITH MATERIALIZED daily_rates AS (
    SELECT currency_code,
           get_live_rate(currency_code) AS usd_rate  -- expensive external call
    FROM supported_currencies
    WHERE is_active = true
)
SELECT o.order_id, o.order_total * dr.usd_rate AS total_usd
FROM orders o
JOIN daily_rates dr ON dr.currency_code = o.currency_code
WHERE o.order_status = 'pending';

-- SQL Server ANTI-PATTERN: CTE referenced 3x may scan the table 3x
WITH region_totals AS (
    SELECT region_id, SUM(revenue) AS total_revenue
    FROM sales_transactions
    WHERE transaction_date >= '2024-01-01'
    GROUP BY region_id
)
SELECT 'Top' AS tier, * FROM region_totals WHERE total_revenue > 1000000
UNION ALL
SELECT 'Mid' AS tier, * FROM region_totals WHERE total_revenue BETWEEN 250000 AND 1000000
UNION ALL
SELECT 'Low' AS tier, * FROM region_totals WHERE total_revenue < 250000;
-- ^ May scan sales_transactions THREE TIMES

-- SQL Server CORRECT PATTERN: materialize into temp table
SELECT region_id, SUM(revenue) AS total_revenue
INTO #region_totals
FROM sales_transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY region_id;

SELECT 'Top' AS tier, * FROM #region_totals WHERE total_revenue > 1000000
UNION ALL
SELECT 'Mid' AS tier, * FROM #region_totals WHERE total_revenue BETWEEN 250000 AND 1000000
UNION ALL
SELECT 'Low' AS tier, * FROM #region_totals WHERE total_revenue < 250000;

DROP TABLE #region_totals;

Production CTE Patterns — Window Functions, Data Mutations and When Not to Use CTEs

CTEs compose beautifully with window functions because window function results can't be filtered in the same SELECT where they're computed — you need a wrapper, and a CTE is cleaner than a subquery for this.

CTEs also work with INSERT, UPDATE, DELETE and MERGE in PostgreSQL and SQL Server — called data-modifying CTEs. In PostgreSQL, data-modifying CTEs execute exactly once, and their effects are visible to the outer query but not to other CTEs in the same WITH clause.

Know when NOT to use a CTE. If your query is a single flat SELECT that runs fine as a subquery, wrapping it in a CTE adds no value. CTEs shine for multi-step logic, recursion, and avoiding repeated complex expressions. A CTE over a 50-million-row unindexed table is still a full table scan — CTEs don't fix missing indexes.

-- PATTERN A: CTE + Window Function pipeline
-- Find products in bottom 10th percentile of sales velocity per category

WITH product_velocity AS (
    SELECT p.product_id, p.product_name, p.category,
           ROUND(SUM(oi.quantity)::NUMERIC /
                 NULLIF(CURRENT_DATE - p.launch_date, 0), 2) AS daily_velocity
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    LEFT JOIN orders o ON o.order_id = oi.order_id AND o.order_status = 'completed'
    WHERE p.is_active = true AND p.launch_date < CURRENT_DATE - INTERVAL '30 days'
    GROUP BY p.product_id, p.product_name, p.category, p.launch_date
),
velocity_ranked AS (
    SELECT *,
           PERCENT_RANK() OVER (PARTITION BY category ORDER BY daily_velocity ASC)
               AS velocity_percentile
    FROM product_velocity
)
SELECT product_id, product_name, category, daily_velocity,
       ROUND(velocity_percentile * 100, 1) AS percentile,
       'CLEARANCE_REVIEW' AS action
FROM velocity_ranked
WHERE velocity_percentile <= 0.10
ORDER BY category, daily_velocity;

-- PATTERN B: Atomic archive-and-delete (PostgreSQL data-modifying CTE)
WITH to_archive AS (
    DELETE FROM orders
    WHERE order_status = 'completed'
      AND order_date < CURRENT_DATE - INTERVAL '2 years'
    RETURNING *
)
INSERT INTO orders_archive
SELECT *, CURRENT_TIMESTAMP AS archived_at
FROM to_archive
RETURNING order_id, archived_at;

CTE vs View vs Temporary Table — When to Use Which

Choosing between a CTE, a view, and a temporary table depends on scope, performance requirements, and reuse pattern. All three allow you to encapsulate a query, but they differ drastically in lifespan, materialization behavior, and DDL impact.

CTEs are ideal for single-query decomposition. They have no schema footprint, disappear immediately after the query finishes, and cannot be reused across separate statements. They are the best choice for breaking down complex logic within one query, especially when recursion is needed or when you want to chain multiple transformation steps.

Views are persistent schema objects that wrap a query. They can be referenced across multiple queries by multiple users. They are a security boundary and provide abstraction. However, they require DDL permissions (CREATE VIEW) and can become a maintenance burden if not managed. Views are best for shared business logic that must be used consistently across many reports or applications.

Temporary tables (prefixed with # in SQL Server, temp table in PostgreSQL) are explicitly materialized on disk (or in memory for small ones). They persist for the duration of the session (or transaction in some cases). They are the only option when you need to reuse an expensive intermediate result multiple times within a script or across multiple statements without re-executing the source query. They also allow indexes and statistics, giving you more control over query performance.

Here's a quick reference:

In production, the pattern is often: use CTEs for readability within a single query, use temp tables when you need to break that query into multiple steps with explicit materialization, and use views only when the same logic must be shared across teams or applications.

CTEs and Indexing — Why Your Recursive CTE Just Killed the Server and How to Fix It

Nobody tells you this until you page them at 3 AM. A recursive CTE looks innocent. It's not. The recursive member executes per row, not per batch. If your anchor set returns 10k rows and each recursion step does a full table scan, you're generating millions of logical reads before your monitoring even twitches.

The fix isn't 'add an index'. It's add the right index. A recursive CTE accessing a parent-child table needs a covering index on the join columns used in the recursive step. Without it, SQL Server (or Postgres, or MySQL) spools to tempdb for every iteration. That's where performance goes to die.

Test this yourself. Write the recursive CTE. Check the execution plan — look for 'Eager Spool' or 'Index Scan' on the recursive side. If you see either, you're one bad index away from a production incident. The index you need is composite: (parent_id, child_id) if your join is on parent_id. Include any columns you filter or return. That's it. No magic.

// io.thecodeforge — database tutorial

-- DO NOT deploy this: no index on manager_id
WITH OrgChart AS (
    SELECT employee_id, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, oc.depth + 1
    FROM employees e
    INNER JOIN OrgChart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM OrgChart;

-- DO this: create the covering index first
CREATE INDEX IX_employees_manager_id_includes
ON employees (manager_id, employee_id)
INCLUDE (department_id, salary);

-- Now the recursive CTE hits an index seek, not a scan
WITH OrgChart AS (
    SELECT employee_id, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, oc.depth + 1
    FROM employees e
    INNER JOIN OrgChart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM OrgChart;

CTE Side Effects — When They Don't Execute Exactly Once and Why That Breaks Your Report

Here's a fun one: you write a CTE that calls a function with side effects — maybe it logs access, updates a counter, or reads from a sequence. You reference the CTE twice in the outer query. SQL Server says 'I'll just inline this' and executes the function twice. Now you've double-logged, double-counted, or burned two sequence values. Good luck explaining that to the auditor.

This isn't a bug. It's by design. The optimizer decides whether to materialize a CTE or inline it based on cost estimates. You don't control it. If your CTE uses a non-deterministic function (GETDATE(), NEWID(), RAND(), any custom function with side effects), you get one execution per reference, not one execution total.

Fix: If you must have side effects exactly once, force materialization. Use a temp table. Or wrap the CTE in another CTE that adds TOP 100 PERCENT or ORDER BY a non-clustered column — some optimizers back off inlining when they see that. Better yet: don't put side-effect functions in CTEs at all. Push them to an explicit temp table. You lose the 'elegance' but gain correctness. Production rewards correctness.

// io.thecodeforge — database tutorial

-- Side-effect function called twice because CTE inlined
CREATE FUNCTION dbo.LogAccess(@user_id INT) RETURNS INT
AS BEGIN
    INSERT INTO access_log(user_id, accessed_at)
    VALUES (@user_id, GETDATE());
    RETURN @user_id;
END;

-- This CTE looks clean. It's a lie.
WITH UserCTE AS (
    SELECT user_id, dbo.LogAccess(user_id) AS logged
    FROM users
    WHERE active = 1
)
SELECT * FROM UserCTE WHERE user_id = 100
UNION ALL
SELECT * FROM UserCTE WHERE user_id = 200;
-- dbo.LogAccess executes 2 times per user, not 1

-- Fix: force materialization into temp table
SELECT user_id, dbo.LogAccess(user_id) AS logged
INTO #logged_users
FROM users
WHERE active = 1;

SELECT * FROM #logged_users WHERE user_id = 100
UNION ALL
SELECT * FROM #logged_users WHERE user_id = 200;
-- dbo.LogAccess executes exactly 1 time per user

CTEs in WHERE and FROM Subqueries — The Nesting Trap That Kills Readability Fast

You can use a CTE inside a subquery. You can use a subquery inside a CTE. You can stack three CTEs in a WITH clause. The SQL standard lets you. Doesn't mean you should. I've seen a 'report query' with six CTEs, each referencing the prior one, then two CTEs used inside a HAVING clause. It took me 45 minutes to trace the data flow. Nobody could maintain it.

The real problem: CTEs don't show in the execution plan as named entities. When you nest them inside subqueries, the optimizer flattens everything into one giant blob. You lose the ability to isolate performance problems. A slow CTE inside a WHERE clause becomes invisible — you just see a big 'Filter' operator with no clue which part is killing you.

My rule: one level of CTE for readability. If you need intermediate results, write them to temp tables with appropriate indexes. Name them. Document them. Your future self (or the junior who inherits this) will thank you when the query needs tuning. You don't get points for 'elegant nesting' in production — you get points for being able to explain what your query does at 2 AM.

// io.thecodeforge — database tutorial

-- Don't do this: CTE nested in a subquery
WITH ActiveUsers AS (
    SELECT user_id, last_login
    FROM users
    WHERE status = 'active'
)
SELECT *
FROM orders
WHERE user_id IN (
    SELECT user_id FROM ActiveUsers
    WHERE last_login > '2024-01-01'
);
-- This CTE is invisible in the execution plan as a separate step

-- Do this: materialize the intermediate result
SELECT user_id
INTO #active_recent_users
FROM users
WHERE status = 'active'
  AND last_login > '2024-01-01';

CREATE INDEX IX_temp_user_id ON #active_recent_users(user_id);

SELECT *
FROM orders
WHERE user_id IN (SELECT user_id FROM #active_recent_users);
-- Now you can isolate performance of the temp table population vs the main query

Advanced SQL CTE Techniques — Chaining, Modifying Data, and Debugging Like a Pro

You've mastered the single CTE. Now chain them. A CTE can reference previous CTEs in the same WITH block. This isn't just neat — it lets you build transformations step by step without nesting subqueries into a nightmare. Each CTE becomes a named pipeline stage. You can inspect, debug, and reuse each stage independently. That's the whole point.

But here's where most devs miss the mark: CTEs can modify data. In PostgreSQL and SQL Server, you can wrap INSERT, UPDATE, or DELETE inside a CTE and target the result set directly. This is massive for batch operations — update rows based on a complex join, then insert a log entry in the same statement. One atomic operation. No race conditions.

Production tip: Chain CTEs but watch the optimizer. Some engines materialize each CTE, others inline them. If your chain is deep and your data is wide, you might end up reading the same table five times. Profile before you profile your career.

// io.thecodeforge — database tutorial

WITH high_earners AS (
    SELECT department_id, salary
    FROM employees
    WHERE salary > 100000
),
department_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM high_earners
    GROUP BY department_id
)
UPDATE employees e
SET bonus = e.salary * 0.15
FROM department_avg da
WHERE e.department_id = da.department_id
  AND e.salary > da.avg_salary;

SELECT * FROM employees WHERE bonus IS NOT NULL;

Potential Issues or Limitations of CTEs in SQL — The Hidden Costs Nobody Warns You About

CTEs look clean but they hide landmines. First: no index hints. You can't force an index inside a CTE definition. If your recursive CTE walks a million-node tree and the join column isn't indexed, you're toast. The server will scan full tables every recursion level. That's not a bug — it's your poor schema design.

Second: CTEs are often materialized only once, but some engines (hello, SQL Server) might re-evaluate them multiple times inside the same query. If your CTE uses a volatile function like GETDATE() or RAND(), you'll get inconsistent results. Debugging that is a special kind of hell.

Third: recursion limits. MySQL defaults to 1000 recursion depth. PostgreSQL defaults to 100 and you have to explicitly SET session to raise it. Forget that, and your org chart query silently truncates at level 7. Nobody catches that until leadership asks why the CEO's reports are missing.

Fourth: CTEs can't be indexed, partitioned, or parallelized the way temp tables can. For large datasets, a temp table with proper indexes will blow past a CTE every time.

// io.thecodeforge — database tutorial

WITH RECURSIVE org_tree AS (
    SELECT employee_id, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree
WHERE depth > 10;

-- In PostgreSQL, default max depth is 100. Errors at depth 101.

CTEs in Azure Synapse Analytics — Why Your On-Premise Query Breaks in the Cloud

Azure Synapse Analytics (formerly SQL Data Warehouse) and Analytics Platform System (PDW) support CTEs but with critical restrictions that catch teams off guard. Unlike SQL Server, Synapse requires that every CTE be referenced at least once in the main query body—orphan CTEs silently fail. Recursive CTEs are outright banned because Synapse's MPP architecture cannot handle stack-based recursion across distribution nodes. Non-recursive CTEs behave as inline views, not materialized staging tables, so referencing the same CTE twice causes double execution and massive slowdowns. CTEs also cannot contain ORDER BY without TOP or OFFSET/FETCH, nor can they use OPTION clauses. The biggest trap: Synapse CTEs are limited to one SELECT statement—you cannot chain CTEs with UNION in the same WITH block. Always test CTE logic in a dedicated SQL pool before production deploy.

// io.thecodeforge — database tutorial

-- This fails in Synapse: Recursive CTE not allowed
WITH OrgCTE AS (
    SELECT EmployeeID, ManagerID FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID 
    FROM Employees e
    INNER JOIN OrgCTE o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgCTE;

-- Workaround: Use temp table + loop instead
CREATE TABLE #Org (EmployeeID INT, Level INT);
INSERT INTO #Org VALUES (1, 0);
WHILE @@ROWCOUNT > 0
    INSERT INTO #Org
    SELECT e.EmployeeID, o.Level + 1
    FROM Employees e
    INNER JOIN #Org o ON e.ManagerID = o.EmployeeID
    WHERE NOT EXISTS (SELECT 1 FROM #Org WHERE EmployeeID = e.EmployeeID);

Arguments and Usage Guidelines — How Databases Differ on CTE Syntax

Every major SQL dialect supports CTEs but with subtle argument differences that break portability. PostgreSQL and MySQL allow CTEs to have explicit column aliases: WITH cte (col1, col2) AS (SELECT ...). SQL Server and Oracle require this when subquery columns lack names (e.g., aggregate expressions). DB2 and Teradata enforce strict recursion depth limits via OPTION (MAXRECURSION n)—default is 100 in SQL Server, infinite in PostgreSQL unless you set max_recursive_iterations. The most ignored guideline: never use CTEs for singleton lookups. CTEs optimize poorly for single-row fetches because the optimizer scans the entire CTE definition before applying outer filters. Another hard rule—CTEs in subqueries or WHERE clauses force nested scopes that degrade readability faster than any other pattern. Always define CTEs at the top of the query, not inside derived tables. If you exceed 3 CTEs chained, extract the logic into a view.

// io.thecodeforge — database tutorial

-- PostgreSQL: Explicit column aliases required for aggregates
WITH dept_avg (department, avg_salary) AS (
    SELECT department, AVG(salary)
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_avg WHERE avg_salary > 50000;

-- SQL Server: Same syntax, but default recursion limit 100
-- To override, use OPTION (MAXRECURSION 0) for unlimited
WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 1000
)
SELECT n FROM numbers
OPTION (MAXRECURSION 1000);

-- MySQL 8+: CTE columns must match subquery column count

Recursive CTE Patterns: Tree Traversal, Graph Queries, Hierarchy

Recursive CTEs are a powerful tool for traversing hierarchical or graph-structured data. They consist of an anchor member (base result) and a recursive member that references the CTE itself. SQL Server executes the anchor first, then repeatedly executes the recursive member until no new rows are produced. Common patterns include:

1. Tree Traversal (Org Chart) Traverse an employee hierarchy to find all subordinates of a manager:

``sql WITH EmployeeHierarchy AS ( SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level FROM Employees WHERE ManagerID IS NULL -- root UNION ALL SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, Level + 1 FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchy; ``

2. Graph Queries (Shortest Path) Find the shortest path in a directed graph using a breadth-first search approach. The anchor selects starting nodes, and the recursive member follows edges, tracking visited nodes to avoid cycles.

``sql WITH PathCTE AS ( SELECT StartNode, EndNode, CAST(StartNode AS VARCHAR(MAX)) AS Path, 1 AS Depth FROM Edges WHERE StartNode = 'A' UNION ALL SELECT e.StartNode, e.EndNode, Path + '->' + e.EndNode, Depth + 1 FROM Edges e INNER JOIN PathCTE p ON e.StartNode = p.EndNode WHERE p.Path NOT LIKE '%' + e.EndNode + '%' -- avoid cycles ) SELECT TOP 1 Path, Depth FROM PathCTE WHERE EndNode = 'G' ORDER BY Depth; ``

3. Hierarchy with Aggregation Roll up values from leaf nodes to root. For example, summing sales across a product category tree.

``sql WITH CategoryTree AS ( SELECT CategoryID, ParentCategoryID, SalesAmount FROM Categories WHERE ParentCategoryID IS NULL UNION ALL SELECT c.CategoryID, c.ParentCategoryID, c.SalesAmount FROM Categories c INNER JOIN CategoryTree ct ON c.ParentCategoryID = ct.CategoryID ) SELECT CategoryID, SUM(SalesAmount) AS TotalSales FROM CategoryTree GROUP BY CategoryID; ``

Performance Considerations Recursive CTEs can be expensive on large datasets. Ensure proper indexing on join columns (e.g., ManagerID, ParentCategoryID). Use MAXRECURSION option to limit depth and prevent infinite loops.

Production Insight Always test recursive CTEs with representative data volumes. Use OPTION (MAXRECURSION 0) only when you are certain the recursion depth is bounded. For very deep hierarchies (e.g., > 100 levels), consider alternative approaches like nested sets or iterative processing.

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

CTE vs Subquery vs Temp Table: Performance Comparison

Choosing between CTEs, subqueries, and temp tables depends on readability, reusability, and performance. Here's a breakdown:

1. Subqueries - Inline in SELECT, FROM, or WHERE clauses. - Pros: No extra objects, simple for one-off use. - Cons: Can be hard to read when nested; often re-evaluated multiple times. - Performance: SQL Server may optimize correlated subqueries, but non-correlated subqueries can be materialized as spools. For complex logic, performance can degrade.

2. CTEs (Common Table Expressions) - Named temporary result set within a single query. - Pros: Improves readability by breaking complex queries into steps; can be referenced multiple times in the same query. - Cons: Not reusable across queries; may be re-evaluated each time referenced (unless materialized by the optimizer). - Performance: CTEs are not inherently materialized; the optimizer decides. For multiple references, a CTE may be executed multiple times, hurting performance.

3. Temp Tables (#temp) - Physical temporary table stored in tempdb. - Pros: Reusable across batches; can be indexed; explicit materialization ensures single evaluation. - Cons: Overhead of creating and dropping; can cause tempdb contention. - Performance: Best for large intermediate results that are referenced multiple times or need indexing.

Performance Comparison Example Suppose you need to compute total sales per region and then filter regions with high sales.

Subquery approach: ``sql SELECT Region, TotalSales FROM ( SELECT Region, SUM(Sales) AS TotalSales FROM Orders GROUP BY Region ) AS RegionSales WHERE TotalSales > 10000; ``

CTE approach: ``sql WITH RegionSales AS ( SELECT Region, SUM(Sales) AS TotalSales FROM Orders GROUP BY Region ) SELECT Region, TotalSales FROM RegionSales WHERE TotalSales > 10000; ``

Temp table approach: ```sql SELECT Region, SUM(Sales) AS TotalSales INTO #RegionSales FROM Orders GROUP BY Region;

SELECT Region, TotalSales FROM #RegionSales WHERE TotalSales > 10000;

DROP TABLE #RegionSales; ```

When to Use What - Use subqueries for simple, single-use logic. - Use CTEs for readability and when the result is referenced only once or twice in the same query. - Use temp tables when the result is large, referenced multiple times, or needs indexing. Also prefer temp tables for iterative processing (e.g., loops).

Production Insight In performance-critical scenarios, test all three approaches. CTEs are often the most readable but can be a trap if referenced multiple times. Temp tables give you control over materialization and indexing, which is crucial for large datasets.

-- CTE approach
WITH RegionSales AS (
    SELECT Region, SUM(Sales) AS TotalSales
    FROM Orders
    GROUP BY Region
)
SELECT Region, TotalSales
FROM RegionSales
WHERE TotalSales > 10000;

-- Temp table approach
SELECT Region, SUM(Sales) AS TotalSales
INTO #RegionSales
FROM Orders
GROUP BY Region;

SELECT Region, TotalSales
FROM #RegionSales
WHERE TotalSales > 10000;
DROP TABLE #RegionSales;

Multiple CTEs: WITH ... , ... Pattern for Complex Queries

SQL Server allows defining multiple CTEs in a single WITH clause, separated by commas. This pattern is invaluable for breaking down complex queries into manageable, readable steps. Each CTE can reference previous CTEs, enabling a modular approach.

Syntax ``sql WITH CTE1 AS ( SELECT ... ), CTE2 AS ( SELECT ... FROM CTE1 ... ), CTE3 AS ( SELECT ... FROM CTE2 ... ) SELECT * FROM CTE3; ``

Example: Sales Analysis Pipeline Suppose we need to compute monthly sales, then rank months, and finally filter top 3 months per year.

``sql WITH MonthlySales AS ( SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, SUM(Amount) AS TotalSales FROM Orders GROUP BY YEAR(OrderDate), MONTH(OrderDate) ), RankedMonths AS ( SELECT Year, Month, TotalSales, ROW_NUMBER() OVER (PARTITION BY Year ORDER BY TotalSales DESC) AS Rank FROM MonthlySales ), TopMonths AS ( SELECT Year, Month, TotalSales FROM RankedMonths WHERE Rank <= 3 ) SELECT * FROM TopMonths ORDER BY Year, Rank; ``

Benefits - Readability: Each CTE encapsulates a logical step, making the query self-documenting. - Reusability: A CTE can be referenced multiple times within the same query, avoiding duplication. - Debugging: You can test each CTE independently by commenting out later parts.

Performance Considerations - Multiple CTEs do not automatically materialize; each reference may re-execute the CTE's logic. However, if a CTE is referenced only once, it's typically inlined. - For expensive CTEs referenced multiple times, consider using a temp table. - The optimizer may merge CTEs into a single plan, which can be efficient.

Production Insight Use multiple CTEs to replace deeply nested subqueries. They are especially useful in reporting queries where you need to compute aggregates, then apply window functions, then filter. However, be cautious of re-evaluation: if a CTE is referenced in multiple subsequent CTEs, it may be executed multiple times. Test with actual execution plans.

Limitations - CTEs are scoped to the single statement following the WITH clause. You cannot reuse them in another query. - Recursive CTEs cannot be combined with non-recursive CTEs in the same WITH clause (though you can have multiple recursive CTEs? Actually, only one recursive CTE per WITH clause is allowed in SQL Server).

Alternative: Chained CTEs vs Derived Tables Chained CTEs are generally more readable than nested derived tables. For example, compare:

```sql -- With CTEs WITH Step1 AS (...), Step2 AS (...) SELECT ...

-- With derived tables SELECT ... FROM (SELECT ... FROM (SELECT ...) AS Step1) AS Step2 ```

CTEs win for readability.

Best Practices - Keep each CTE focused on a single transformation. - Use meaningful names. - Limit the number of CTEs to avoid confusion; if you have more than 5-6, consider breaking into separate queries or using temp tables.

WITH MonthlySales AS (
    SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, SUM(Amount) AS TotalSales
    FROM Orders
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
),
RankedMonths AS (
    SELECT Year, Month, TotalSales,
           ROW_NUMBER() OVER (PARTITION BY Year ORDER BY TotalSales DESC) AS Rank
    FROM MonthlySales
),
TopMonths AS (
    SELECT Year, Month, TotalSales
    FROM RankedMonths
    WHERE Rank <= 3
)
SELECT * FROM TopMonths
ORDER BY Year, Rank;