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

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

What is a common table expression (CTE)?

A Cte is a temporary result specified within a clause that can be referred to within the next select, enter, update, or delete the statement. It improves query reading and is particularly beneficial for recursive operations.

Most common situations to use CTEs

1. Slap -Separate complex queries

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

Example: Multiple Steps Integration

Think of the calculation:

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

With the 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 the CTEs? Each step is isolated and easier to change OI -debug, unlike deep nested subqueries.

2. The reuse within the same query

If a subquery is used several times within a query, a CTE can be determined once and has been referred to several times, improving the ability to read and performance.

Example: With 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 the 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 the CTEs? Reuse reduces redundancy and ensures the same in logic.

3. Temporary results without creating tables

CTEs act as a temporary, inline table that exists only for query duration. Unlike temporary tables, you do not need additional DDL (for example, create a table) or cleaning (for example, drop table).

4. Improved Reading Query

When working with complex queries, especially those involving many subqueries or joining, making the query more readable and maintaining the query.

Conclusion

CTEs are a powerful feature that simplifies Query Writing in SQL, especially for recursive operations, hierarchical data, and collapse of complex logic.

Thanks for taking the time to explore the views related to the data. I appreciate your relationship. If this information is worth it, I invite you to follow me or connect me to LinkedIn. Happy exploration! 👋