Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve query readability, modularity, and allow recursive operations.

CTEs are often used to simplify complex joins, subqueries, or recursive queries.

Syntax

The basic syntax of a CTE is:

WITH cte_name (column1, column2, ...)
AS
(
    SQL query
)
SELECT *
FROM cte_name;

• cte_name: Name you give to the CTE.
• column1, column2: Optional column names for the CTE.
• SQL query: The query whose results will be stored in the CTE.

Key Features

• Improves readability of complex SQL queries.
• Can be recursive for hierarchical data.
• Temporary in scope; only exists during the execution of the query.
• Can be used multiple times in the same query.

Example 1: Simple CTE

Suppose we have an Employees table. We want to select employees with a salary above 50000.

WITH HighSalary AS
(
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT *
FROM HighSalary;

Example 2: CTE with Aggregation

Calculate the total salary for each department.

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

Example 3: Recursive CTE

List all employees and their managers in a hierarchy.

WITH EmployeeHierarchy AS
(
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL    UNION ALL    SELECT e.EmployeeID, e.Name, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.ManagerID = eh.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

Best Practices

• Use meaningful names for CTEs.
• Keep CTEs simple; avoid very large or unnecessary CTEs.
• Use recursive CTEs only when necessary for hierarchical data.
• Consider performance, as CTEs are not indexed and may impact execution time for large datasets.

Conclusion

CTEs are a powerful SQL feature for breaking down complex queries into readable, reusable components. They are particularly useful for hierarchical data and modular query design.