SQL Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary, named result set that is defined at the beginning of a query using the WITH keyword. The CTE can then be referenced by name inside the main query — just like a table or a view — but only exists for the duration of that single query.

Think of a CTE like a temporary whiteboard. Before solving a complex problem, the intermediate working steps are written on the whiteboard. The final answer refers back to what is written there. Once the problem is solved, the whiteboard is erased.

Why Use CTEs?

• Readability — Breaking a complex query into named, logical steps makes it much easier to understand.
• Reusability within the query — A CTE can be referenced multiple times in the same query without rewriting it.
• Alternative to subqueries — CTEs are often cleaner and easier to read than deeply nested subqueries.
• Recursive queries — CTEs support recursive logic, enabling hierarchical data traversal (like an org chart or category tree).

Basic CTE Syntax

WITH cte_name AS (
    -- The CTE query (inner query)
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- The main query that references the CTE
SELECT * FROM cte_name;

The Reference Table

Example 1: Simple CTE — Students Above Average Score

Without a CTE, a subquery in the WHERE clause would be needed:

-- Without CTE (using a subquery)
SELECT StudentName, Score
FROM Students
WHERE Score > (SELECT AVG(Score) FROM Students);

The same result with a CTE:

-- With CTE (much more readable)
WITH AvgScore AS (
    SELECT AVG(Score) AS AverageScore
    FROM Students
)
SELECT StudentName, Score
FROM Students, AvgScore
WHERE Score > AvgScore.AverageScore;

Result:

Example 2: CTE for Per-City Summary

WITH CityStats AS (
    SELECT City,
           COUNT(*)    AS TotalStudents,
           AVG(Score)  AS AvgScore,
           SUM(Fees)   AS TotalFees
    FROM Students
    GROUP BY City
)
SELECT City, TotalStudents, AvgScore, TotalFees
FROM CityStats
WHERE TotalStudents > 1
ORDER BY AvgScore DESC;

Result:

Multiple CTEs in One Query

Multiple CTEs can be defined before the main query, separated by commas. Each CTE can reference a previously defined CTE.

WITH
HighScorers AS (
    SELECT StudentID, StudentName, Score
    FROM Students
    WHERE Score >= 85
),
LowFeeStudents AS (
    SELECT StudentID, StudentName, Fees
    FROM Students
    WHERE Fees < 45000
)
-- Find students who are both high scorers AND pay low fees
SELECT h.StudentName, h.Score, l.Fees
FROM HighScorers h
INNER JOIN LowFeeStudents l ON h.StudentID = l.StudentID;

Result: Arjun Nair — score is 85 (qualifies as high scorer) and fees are 38000 (qualifies as low fee).

CTE vs Subquery — When to Use Each

Recursive CTE

A recursive CTE references itself, allowing it to process hierarchical data — such as an employee-manager chain, a category tree, or a graph. It consists of two parts: an anchor member (the starting point) and a recursive member (the part that references the CTE itself).

Reference: Employee Hierarchy Table

WITH RECURSIVE OrgChart AS (
    -- Anchor member: Start with the CEO (no manager)
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: Find each employee's subordinates
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, oc.Level + 1
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM OrgChart
ORDER BY Level;

Result:

The recursive CTE walks down the management chain automatically, regardless of how deep it goes.

Key Points to Remember

• CTEs are defined with the WITH keyword before the main SELECT.
• A CTE only exists for the duration of the query — it is not saved in the database like a view.
• Multiple CTEs are separated by commas; each one can reference the previous ones.
• Recursive CTEs must have an anchor member (starting point) joined with UNION ALL to a recursive member.
• CTEs make complex queries significantly easier to read, understand, and maintain.

Summary

CTEs provide a clean, readable way to break complex queries into manageable, named steps. Defined with the WITH keyword, they act as temporary tables that can be reused within the same query. For hierarchical data traversal, recursive CTEs replace what would otherwise require complex procedural code. Whether replacing nested subqueries or handling tree-shaped data, CTEs are an essential tool in the intermediate-to-advanced SQL toolkit.