What is a Common Table Expression (CTE) in SQL?
Definition
A Common Table Expression (CTE) in SQL is a temporary named result set that can be referenced in subsequent queries.
Benefits
CTEs offer several benefits, including:
- Simplifying complex queries by breaking them down into smaller, more manageable parts.
- Improving performance by avoiding redundant subqueries.
- Increasing code readability and maintainability.
Syntax
The syntax for creating a CTE is as follows:
WITH cte_name AS ( SELECT ... FROM ... WHERE ... ) SELECT ... FROM cte_name Example
Consider the following query that calculates the total sales for each product category:
WITH ProductSales AS ( SELECT ProductCategory, SUM(Sales) AS TotalSales FROM Sales GROUP BY ProductCategory ) SELECT ProductCategory, TotalSales FROM ProductSales In this example, the CTE named ProductSales is created to store the intermediate result of the subquery. This result set is then referenced in the outer query to calculate the total sales for each product category.
Comments