Common Table Expression (CTE) in SQL

A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword and exists only for the duration of the query in which it is defined.

Key Features:

1. Readable and Reusable: Simplifies complex queries by breaking them into smaller, logical building blocks.
2. Temporary: The CTE exists only within the scope of the query and does not persist in the database.
3. Recursive Support: Allows recursive queries, which are useful for hierarchical data like organizational structures or tree-like data.

---

Syntax:

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;

---

Example 1: Simple CTE

Find the employees with salaries greater than the average salary.

WITH AvgSalary AS (
    SELECT AVG(Salary) AS AverageSalary
    FROM Employees
)
SELECT EmployeeID, Salary
FROM Employees, AvgSalary
WHERE Salary > AverageSalary;

---

Example 2: Recursive CTE

Find all levels of a reporting hierarchy.

WITH Hierarchy AS (
    SELECT EmployeeID, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL  -- Start with top-level managers
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, h.Level + 1
    FROM Employees e
    INNER JOIN Hierarchy h
    ON e.ManagerID = h.EmployeeID
)
SELECT * 
FROM Hierarchy;

---

Use Cases:

1. Simplifying complex queries by structuring them into smaller, manageable parts.
2. Computing aggregates or ranks for temporary use in a query.
3. Recursive data processing, such as traversing hierarchical relationships.

CTEs make SQL queries more modular, maintainable, and easier to debug.