CTE in SQL

A Common Table Expression (CTE) provides a way to define a temporary result set that can be easily referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH keyword, and they make SQL queries more readable and can also be used for recursive queries.

A CTE has a similar role to a derived table or a subquery, but it provides better readability and allows the definition of multiple temporary result sets in the same query.

Basic Syntax:

WITH cte_name (column_name1, column_name2, ...) AS (
    SQL_query_definition
)
-- Main query referencing the CTE
SELECT ...
FROM cte_name
...

Example:

Imagine you have a sales table and you want to compute the total sales and then list all sales entries that exceed the average sales value.

WITH TotalSales AS (
    SELECT SUM(amount) AS total_amount FROM sales
),
AverageSales AS (
    SELECT AVG(amount) AS avg_amount FROM sales
)
-- Main query
SELECT s.salesperson, s.amount, a.avg_amount
FROM sales s, AverageSales a
WHERE s.amount > a.avg_amount;

Recursive CTE:

CTEs can be recursive, which is useful for hierarchical data. For example, an employees table with a manager-employee relationship can be used to get the hierarchy of employees.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member
    SELECT employee_id, employee_name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive member
    SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
-- Main query
SELECT * FROM EmployeeHierarchy;

The recursive CTE starts with an anchor member (base result set) and then extends the result set using a recursive member (recursive part) until the complete hierarchy is fetched.

Note: Not all database systems support recursive CTEs, but most major RDBMS like SQL Server, PostgreSQL, and Oracle do.

Creating and Defining CTEs:

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = 1
)
SELECT * FROM EmployeeCTE;

CTE vs. Subqueries in SQL:

-- Using CTE
WITH HighSalaryCTE AS (
    SELECT EmployeeID, FirstName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighSalaryCTE;

-- Equivalent Subquery
SELECT EmployeeID, FirstName, Salary
FROM Employees
WHERE Salary > 50000;

CTEs and JOIN Operations:

WITH EmployeeDepartmentCTE AS (
    SELECT e.EmployeeID, e.FirstName, d.DepartmentName
    FROM Employees e
    INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
)
SELECT * FROM EmployeeDepartmentCTE;

CTEs and UNION, INTERSECT, EXCEPT:

WITH SalesCTE AS (
    SELECT ProductID, SalesAmount
    FROM Sales2019
    UNION
    SELECT ProductID, SalesAmount
    FROM Sales2020
)
SELECT * FROM SalesCTE;

Using CTEs for Hierarchical Data:

WITH RecursiveHierarchyCTE AS (
    SELECT EmployeeID, ManagerID, FirstName, LastName
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.FirstName, e.LastName
    FROM Employees e
    INNER JOIN RecursiveHierarchyCTE r ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveHierarchyCTE;

CTEs with Aggregate Functions in SQL:

WITH DepartmentTotalSalaryCTE AS (
    SELECT DepartmentID, SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT * FROM DepartmentTotalSalaryCTE;

CTEs and Window Functions:

WITH RankBySalaryCTE AS (
    SELECT EmployeeID, FirstName, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
)
SELECT * FROM RankBySalaryCTE;

Multiple CTEs in a Single Query:

WITH HighSalaryCTE AS (
    SELECT EmployeeID, FirstName, Salary
    FROM Employees
    WHERE Salary > 50000
),
SalesCTE AS (
    SELECT ProductID, SalesAmount
    FROM Sales
)
SELECT * FROM HighSalaryCTE
UNION
SELECT * FROM SalesCTE;

CTEs and Subquery Factoring in SQL:

WITH RegionSalesCTE (Region, TotalSales) AS (
    SELECT Region, SUM(SalesAmount)
    FROM Sales
    GROUP BY Region
)
SELECT * FROM RegionSalesCTE;