Explaining CTE in SQL Server

Common Table Expressions (CTEs) in SQL are temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. They can be used to simplify complex queries and improve readability. Here's a detailed explanation with three real examples, complete with table creation scripts and sample data for end-to-end execution.

Simple Usage to select data

Scenario

Suppose you have a table of employees, and you want to get a list of employees along with their department names.

SQL

-- Create departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
)

GO

-- Create employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

GO

-- Insert sample data into departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
    (1, 'HR'),
    (2, 'Finance'),
    (3, 'IT'),
    (4, 'Accounts');

GO

-- Insert sample data into employees
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES
    (101, 'Devesh', 1),
    (102, 'Pankaj Kumar', 2),
    (103, 'Sanjay Kumar', 3),
    (104, 'Tanuj Sharma', 2),
    (105, 'Rajesh', 1),
    (106, 'Gaurav', 4);

CTE Query

-- Define the CTE
WITH EmployeeCTE AS (
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        d.DepartmentName
    FROM
        Employees e
    JOIN
        Departments d ON e.DepartmentID = d.DepartmentID
)

-- Use the CTE to select data
SELECT * 
FROM EmployeeCTE;

Recursive CTE for Organizational Chart

Scenario: You need to create an organizational chart that displays the hierarchy of employees and calculates the depth level of each employee within the organization

We will be defining a table having a Manager ID. And the Manager ID is nothing but its emp ID from the same table.

SQL Script

-- Create employees table with a self-referencing foreign key
CREATE TABLE OrgEmployees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES OrgEmployees(EmployeeID)
);

-- Insert sample data into OrgEmployees
INSERT INTO OrgEmployees (EmployeeID, EmployeeName, ManagerID) VALUES
    (1, 'CEO', NULL),
    (2, 'CTO', 1),
    (3, 'CFO', 1),
    (4, 'Lead Developer', 2),
    (5, 'Developer', 4),
    (6, 'Lead Accountant', 3),
    (7, 'Accountant', 6);

CTE query

-- Define the recursive CTE
WITH OrgHierarchy AS (
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS DepthLevel,
        CAST(EmployeeName AS VARCHAR(MAX)) AS HierarchyPath
    FROM
        OrgEmployees
    WHERE
        ManagerID IS NULL

    UNION ALL

    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        oh.DepthLevel + 1,
        CAST(oh.HierarchyPath + ' -> ' + e.EmployeeName AS VARCHAR(MAX)) AS HierarchyPath
    FROM
        OrgEmployees e
    JOIN
        OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)

-- Use the CTE to select the organizational chart with depth levels
SELECT
    EmployeeID,
    EmployeeName,
    ManagerID,
    DepthLevel,
    HierarchyPath
FROM
    OrgHierarchy
ORDER BY
    HierarchyPath;

Result

Multi-Level Aggregation and Filtering

Scenario: You have a sales database with multiple levels of product categories, and you want to calculate total sales for each category, subcategory, and product, then filter out categories with total sales below a certain threshold.

SQL

-- Create category table
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50),
    ParentCategoryID INT NULL
);

-- Create products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- Create sales table
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert sample data into categories
INSERT INTO Categories (CategoryID, CategoryName, ParentCategoryID) VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Laptops', 2),
    (4, 'Desktops', 2),
    (5, 'Accessories', 1);

-- Insert sample data into products
INSERT INTO Products (ProductID, ProductName, CategoryID) VALUES
    (1, 'Laptop A', 3),
    (2, 'Laptop B', 3),
    (3, 'Desktop A', 4),
    (4, 'Mouse', 5),
    (5, 'Keyboard', 5);

-- Insert sample data into sales
INSERT INTO Sales (SaleID, ProductID, SaleAmount, SaleDate) VALUES
    (1, 1, 1000.00, '2024-01-01'),
    (2, 2, 1500.00, '2024-01-02'),
    (3, 3, 800.00, '2024-01-03'),
    (4, 4, 20.00, '2024-01-04'),
    (5, 5, 30.00, '2024-01-05');

CTE for multilevel aggregation and filtering

-- Define the CTE for product sales
WITH ProductSales AS (
    SELECT
        p.CategoryID,
        SUM(s.SaleAmount) AS TotalSales
    FROM
        Sales s
    JOIN
        Products p ON s.ProductID = p.ProductID
    GROUP BY
        p.CategoryID
),

-- Define the CTE for category sales
CategorySales AS (
    SELECT
        c.CategoryID,
        c.CategoryName,
        c.ParentCategoryID,
        COALESCE(ps.TotalSales, 0) AS TotalSales
    FROM
        Categories c
    LEFT JOIN
        ProductSales ps ON c.CategoryID = ps.CategoryID
),

-- Recursive CTE to accumulate sales for parent categories
RecursiveCategorySales AS (
    SELECT
        CategoryID,
        CategoryName,
        ParentCategoryID,
        TotalSales
    FROM
        CategorySales

    UNION ALL

    SELECT
        c.CategoryID,
        c.CategoryName,
        c.ParentCategoryID,
        cs.TotalSales
    FROM
        CategorySales c
    JOIN
        RecursiveCategorySales cs ON c.CategoryID = cs.ParentCategoryID
)

-- Aggregate the total sales for each category including parent categories
SELECT
    CategoryID,
    CategoryName,
    SUM(TotalSales) AS TotalSales
FROM
    RecursiveCategorySales
GROUP BY
    CategoryID, CategoryName
HAVING
    SUM(TotalSales) > 500; -- Filter for categories with sales above a threshold