25. CTEs (Common Table Expressions) -- Organizing Complex Queries

Level: Guided beginner to advanced

ℹ️ What You'll Learn

• CTE syntax (WITH clause)
• Non-recursive CTEs -- temporary named result sets
• Multiple CTEs -- chaining queries
• Recursive CTEs -- hierarchical data
• CTE for readability and organization
• Filtering grouped data with CTEs
• Common CTE mistakes
• Best practices

Complex queries nest subqueries. CTEs name intermediate results, improving readability. This article teaches organizing queries with CTEs.

Why CTEs Matter for Backend Developers

APIs need readable, maintainable queries:

Complex requirement: "Get top 3 students per class,
                      show with class average and ranking"
    v
Without CTE: Nested subqueries (hard to read)
WITH CTE:    Named steps (clear logic)
    v
Maintenance: Easy to debug and modify

CTEs make complex queries understandable.

CTE Syntax

WITH CTE_Name AS (
    SELECT Column1, Column2 FROM Table
    WHERE Condition
)
SELECT * FROM CTE_Name;

Parts:

• WITH: Start CTE
• CTE_Name: Reference name
• AS (...): CTE definition
• SELECT after: Query the CTE

Example 1: Basic CTE (readability)

Rank students within class:

WITH StudentRanks AS (
    SELECT 
        s.Name AS Student,
        s.ClassName,
        r.MarksObtained,
        ROW_NUMBER() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS ClassRank
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
)
SELECT * FROM StudentRanks
WHERE ClassRank <= 3
ORDER BY ClassName, ClassRank;

Result (top 3 per class):

Student     | ClassName | MarksObtained | ClassRank
Ravi Kumar  | 10-A      | 92            | 1
Priya Sharma| 10-A      | 85            | 2
Sneha Patel | 10-A      | 78            | 3
Arjun Reddy | 10-B      | 88            | 1

CTE: Named "StudentRanks". Filters in outer SELECT. Clearer than nested subquery.

Example 2: Multiple CTEs (chained)

Fee collection report:

WITH StudentFees AS (
    SELECT 
        s.StudentId,
        s.Name AS Student,
        s.ClassName,
        f.TotalFees,
        f.PaidAmount
    FROM Student s
    LEFT JOIN FeeAccount f ON s.StudentId = f.StudentId
),
FeeStats AS (
    SELECT 
        ClassName,
        COUNT(StudentId) AS StudentCount,
        SUM(TotalFees) AS TotalFeesCollected,
        SUM(PaidAmount) AS TotalPaid,
        SUM(TotalFees) - SUM(PaidAmount) AS TotalDue
    FROM StudentFees
    GROUP BY ClassName
)
SELECT * FROM FeeStats
ORDER BY TotalDue DESC;

Result (fee summary):

ClassName | StudentCount | TotalFeesCollected | TotalPaid | TotalDue
10-A      | 3            | 150000             | 100000    | 50000
10-B      | 2            | 100000             | 80000     | 20000
11-A      | 4            | 200000             | 180000    | 20000

StudentFees CTE: Student + fee data. FeeStats CTE: Group and aggregate. Readable pipeline.

Example 3: CTE for Complex Filtering

Top 3 students from each class averaging > 80:

WITH ClassStats AS (
    SELECT 
        s.ClassName,
        AVG(r.MarksObtained) AS ClassAverage
    FROM Student s
    LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
    GROUP BY s.ClassName
),
StudentRanks AS (
    SELECT 
        s.Name AS Student,
        s.ClassName,
        r.MarksObtained,
        ROW_NUMBER() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS Rank
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
)
SELECT 
    sr.Student,
    sr.ClassName,
    sr.MarksObtained,
    sr.Rank,
    cs.ClassAverage
FROM StudentRanks sr
INNER JOIN ClassStats cs ON sr.ClassName = cs.ClassName
WHERE sr.Rank <= 3 AND cs.ClassAverage > 80
ORDER BY sr.ClassName, sr.Rank;

Result (filtered):

Student     | ClassName | MarksObtained | Rank | ClassAverage
Ravi Kumar  | 10-A      | 92            | 1    | 85.0
Priya Sharma| 10-A      | 85            | 2    | 85.0
Sneha Patel | 10-A      | 78            | 3    | 85.0

Multiple CTEs: ClassStats (aggregate), StudentRanks (ranking), Final SELECT (join and filter).

Example 4: Recursive CTE (hierarchy)

Get class hierarchy (10th -> 10-A -> sections):

WITH ClassHierarchy AS (
    -- Anchor: Root classes (no parent)
    SELECT 
        ClassId,
        ClassName,
        NULL AS ParentId,
        1 AS Level
    FROM Class
    WHERE ParentClassId IS NULL

    UNION ALL

    -- Recursive: Child classes
    SELECT 
        c.ClassId,
        c.ClassName,
        c.ParentClassId,
        ch.Level + 1
    FROM Class c
    INNER JOIN ClassHierarchy ch ON c.ParentClassId = ch.ClassId
)
SELECT * FROM ClassHierarchy
ORDER BY Level, ClassName;

Result (hierarchy):

ClassId | ClassName | ParentId | Level
1       | 10th      | NULL     | 1
2       | 10-A      | 1        | 2
3       | 10-B      | 1        | 2
4       | 10-A-1    | 2        | 3

Anchor: Roots (no parent). Recursive: Children (join to hierarchy). Builds tree.

Example 5: Recursive CTE with Path

Show full path in hierarchy:

WITH ClassPath AS (
    -- Anchor: Root classes
    SELECT 
        ClassId,
        ClassName,
        ParentClassId,
        CAST(ClassName AS VARCHAR(MAX)) AS FullPath,
        1 AS Level
    FROM Class
    WHERE ParentClassId IS NULL

    UNION ALL

    -- Recursive: Build path
    SELECT 
        c.ClassId,
        c.ClassName,
        c.ParentClassId,
        cp.FullPath + ' -> ' + c.ClassName AS FullPath,
        cp.Level + 1
    FROM Class c
    INNER JOIN ClassPath cp ON c.ParentClassId = cp.ClassId
)
SELECT ClassName, FullPath, Level FROM ClassPath
ORDER BY FullPath;

Result (path):

ClassName | FullPath          | Level
10th      | 10th              | 1
10-A      | 10th -> 10-A       | 2
10-A-1    | 10th -> 10-A -> 10-A-1 | 3

Concatenate path as recursion builds. Shows full hierarchy path.

Example 6: CTE to Replace Subquery

Original (nested subquery):

SELECT 
    s.Name,
    r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
WHERE r.MarksObtained > (
    SELECT AVG(MarksObtained) FROM ExamResult
);

Rewritten (CTE):

WITH OverallAverage AS (
    SELECT AVG(MarksObtained) AS AvgMarks FROM ExamResult
)
SELECT 
    s.Name,
    r.MarksObtained,
    oa.AvgMarks
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
CROSS JOIN OverallAverage oa
WHERE r.MarksObtained > oa.AvgMarks;

CTE version: Clearer, can reference in both WHERE and SELECT.

---

Common CTE Mistakes

Mistake 1: Missing WITH clause between multiple CTEs

Wrong:

WITH CTE1 AS (
    SELECT * FROM Student
)
WITH CTE2 AS (
    SELECT * FROM Teacher
);
-- ERROR: Can't use WITH twice

Fix: Use comma:

WITH CTE1 AS (
    SELECT * FROM Student
),
CTE2 AS (
    SELECT * FROM Teacher
)
SELECT * FROM CTE1 UNION SELECT * FROM CTE2;

Mistake 2: Referencing CTE outside of following SELECT

Wrong:

WITH StudentRanks AS (
    SELECT * FROM Student
);
SELECT * FROM StudentRanks;  -- ERROR: CTE scope ended

Fix: CTE followed immediately by SELECT:

WITH StudentRanks AS (
    SELECT * FROM Student
)
SELECT * FROM StudentRanks;

CTE only valid for immediately following query.

Mistake 3: Infinite recursion (no termination)

Wrong:

WITH ClassHierarchy AS (
    SELECT ClassId, ParentClassId FROM Class
    
    UNION ALL
    
    SELECT c.ClassId, c.ParentClassId
    FROM Class c
    INNER JOIN ClassHierarchy ch ON c.ClassId = ch.ParentClassId
    -- Cycles: 10-A points to 10th, 10th points to 10-A (infinite loop)
)
SELECT * FROM ClassHierarchy;
-- MAXRECURSION exceeded error

Fix: Ensure anchor stops recursion:

WITH ClassHierarchy AS (
    -- Anchor: ParentClassId IS NULL (stops recursion)
    SELECT ClassId, ParentClassId, 1 AS Level FROM Class
    WHERE ParentClassId IS NULL
    
    UNION ALL
    
    SELECT c.ClassId, c.ParentClassId, ch.Level + 1
    FROM Class c
    INNER JOIN ClassHierarchy ch ON c.ParentClassId = ch.ClassId
    WHERE ch.Level < 10  -- Safety limit
)
SELECT * FROM ClassHierarchy;

Mistake 4: CTE performance -- large result sets

Wrong:

WITH AllExamResults AS (
    SELECT * FROM ExamResult  -- 1M rows
)
SELECT * FROM AllExamResults
WHERE MarksObtained > 80;  -- Filter after building CTE

Result: Builds 1M-row CTE, then filters.

Fix: Filter in CTE:

WITH HighScores AS (
    SELECT * FROM ExamResult
    WHERE MarksObtained > 80  -- Filter in CTE definition
)
SELECT * FROM HighScores;

Mistake 5: Complex recursive CTE without MAXRECURSION

Wrong:

WITH Recursive AS (
    SELECT ... FROM ...
    UNION ALL
    SELECT ... FROM ... INNER JOIN Recursive
)
SELECT * FROM Recursive;
-- If logic wrong: infinite loop, hangs system

Fix: Add safety limit:

WITH Recursive AS (
    ...
)
SELECT * FROM Recursive
OPTION (MAXRECURSION 100);  -- Limit to 100 levels

---

Best Practices for CTEs

1. Name CTEs descriptively -- StudentRanks, not CTE1
2. Use for readability -- Complex queries benefit most
3. Filter in CTE -- Don't build huge intermediate sets
4. Anchor before recursion -- Base case first
5. Add MAXRECURSION on recursive CTEs -- Prevent hangs
6. One CTE per logical step -- Multiple CTEs OK if clear
7. Test with OPTION (MAXRECURSION) -- Safety on recursive

---

🎯 Q1: What's the difference between CTE and subquery?

Subquery: Inline, used once.

SELECT * FROM (SELECT * FROM Student WHERE ...) AS Sub;

CTE: Named, reference multiple times.

WITH StudentList AS (SELECT * FROM Student WHERE ...)
SELECT * FROM StudentList
WHERE ... ALSO USE StudentList;

CTE: Reusable, more readable. Subquery: One-time, nested.

🎯 Q2: Can I reference a CTE inside another CTE?

Yes, in sequence:

WITH CTE1 AS (SELECT * FROM Student),
     CTE2 AS (SELECT * FROM CTE1 WHERE ...)
SELECT * FROM CTE2;

CTE2 uses CTE1. Build in order.

No, can't mix:

WITH CTE1 AS (SELECT * FROM CTE2),  -- ERROR: CTE2 not yet defined
     CTE2 AS (SELECT * FROM Student)

Define before use.

🎯 Q3: What's a recursive CTE?

Finds hierarchical data (tree structure):

WITH Hierarchy AS (
    SELECT ClassId, ParentClassId, 1 AS Level FROM Class WHERE ParentClassId IS NULL
    UNION ALL
    SELECT c.ClassId, c.ParentClassId, h.Level + 1
    FROM Class c INNER JOIN Hierarchy h ON c.ParentClassId = h.ClassId
)
SELECT * FROM Hierarchy;

Anchor: Root nodes. Recursive: Join to itself, build tree.

Use for org charts, folder hierarchies, categories.

🎯 Q4: What is MAXRECURSION?

Safety limit on recursive iterations:

SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION 100);  -- Stop after 100 levels

Prevents infinite loops hanging system.

Default: 100. Increase if needed, but test first.

🎯 Q5: Should I use CTE or subquery?

Use CTE when:

• Query reused multiple times
• Logic complex and needs naming
• Recursive hierarchy needed
• Multiple intermediate steps

Use subquery when:

• Simple, one-time filter
• Single use, short logic

Rule: CTEs for readability. Subqueries for simple, short logic.

🎯 Q6: Can I reference CTE in WHERE clause?

Yes, after CTE defined:

WITH StudentRanks AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY MarksObtained DESC) AS Rank
    FROM Student
)
SELECT * FROM StudentRanks
WHERE Rank <= 10;

No, before definition:

WHERE Rank <= 10  -- ERROR: StudentRanks not yet defined
WITH StudentRanks AS (...)

CTE must be defined before USE.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on CTEs for organizing complex queries in SQL Server. Try these prompts:

• "Show how CTE improves readability: rank students within class"
• "Explain multiple CTEs: StudentRanks then filter with ClassStats"
• "What's a recursive CTE? Show class hierarchy example"
• "How do I build a path in recursive CTE (10th -> 10-A)?"
• "Quiz me: ask 5 questions about CTEs, recursion, multiple CTEs"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

26. Stored Procedures & Functions -- Reusable SQL Code ->