A Deep Dive into Understanding, Writing, and Optimizing CTEs

Introduction to CTEs

When solving a complex math problem, we often break it into smaller steps, solve those and then combine the answers. Common Table Expressions, better known as CTE is just like that in SQL.

CTEs are a fundamental feature in SQL that greatly enhance the readability, modularity, and maintainability of your queries. CTEs allow you to define a temporary result set that exists only within the execution scope of a single SQL statement and can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They serve as powerful tools for breaking down complex queries, enabling recursive operations, and making your SQL code more elegant and reusable.

Why Use CTEs?

• Readability: CTEs can help break down complicated queries into simpler, logical building blocks.

• Reusability: The same CTE can be referenced multiple times within the query, avoiding duplication of code.

• Recursion: CTEs enable elegant solutions to problems involving hierarchical or recursive data.

• Maintainability: Modifying a CTE is easier than editing multiple subqueries or derived tables scattered throughout a query.

Types of CTE

• Non-Recursive CTEs - These are used to simplify complex joins or aggregations.

• Recursive CTEs - These are special CTE’s that reference themselves and are great for dealing with hierarchical and repetitive patterns.

CTE Syntax

Let’s break down the basic syntax of the Non-Recursive CTEs in SQL Server.

WITH cte_name (column1, column2, ...) AS (

-- SQL query that generates the temporary result set

SELECT ...

) SELECT * FROM cte_name;

• Start with the keyword WITH which is followed by the CTE name and the keyword AS.

• Inside the parentheses, write the SQL query that defines the temporary result set.

• Now you can use cte_name as if it were a regular table or view

You may define multiple CTEs by separating them with commas:

WITH cte1 AS (

-- Query 1

),

cte2 AS (

-- Query 2

)

SELECT ...

FROM cte1

JOIN cte2 ON ...

Recursive CTEs

WITH RECURSIVE cte_name (column1, column2, ...) AS (
    -- Anchor member: the base case
    SELECT column1, column2, ...
    FROM table
    WHERE condition_for_base_case

    UNION ALL

    -- Recursive member: refers back to the CTE itself
    SELECT t.column1, t.column2, ...
    FROM table t
    INNER JOIN cte_name c ON t.parent_column = c.column
)
SELECT * FROM cte_name;

• The anchor part selects the root(s).

• The recursive part keeps joining back to the CTE.

• UNION ALL to preserve duplicates (use UNION if you want distinct rows).

CTE Limitations and Considerations

• CTEs only exist for the duration of the executing statement. They cannot be reused across separate queries.

• While CTEs improve readability, they do not always offer performance improvements over subqueries.

• In some databases, they may even have performance drawbacks if used improperly.

• CTE names must be unique within the query and should not conflict with existing table names.

• Recursive CTEs may be limited by the database system’s default recursion depth, which can be adjusted if necessary.

When Not to Use CTEs

While CTEs are incredibly useful, they are not always the best solution:

• When performance is critical and analysis shows that CTEs are less efficient than alternatives.

• In queries where the same intermediate result is required in multiple different queries; use a view or temporary table instead.

• Where recursion exceeds the supported depth limits of your database.

Best Practices for Using CTEs

• Give meaningful, descriptive names to your CTEs and their columns.

• Limit the scope of your CTEs as much as possible to aid with comprehension and debugging.

• Avoid deeply nested or overly complex CTE chains unless absolutely necessary.

• Monitor performance for queries with multiple or recursive CTEs, and consider alternatives if queries become slow.

• Comment your CTEs if they perform non-obvious manipulations or calculations.

Practical Examples of CTEs

Example 1: Calculate the total sales per region and filter only those regions where the total sales exceed $100,000.

Sales Table

Expected Output

Approach 1: Use Subquery to calculate the total sales per region and filter only those regions where the total sales exceed 100000 dollars.

Select * from
 (select region,sum(amount) as total_sales from sales group by region) as region_sales 
where total_sales > 100000;

• The logic is nested.

• The subquery can get harder to manage when it grows.

• You can’t reuse the inner results elsewhere.

Approach 2: Use Non-Recursive CTEs to calculate the total sales per region and filter only those regions where the total sales exceed 100000 dollars.

WITH RegionSales As
(
Select region,sum(amount) as total_sales 
from Sales
Group By (Region)
) Select * from RegionSales 
where total_sales > 100000;

• It is easy to read and code is modular.

  Approach 3: Use Non-Recursive Chained or multiple CTEs to calculate the total sales per region and filter only those regions where the total sales exceed 100000 dollars.

WITH RegionSales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM Sales
  GROUP BY region
),
HighestRegionSales AS (
  SELECT * FROM RegionSales
  WHERE total_sales > 100000
)
SELECT * FROM HighestRegionSales;

In this query, we’re using two CTEs:

• The first CTE, called RegionSales, calculates the total sales per region by grouping and summing the data.

• The second CTE, HighestRegionSales, uses the output from RegionSales and filters it to return only regions where total sales exceed $100,000.

• Finally, we select from HighestRegionSales to get our final result set.

Using chained CTEs makes your SQL more modular and readable. Instead of writing nested subqueries, you break complex logic into smaller steps — just like building blocks.

This is especially helpful in enterprise scenarios where you want to debug or explain your query to someone else.

Example 2: Generate list of dates from January 1st 2025 to January 31 2025.

Approach 1: To generate a small number we can use the values clause.

SELECT DATEADD(DAY, v.n, '2025-01-01') AS calendar_date
FROM (
  VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
         (10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
         (20), (21), (22), (23), (24), (25), (26), (27), (28), (29),
         (30)
) AS v(n);

• You manually provide the integers 0 to 30 using VALUES.

• Each value n is added to '2025-01-01' using DATEADD.

• This gives you a list of 31 sequential dates.

• Tedious, imagine generating 100 dates.

Approach 2: Use Recursive CTEs to generate the numbers.

WITH DateRange AS (
    -- Anchor member: Start at Jan 1, 2025
    SELECT CAST('2025-01-01' AS DATE) AS calendar_date

    UNION ALL

    -- Recursive member: Add 1 day at a time
    SELECT DATEADD(DAY, 1, calendar_date)
    FROM DateRange
    WHERE calendar_date < '2025-01-31'
)
SELECT *
FROM DateRange
OPTION (MAXRECURSION 31);  -- Limit recursion to 31 iterations

• Anchor: Starts with '2025-01-01'.

• Recursive member: Adds 1 day to the previous row.

• MAXRECURSION: Limits recursion to 31 to prevent infinite loops.

• Much more readable.

• Easy to update.

Example 3: Show each department, its level in the hierarchy, and its full path.

Department Table

Expected Output

Approach 1: Use Subquery and Self Join to show each department, its level in the hierarchy, and its full path.

SELECT
    d4.id,
    d4.name,
    CASE 
        WHEN d3.name IS NULL THEN 1
        WHEN d2.name IS NULL THEN 2
        ELSE 3
    END AS level,
    CONCAT_WS(' > ', 
        d1.name,
        d2.name,
        d3.name,
        d4.name
    ) AS path
FROM departments d4
LEFT JOIN departments d3 ON d4.parent_dept_id = d3.id
LEFT JOIN departments d2 ON d3.parent_dept_id = d2.id
LEFT JOIN departments d1 ON d2.parent_dept_id = d1.id
ORDER BY level;

• d4 is the current department.

• d3, d2, and d1 are its ancestors up to 3 levels deep.

• CONCAT_WS(' > ', ...) builds the full path.

• CASE calculates the hierarchy level based on how many parent levels exist.

Limitations:

• This approach is not truly recursive.

• You must hard-code the depth (3 levels here).

• For deeper hierarchies, you'd need to extend it (e.g., d5, d6, etc.).

Approach 1: Use Recursive CTEs to show each department, its level in the hierarchy, and its full path.

WITH DepartmentHierarchy AS (
    -- Anchor: Top-level departments
    SELECT
        id,
        name,
        parent_dept_id,
        1 AS level,
        CAST(name AS VARCHAR(MAX)) AS path
    FROM departments
    WHERE parent_dept_id IS NULL

    UNION ALL

    -- Recursive: Find children of each department
    SELECT
        d.id,
        d.name,
        d.parent_dept_id,
        h.level + 1,
        CAST(h.path + ' > ' + d.name AS VARCHAR(MAX)) AS path
    FROM departments d
    INNER JOIN DepartmentHierarchy h ON d.parent_dept_id = h.id
)
SELECT *
FROM DepartmentHierarchy
ORDER BY level, path;

• Recursive CTEs automatically handle any number of nested levels.

• No need to guess or hardcode joins.

• for d1, d2, d3, etc. The path is constructed progressively, one step at a time.

• Much cleaner SQL

• Easier to explain, debug, and extend.

• Fits cleanly into documentation, training, and automation use cases.

Conclusion

Common Table Expressions (CTEs) improve SQL readability by breaking complex queries into logical, reusable blocks. They are especially powerful for recursive operations like traversing hierarchies. CTEs make queries cleaner and easier to maintain compared to deeply nested subqueries or fixed-depth joins. However, they may introduce performance overhead if not used carefully, especially in recursive form without proper termination. Unlike temporary tables, CTEs cannot be indexed or reused across batches. Despite these limitations, CTEs are a versatile tool that balances clarity and capability — ideal for solving layered query problems in a structured and maintainable way.