A Common Table Expression (CTE) in SQL is a powerful tool that allows you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve the readability and organization of complex queries by allowing you to break them into simpler, more manageable parts.

Syntax of CTE

WITH cte_name AS (
-- CTE query
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
-- Main query using the CTE
SELECT *
FROM cte_name
WHERE condition;

Key Features of CTEs

1. Readability: CTEs help improve the readability of SQL queries, especially for complex queries that involve multiple joins or subqueries.

2. Modularity: You can define a CTE once and use it multiple times within the same query.

3. Recursion: CTEs can be recursive, allowing you to work with hierarchical data (e.g., organizational charts, folder structures).

Example of a Simple CTE

Here’s an example demonstrating a simple CTE that retrieves employees and their salaries:

WITH EmployeeCTE AS (
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000
)
SELECT *
FROM EmployeeCTE;

Example of a Recursive CTE

This example demonstrates a recursive CTE to retrieve a hierarchical employee structure:

WITH RecursiveCTE AS (
SELECT EmployeeID, ManagerID, FirstName, LastName
FROM Employees
WHERE ManagerID IS NULL -- Start with top-level managers
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.FirstName, e.LastName
FROM Employees e
INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT *
FROM RecursiveCTE;

Usage Scenarios

Aggregating data: You can use CTEs to aggregate data before using it in the main query.

Breaking complex queries: Simplify complex joins or calculations by breaking them into manageable parts.

Recursion: Handle recursive relationships in data, such as organizational hierarchies.

CTEs are supported by most major relational database management systems, including SQL Server, PostgreSQL, Oracle, and MySQL (from version 8.0).