SQL Server WITH Clause: Complete Guide

Ryan Forrester

Oct 23rd, 2024

Blog

Breakdown of SQL Server WITH Clause

The WITH clause in SQL Server (also known as Common Table Expressions or CTEs) lets you break down complex queries into readable, manageable chunks. Instead of nesting multiple subqueries, you can write them as separate, named pieces. Let’s see how to use them effectively.

Table of Contents

Breakdown of SQL Server WITH Clause
Basic WITH Clause Structure
Real-World Applications
Advanced Techniques
Common Pitfalls and Solutions

Basic WITH Clause Structure

Here’s the fundamental syntax:

WITH CTE_Name AS (
    SELECT columns
    FROM table
    WHERE conditions
)
SELECT *
FROM CTE_Name;

A simple example:

WITH SalesSummary AS (
    SELECT 
        CustomerID,
        COUNT(*) AS OrderCount,
        SUM(TotalAmount) AS TotalSpent
    FROM Orders
    GROUP BY CustomerID
)
SELECT *
FROM SalesSummary
WHERE TotalSpent > 1000;

Real-World Applications

1. Finding Top Performers by Department

WITH DepartmentStats AS (
    SELECT 
        DepartmentID,
        AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
),
TopPerformers AS (
    SELECT 
        e.EmployeeID,
        e.Name,
        e.DepartmentID,
        e.Salary,
        d.AvgSalary,
        (e.Salary - d.AvgSalary) AS SalaryDifference
    FROM Employees e
    JOIN DepartmentStats d ON e.DepartmentID = d.DepartmentID
    WHERE e.Salary > d.AvgSalary
)
SELECT 
    Name,
    Salary,
    AvgSalary,
    FORMAT(SalaryDifference, 'C') AS AboveAverage
FROM TopPerformers
ORDER BY SalaryDifference DESC;

2. Sales Analysis with Running Totals

WITH MonthlySales AS (
    SELECT 
        DATETRUNC('month', OrderDate) AS Month,
        SUM(Amount) AS MonthlyTotal,
        COUNT(*) AS OrderCount
    FROM Orders
    WHERE YEAR(OrderDate) = 2024
    GROUP BY DATETRUNC('month', OrderDate)
),
RunningTotals AS (
    SELECT 
        Month,
        MonthlyTotal,
        OrderCount,
        SUM(MonthlyTotal) OVER (
            ORDER BY Month
        ) AS RunningTotal,
        AVG(MonthlyTotal) OVER (
            ORDER BY Month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS ThreeMonthAvg
    FROM MonthlySales
)
SELECT 
    FORMAT(Month, 'MMM yyyy') AS MonthYear,
    FORMAT(MonthlyTotal, 'C') AS Revenue,
    OrderCount,
    FORMAT(RunningTotal, 'C') AS YearToDate,
    FORMAT(ThreeMonthAvg, 'C') AS QuarterlyAvg
FROM RunningTotals
ORDER BY Month;

3. Customer Segmentation

WITH CustomerMetrics AS (
    SELECT 
        CustomerID,
        COUNT(*) AS OrderCount,
        SUM(Amount) AS TotalSpent,
        MAX(OrderDate) AS LastOrderDate,
        DATEDIFF(DAY, MAX(OrderDate), GETDATE()) AS DaysSinceLastOrder
    FROM Orders
    GROUP BY CustomerID
),
Segments AS (
    SELECT 
        CustomerID,
        OrderCount,
        TotalSpent,
        LastOrderDate,
        DaysSinceLastOrder,
        CASE
            WHEN OrderCount >= 10 AND DaysSinceLastOrder <= 90 
                THEN 'VIP'
            WHEN OrderCount >= 5 AND DaysSinceLastOrder <= 180 
                THEN 'Regular'
            WHEN DaysSinceLastOrder > 365 
                THEN 'Inactive'
            ELSE 'Occasional'
        END AS CustomerSegment
    FROM CustomerMetrics
)
SELECT 
    CustomerSegment,
    COUNT(*) AS CustomersInSegment,
    FORMAT(AVG(TotalSpent), 'C') AS AvgSpentPerCustomer,
    FORMAT(SUM(TotalSpent), 'C') AS TotalRevenue
FROM Segments
GROUP BY CustomerSegment
ORDER BY AVG(TotalSpent) DESC;

4. Hierarchical Data Navigation

WITH EmployeeHierarchy AS (
    -- Base case: employees with no manager
    SELECT 
        EmployeeID,
        Name,
        ManagerID,
        0 AS Level,
        CAST(Name AS VARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive case: employees and their managers
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.Path + ' > ' + e.Name AS VARCHAR(1000))
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    Level,
    Path,
    (SELECT COUNT(*) - 1 
     FROM STRING_SPLIT(Path, '>')) AS ReportsCount
FROM EmployeeHierarchy
ORDER BY Path;

Build a Web Application using only SQL

Building an entire web app using only SQL is not only possible but can be incredibly efficient with the right tools. With Five, a rapid application development environment, you can create a fully responsive SQL app that runs on top of a MySQL database without needing any additional programming languages or frameworks.

The step-by-step tutorial available here will guide you through creating:

A MySQL-based database for your web app.
A form-based user interface that dynamically interacts with the data.
Interactive charts and comprehensive PDF reports directly from SQL queries.
A secure login system, turning your SQL queries into a multiuser app.

Using Five, the development process is simplified. The platform automatically generates forms, charts, reports, and other front-end elements based on your SQL schema, meaning you can build an entire application with just SQL and Five’s intuitive visual tools.

By the end the tutorial available here “How To Build a SQL App“, you will have a fully functional web app that uses SQL to manage data, generate reports, and provide a dynamic user experience — all without writing any additional code outside SQL.

Get started with free access to Five’s development environment and begin building your SQL-powered web app today!

Build Your SQL Web App In 3 Steps
Start Developing Today

Advanced Techniques

1. Using Multiple CTEs for Complex Analysis

WITH OrderStats AS (
    SELECT 
        ProductID,
        COUNT(*) AS TimesOrdered,
        SUM(Quantity) AS TotalQuantity
    FROM OrderDetails
    GROUP BY ProductID
),
InventoryStats AS (
    SELECT 
        ProductID,
        StockLevel,
        ReorderPoint,
        LeadTimeDays
    FROM Inventory
),
ProductMetrics AS (
    SELECT 
        p.ProductID,
        p.Name,
        os.TimesOrdered,
        os.TotalQuantity,
        i.StockLevel,
        i.ReorderPoint,
        CASE
            WHEN i.StockLevel <= i.ReorderPoint 
                AND os.TimesOrdered > 10
                THEN 'Urgent Reorder'
            WHEN i.StockLevel <= i.ReorderPoint
                THEN 'Regular Reorder'
            WHEN os.TimesOrdered = 0
                THEN 'Dead Stock'
            ELSE 'Adequate Stock'
        END AS StockStatus
    FROM Products p
    LEFT JOIN OrderStats os ON p.ProductID = os.ProductID
    LEFT JOIN InventoryStats i ON p.ProductID = i.ProductID
)
SELECT 
    StockStatus,
    COUNT(*) AS ProductCount,
    SUM(StockLevel) AS TotalUnits
FROM ProductMetrics
GROUP BY StockStatus
ORDER BY ProductCount DESC;

2. Temporary Results for Complex Calculations

WITH SalesData AS (
    SELECT 
        ProductID,
        SUM(Amount) AS Revenue,
        SUM(Quantity) AS UnitsSold,
        SUM(Amount - (Cost * Quantity)) AS Profit
    FROM Sales
    GROUP BY ProductID
),
ProductRanks AS (
    SELECT 
        ProductID,
        Revenue,
        UnitsSold,
        Profit,
        NTILE(4) OVER (ORDER BY Revenue DESC) AS RevenueQuartile,
        NTILE(4) OVER (ORDER BY Profit DESC) AS ProfitQuartile
    FROM SalesData
),
Performance AS (
    SELECT 
        ProductID,
        CASE 
            WHEN RevenueQuartile = 1 AND ProfitQuartile = 1 
                THEN 'Star'
            WHEN RevenueQuartile <= 2 AND ProfitQuartile > 2
                THEN 'High Volume Low Margin'
            WHEN RevenueQuartile > 2 AND ProfitQuartile <= 2
                THEN 'Low Volume High Margin'
            ELSE 'Underperforming'
        END AS Category
    FROM ProductRanks
)
SELECT 
    p.Category,
    COUNT(*) AS ProductCount,
    FORMAT(AVG(s.Revenue), 'C') AS AvgRevenue,
    FORMAT(AVG(s.Profit), 'C') AS AvgProfit
FROM Performance p
JOIN SalesData s ON p.ProductID = s.ProductID
GROUP BY p.Category
ORDER BY AvgProfit DESC;

Common Pitfalls and Solutions

1. Recursive CTE Infinite Loops

-- Problematic recursive CTE
WITH BadRecursion AS (
    SELECT ID, ParentID
    FROM Hierarchy
    WHERE ID = 1

    UNION ALL

    SELECT h.ID, h.ParentID
    FROM Hierarchy h
    JOIN BadRecursion b ON h.ParentID = b.ID
    -- Missing termination condition!
)

-- Fixed version with MAXRECURSION hint
WITH SafeRecursion AS (
    SELECT ID, ParentID, 1 AS Level
    FROM Hierarchy
    WHERE ID = 1

    UNION ALL

    SELECT h.ID, h.ParentID, r.Level + 1
    FROM Hierarchy h
    JOIN SafeRecursion r ON h.ParentID = r.ID
    WHERE r.Level < 10  -- Prevents infinite recursion
)
SELECT *
FROM SafeRecursion
OPTION (MAXRECURSION 10);

2. Performance Considerations

-- Instead of multiple separate CTEs
WITH Orders2024 AS (
    SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024
),
Orders2023 AS (
    SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023
)

-- Use a single CTE with conditional logic
WITH YearlyOrders AS (
    SELECT 
        *,
        YEAR(OrderDate) AS OrderYear
    FROM Orders
    WHERE YEAR(OrderDate) IN (2023, 2024)
)
SELECT 
    OrderYear,
    COUNT(*) AS OrderCount,
    SUM(Amount) AS TotalRevenue
FROM YearlyOrders
GROUP BY OrderYear;

The WITH clause helps you write clearer, more maintainable SQL. Use it to break down complex problems into smaller pieces, making your queries easier to understand and debug. Just remember to keep your CTEs focused and avoid unnecessary nesting that could impact performance.