SQL Recursive Queries - Understanding Common Table Expressions (CTEs)

Recursive queries in SQL, enabled by Common Table Expressions (CTEs), allow us to work with hierarchical or recursive data structures such as employee-manager relationships, family trees, or file directories. This article introduces the WITH clause and demonstrates how recursive CTEs can simplify these operations.

What is a Common Table Expression (CTE)?

A CTE is a temporary result set defined within a WITH clause that can be referenced within the subsequent SELECT, INSERT, UPDATE, or DELETE statement. It improves query readability and is particularly useful for recursive operations.

Most Common Situations to Use CTEs

1. Breaking Down Complex Queries

CTEs allow you to break down a complex query into smaller, manageable parts. Each part of the query can be defined as a separate CTE, making the overall logic easier to follow.

Example: Multi-Step Aggregations

Imagine calculating:

1. Total sales by region.
2. Regions with total sales above $2000.
3. Combining this data with a list of regions.

With CTE:

WITH RegionalSales AS (
    SELECT Region, SUM(Amount) AS TotalSales
    FROM Sales
    GROUP BY Region
),
HighPerformingRegions AS (
    SELECT Region
    FROM RegionalSales
    WHERE TotalSales > 2000
)
SELECT r.Region, rs.TotalSales
FROM Regions r
LEFT JOIN RegionalSales rs ON r.Region = rs.Region
WHERE r.Region IN (SELECT Region FROM HighPerformingRegions);

Why CTEs? Each step is isolated and easier to modify or debug, unlike deeply nested subqueries.

2. Reusability Within the Same Query

If a subquery is used multiple times within a query, a CTE can be defined once and referenced multiple times, improving readability and performance.

Example: Using the Same Subquery

Without CTE:

SELECT AVG(Salary) AS AvgSalary
FROM (
    SELECT DepartmentID, AVG(Salary) AS AvgDepartmentSalary
    FROM Employees
    GROUP BY DepartmentID
) AS SubQuery1;

SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > (
    SELECT AVG(Salary)
    FROM (
        SELECT DepartmentID, AVG(Salary) AS AvgDepartmentSalary
        FROM Employees
        GROUP BY DepartmentID
    ) AS SubQuery2
);

With CTE:

WITH DepartmentAverages AS (
    SELECT DepartmentID, AVG(Salary) AS AvgDepartmentSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT AVG(AvgDepartmentSalary) AS OverallAverage
FROM DepartmentAverages;

WITH DepartmentAverages AS (
    SELECT DepartmentID, AVG(Salary) AS AvgDepartmentSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT DepartmentID, AvgDepartmentSalary
FROM DepartmentAverages
WHERE AvgDepartmentSalary > (
    SELECT AVG(AvgDepartmentSalary) FROM DepartmentAverages
);

Why CTEs? Reusability reduces redundancy and ensures consistency in logic.

3. Temporary Results Without Creating Tables

CTEs act as a temporary, inline table that exists only for the duration of the query. Unlike temporary tables, you don’t need additional DDL (e.g., CREATE TABLE) or cleanup (e.g., DROP TABLE).

4. Improved Query Readability

When working with complex queries, especially those involving multiple subqueries or joins, CTEs make the query easier to read and maintain.

Conclusion

CTEs are a powerful feature that simplifies SQL query writing, especially for recursive operations, hierarchical data, and breaking down complex logic.

---

Explore more

Luca LiuFollow

Hello there! 👋 I'm Luca, a Business Intelligence Developer with passion for all things data. Proficient in Python, SQL, Power BI, Tableau, SAP Business Objects.

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

🚀 Connect with me on LinkedIn