SQL Common Table Expressions (CTE): WITH Clause & Recursive Queries

While subqueries work well for simple lookups, deeply nested subqueries create "SQL Spaghetti"—code that is almost impossible to read, maintain, or debug. Introduced in ANSI SQL:1999, Common Table Expressions (CTEs) allow developers to define named, modular temporary result sets using the WITH clause.

Standard CTE Syntax (The WITH Clause)

WITH cte_name AS (
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM cte_name;

Refactoring Complex Queries with Multiple CTEs

Consider calculating department averages and filtering top performers. With CTEs, the logic reads top-to-bottom like clean procedural code:

WITH CourseStats AS (
    -- 1. Calculate average and max GPA per course
    SELECT 
        course,
        ROUND(AVG(gpa), 2) AS avg_course_gpa,
        MAX(gpa) AS max_course_gpa
    FROM students
    GROUP BY course
),
HonorsStudents AS (
    -- 2. Identify students with high GPAs
    SELECT student_id, first_name, course, gpa
    FROM students
    WHERE gpa >= 3.80
)
-- 3. Final clean projection joining both modular CTEs
SELECT 
    h.student_id,
    h.first_name,
    h.course,
    h.gpa,
    c.avg_course_gpa
FROM HonorsStudents h
JOIN CourseStats c ON h.course = c.course;

Recursive CTE: Traversing Hierarchy Trees

The most powerful capability of CTEs is RECURSIVE CTEs. They solve tree traversal problems (such as company organizational charts) without complex procedural code:

-- Recursive query to generate numbers 1 to 10 dynamically
WITH RECURSIVE NumberSeries AS (
    -- Anchor Member (Base case)
    SELECT 1 AS n
    UNION ALL
    -- Recursive Member (Iterative step)
    SELECT n + 1 FROM NumberSeries WHERE n < 10
)
SELECT n FROM NumberSeries;

Next, let us explore analytic reporting using SQL Window Functions!