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
Readability: CTEs help improve the readability of SQL queries, especially for complex queries that involve multiple joins or subqueries.
Modularity: You can define a CTE once and use it multiple times within the same query.
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).
Top comments (0)