Day 14: Common Table Expressions (CTEs) and Recursive Queries

Day 14: Common Table Expressions (CTEs) and Recursive Queries

    1. Common Table Expressions (CTEs)

            A Common Table Expression (CTE) is a temporary result set that simplifies complex queries. It exists only during the execution of the query.

   2. Syntax of a CTE

            SQL

                WITH CTE_Name (Column1, Column2, ...)
                AS
                (
                    SELECT Column1, Column2
                    FROM TableName
                    WHERE Condition
                )
                SELECT * FROM CTE_Name; 

    3. Example of a CTE

                Simple CTE:

                    SQL

                        WITH EmployeeCTE AS
                        (
                            SELECT EmployeeID, Name, Salary
                            FROM Employees
                            WHERE Salary > 5000
                        )
                        SELECT * FROM EmployeeCTE;
 

    4. Recursive CTE

            A recursive CTE refers to itself and is commonly used to query hierarchical data like organizational charts or folder structures.

            Syntax:

                SQL

                    WITH RecursiveCTE (Column1, Column2, ...)
                    AS
                    (
                        -- Anchor member
                        SELECT Column1, Column2
                        FROM TableName
                        WHERE Condition

                        UNION ALL

                        -- Recursive member
                        SELECT Column1, Column2
                        FROM TableName
                        INNER JOIN RecursiveCTE
                        ON TableName.ParentID = RecursiveCTE.ID
                    )
                    SELECT * FROM RecursiveCTE; 

    5. Example of a Recursive CTE

        Hierarchy of Employees:

            SQL

                WITH EmployeeHierarchy AS
                (
                    -- Anchor member
                    SELECT EmployeeID, ManagerID, Name
                    FROM Employees
                    WHERE ManagerID IS NULL

                    UNION ALL

                    -- Recursive member
                    SELECT e.EmployeeID, e.ManagerID, e.Name
                    FROM Employees e
                    INNER JOIN EmployeeHierarchy eh
                    ON e.ManagerID = eh.EmployeeID
                )
                SELECT * FROM EmployeeHierarchy;

    6. Key Points to Remember

1. Use CTEs to break down complex queries for better readability.
2. Recursive CTEs must include:
1. An anchor member (base case).
2. A recursive member with a termination condition (e.g., ManagerID IS NULL).
3. Recursive queries must include a UNION ALL operator.

    7. Benefits of CTEs

1. Improved query readability.
2. Simplifies hierarchical or recursive queries.
3. Can be referenced multiple times within the same query.

Action Steps

1. Write a simple CTE to filter data from a table.
2. Create a recursive CTE to display a hierarchical structure like an organization chart.
3. Test your recursive CTE with a termination condition to avoid infinite loops.