SQL CTEs

SQL CTEs stands for Common Table Expressions.

A Common Table Expression (CTE) is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Defined using the WITH clause, CTEs make complex, multi-step queries significantly easier to write, read, and maintain by breaking them down into modular building blocks.

Basic Syntax

CTEs are defined immediately before the main query executes.

WITH cte_name (column1, column2, ...) AS (
    -- CTE Query Definition
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- Main Query referencing the CTE
SELECT column1, column2
FROM cte_name;

Why Use CTEs?

Before CTEs were introduced in standard SQL, complex multi-level aggregations required nested subqueries or derived tables, which quickly became unreadable and difficult to debug.

       DERIVED TABLE (NESTED)              COMMON TABLE EXPRESSION (CTE)
   +----------------------------+         +----------------------------+
   | SELECT * FROM (            |         | WITH StepOne AS (          |
   |   SELECT * FROM (          |  VS.    |   SELECT ...               |
   |     SELECT ...             |         | ),                         |
   |   ) AS Level1              |         | StepTwo AS (               |
   | ) AS Level2                |         |   SELECT ... FROM StepOne  |
   +----------------------------+         | )                          |
                                          | SELECT * FROM StepTwo;     |
                                          +----------------------------+
                                             (Linear & Easily Readable)

Key Advantages

• Readability: Structures queries linearly from top to bottom rather than nesting logic inside-out.
• Reusability: A single CTE can be referenced multiple times within the same main query (e.g., joining a CTE against itself).
• Recursion: Supports Recursive CTEs, allowing you to query hierarchical data like organizational charts or tree structures.

Practical Examples

1. Basic CTE Example

Find all customers whose total spending is above the overall average customer spending:

WITH CustomerTotals AS (
    -- Step 1: Calculate total spend per customer
    SELECT 
        CustomerID, 
        SUM(TotalAmount) AS TotalSpent
    FROM Orders
    GROUP BY CustomerID
),
AverageSpend AS (
    -- Step 2: Calculate overall average spend across all customers
    SELECT AVG(TotalSpent) AS OverallAvg
    FROM CustomerTotals
)
-- Step 3: Filter customers above average
SELECT 
    c.CustomerID, 
    ct.TotalSpent
FROM CustomerTotals ct
CROSS JOIN AverageSpend avg_s
WHERE ct.TotalSpent > avg_s.OverallAvg;

2. Multiple CTEs in a Single Query

You can define multiple CTEs sequentially separated by commas under a single WITH keyword:

WITH HighValueOrders AS (
    SELECT OrderID, CustomerID, TotalAmount
    FROM Orders
    WHERE TotalAmount > 1000
),
VIPCustomers AS (
    SELECT CustomerID, CustomerName, Country
    FROM Customers
    WHERE IsVIP = 1
)
SELECT 
    vc.CustomerName, 
    vc.Country, 
    hvo.OrderID, 
    hvo.TotalAmount
FROM HighValueOrders hvo
INNER JOIN VIPCustomers vc ON hvo.CustomerID = vc.CustomerID;

Recursive CTEs

A Recursive CTE references itself. It is specifically designed to handle hierarchical data, such as organizational management charts, bill-of-materials (BOM), or category trees.

Structure of a Recursive CTE

A recursive CTE consists of three parts:

1. Anchor Member: The base query that initializes the recursion (runs once).
2. UNION ALL: Combines the anchor result with the recursive step.
3. Recursive Member: The query that references the CTE itself and runs repeatedly until no new rows are returned.

WITH RECURSIVE OrgChart AS (
    -- 1. Anchor Member: Find top-level CEO (ManagerID IS NULL)
    SELECT EmployeeID, FirstName, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- 2. Recursive Member: Join Employees table to the CTE
    SELECT e.EmployeeID, e.FirstName, e.ManagerID, o.Level + 1
    FROM Employees e
    INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT EmployeeID, FirstName, ManagerID, Level
FROM OrgChart
ORDER BY Level, ManagerID;

Note: SQL Server uses WITH OrgChart AS ..., while PostgreSQL, MySQL 8.0+, and SQLite require the WITH RECURSIVE keyword.

CTE vs. Subquery vs. Temporary Table