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. Common Table Expressions (CTEs) 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. CTE 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 Example: Multi-Step Aggregations Imagine calculating: Total sales by region.
Regions with total sales above $2000.
Combining this data with a list of regions. Total sales by region. Regions with total sales above $2000. 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); 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. Why CTEs? 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 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
); 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
); 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. Why CTEs? 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. Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋 LinkedIn

SQL Recursive Queries - How to Understand Common Table Expressions (CTEs)

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

A Quick Guide to Listing All Measures in Power BI via DAX Studio

How to Write Complex Queries in Apache Spark SQL Using CTE (WITH Clause)

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

A Quick Guide to Listing All Measures in Power BI via DAX Studio

How to Write Complex Queries in Apache Spark SQL Using CTE (WITH Clause)

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps