Common Table Expressions (CTEs)

1. Basic Syntax of CTE

The syntax for a CTE is as follows:

Edit Copy

WITH cte_name AS (
    -- CTE Query
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

In this syntax, cte_name is the name of the CTE, and the CTE query can be any valid SQL query.

2. Example of a Simple CTE

Here’s a simple example that demonstrates a CTE:

Edit Copy

WITH EmployeeCTE AS (
    SELECT id, name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM EmployeeCTE;

This CTE named EmployeeCTE retrieves all employees from the Sales department.

3. CTE with Aggregation

CTEs can also be used with aggregation functions:

Edit Copy

WITH SalaryCTE AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM SalaryCTE
WHERE avg_salary > 50000;

This example calculates the average salary per department and selects those departments where the average salary is greater than 50,000.

4. Recursive CTEs

CTEs can also be recursive, allowing you to work with hierarchical data. Here’s an example:

Edit Copy

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL  -- Starting point (top-level managers)
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

This recursive CTE retrieves all employees along with their hierarchy based on the manager-employee relationship.

5. Benefits of Using CTEs

• Improved Readability: Break complex queries into smaller, manageable parts.
• Modularity: Define reusable queries that can be referenced multiple times within a single execution.
• Recursive Queries: Easily work with hierarchical data using recursive CTEs.
• Temporary Data Representation: Represent temporary result sets that do not need to be stored in the database.

6. Conclusion

Common Table Expressions (CTEs) are a powerful feature in SQL that can greatly enhance the readability and maintainability of your queries. Whether for simple queries or complex recursive data retrieval, CTEs provide a flexible way to work with temporary result sets.