Common Table Expressions (CTEs)

Common Table Expressions, abbreviated as CTEs, are a handy tool in SQL for creating temporary result sets that can be referenced within a main SQL statement. CTEs provide a way to create structured, organized, and readable queries, especially when dealing with complex subqueries and joins.

Basic Syntax


                            WITH CTE_Name (Column1, Column2, ...)
                            AS (
                                -- Your SQL query goes here
                            )
                            -- Use the CTE in a main query
                            SELECT * FROM CTE_Name;

Example 1: Simple CTE Usage

This example creates a CTE that fetches employees with a specific criteria, and then the main query fetches data from this CTE. 


                            WITH EmployeesCTE AS (
                                SELECT EmployeeID, EmployeeName
                                FROM Employees
                                WHERE Department = 'Sales'
                            )
                            SELECT * FROM EmployeesCTE;

Example 2: Recursive CTE

CTEs can also be used recursively, which is particularly useful in scenarios such as hierarchies or tree structures. This example demonstrates a recursive CTE that navigates an organizational hierarchy to fetch reporting structure. 


                            WITH RecursiveCTE AS (
                                SELECT EmployeeID, EmployeeName, ManagerID
                                FROM Employees
                                WHERE ManagerID IS NULL
                                UNION ALL
                                SELECT e.EmployeeID, e.EmployeeName, e.ManagerID
                                FROM Employees e
                                INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
                            )
                            SELECT * FROM RecursiveCTE;

Conclusion

Common Table Expressions provide an elegant way to organize and structure SQL queries, making them more readable and maintainable. By leveraging CTEs, developers can break down complicated queries into simpler, more understandable parts, enhancing the overall efficiency and clarity of the SQL code.