Common Table Expressions (CTEs)

Common Table Expressions (CTEs)

What it is

CTEs (Common Table Expressions) allow defining temporary named result sets that exist only during query execution. Introduced in SQL Server 2005, they improve readability and enable recursive queries.

Before this feature

Required derived tables or temp tables:

-- Derived table (hard to read)
SELECT e.Name, d.DeptName, d.AvgSalary
FROM Employees e
INNER JOIN (
    SELECT DepartmentId,
           AVG(Salary) as AvgSalary,
           DepartmentName as DeptName
    FROM Employees
    GROUP BY DepartmentId, DepartmentName
) d ON e.DepartmentId = d.DepartmentId
WHERE e.Salary > d.AvgSalary;

-- Temp table (more code)
CREATE TABLE #DeptAvg (
    DepartmentId INT,
    AvgSalary DECIMAL(10,2)
);
INSERT INTO #DeptAvg
SELECT DepartmentId, AVG(Salary)
FROM Employees
GROUP BY DepartmentId;

SELECT e.Name FROM Employees e
INNER JOIN #DeptAvg d ON e.DepartmentId = d.DepartmentId;

DROP TABLE #DeptAvg;

After this feature

CTEs provide clean, readable syntax:

-- Simple CTE
WITH DepartmentAvg AS (
    SELECT DepartmentId, AVG(Salary) as AvgSalary
    FROM Employees
    GROUP BY DepartmentId
)
SELECT e.Name, e.Salary, d.AvgSalary
FROM Employees e
INNER JOIN DepartmentAvg d ON e.DepartmentId = d.DepartmentId
WHERE e.Salary > d.AvgSalary;

-- Multiple CTEs
WITH
ActiveEmployees AS (
    SELECT * FROM Employees WHERE Status = 'Active'
),
DeptSummary AS (
    SELECT DepartmentId, COUNT(*) as EmpCount
    FROM ActiveEmployees
    GROUP BY DepartmentId
)
SELECT d.Name, ds.EmpCount
FROM Departments d
INNER JOIN DeptSummary ds ON d.Id = ds.DepartmentId;

-- Recursive CTE (hierarchical data)
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT EmployeeId, Name, ManagerId, 1 as Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EmployeeId, e.Name, e.ManagerId, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerId = eh.EmployeeId
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, Name;

Why this is better

• Readable: Named result sets are self-documenting
• Reusable: Reference CTE multiple times in same query
• Recursive: Enable hierarchical queries (org charts, categories)
• Maintainable: Easier to modify than nested subqueries
• Scoped: Exists only for the query (no cleanup needed)

Key notes / edge cases

• CTEs are not stored; they’re executed with each reference
• Multiple references can impact performance (consider temp tables for large data)
• Recursive CTEs need termination condition to avoid infinite loops
• Can’t use ORDER BY in CTE definition (unless with TOP or OFFSET)
• Must be followed immediately by SELECT, INSERT, UPDATE, or DELETE

-- Performance consideration
-- If CTE is referenced multiple times, consider temp table
WITH LargeCTE AS (
    SELECT * FROM LargeTable WHERE ... -- expensive query
)
SELECT * FROM LargeCTE
UNION ALL
SELECT * FROM LargeCTE; -- CTE executed twice!

-- Recursive CTE with MAXRECURSION hint
WITH NumberSequence AS (
    SELECT 1 as Num
    UNION ALL
    SELECT Num + 1 FROM NumberSequence WHERE Num < 100
)
SELECT * FROM NumberSequence
OPTION (MAXRECURSION 100);

Quick practice

1. Write a CTE that finds all employees earning above average

   Answer

   WITH AvgSalary AS (
       SELECT AVG(Salary) as Average
       FROM Employees
   )
   SELECT e.Name, e.Salary
   FROM Employees e, AvgSalary a
   WHERE e.Salary > a.Average;

2. Create a recursive CTE to generate numbers 1 to 10

   Answer

   WITH Numbers AS (
       SELECT 1 as N
       UNION ALL
       SELECT N + 1 FROM Numbers WHERE N < 10
   )
   SELECT * FROM Numbers;

3. When would you use a temp table instead of a CTE?

   Answer

   Use temp tables when: 1) CTE is referenced many times (performance), 2) need to create indexes on results, 3) results need to persist across multiple statements.