What is CTE (Common Table Expression)?

A Common Table Expression (CTE) is a temporary, named result set that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). It's defined using the WITH clause and exists only for the duration of the query's execution.

What is a CTE?

CTEs can be thought of as temporary views that are available for a single query. They provide a way to break down complex queries into smaller, more readable, and manageable logical units. Once defined, a CTE can be referenced multiple times within the same query, improving readability and sometimes performance by allowing the database to optimize the subquery more effectively.

They are particularly useful for enhancing the modularity and clarity of complex SQL statements, especially when dealing with subqueries that need to be reused or when performing recursive queries.

Benefits of Using CTEs

• Improved Readability: Complex queries are easier to understand when broken down into logical, named sections.
• Reusability: A CTE can be referenced multiple times within the same query without rewriting its definition.
• Simplified Complex Logic: They help in organizing multi-step calculations or data transformations.
• Recursive Queries: CTEs are the standard way to write recursive queries in SQL.
• Alternative to Subqueries and Views: CTEs offer a more readable alternative to deeply nested subqueries and are more flexible than views as they are temporary.

Syntax of a CTE

A CTE is defined using the WITH clause, followed by the name of the CTE, an optional list of column names, and then the query that defines the CTE's result set. Multiple CTEs can be chained together, separated by commas.

WITH <cte_name> (column1, column2, ...)
AS
(
    -- CTE definition query
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
-- Main query that references the CTE
SELECT *
FROM <cte_name>
WHERE another_condition;

Example Usage

Consider a scenario where you want to find the average order value for each customer and then identify customers whose average order value is above the overall average.

WITH CustomerOrderAverages AS (
    SELECT 
        CustomerID,
        AVG(OrderTotal) AS AverageOrderValue
    FROM Orders
    GROUP BY CustomerID
),
OverallAverage AS (
    SELECT 
        AVG(OrderTotal) AS GlobalAverageOrderValue
    FROM Orders
)
SELECT 
    coa.CustomerID,
    coa.AverageOrderValue
FROM 
    CustomerOrderAverages coa,
    OverallAverage oa
WHERE 
    coa.AverageOrderValue > oa.GlobalAverageOrderValue;

In this example, CustomerOrderAverages calculates each customer's average order value. OverallAverage calculates the average order value across all orders. The final SELECT statement then combines these two CTEs to filter for customers whose average is higher than the global average, demonstrating how CTEs make multi-step queries more organized.

Recursive CTEs

A special and powerful application of CTEs is for defining recursive queries. A recursive CTE references itself within its own definition, allowing it to iterate through hierarchical or tree-structured data (e.g., organizational charts, bill of materials) until a base condition is met. This is achieved using WITH RECURSIVE <cte_name> AS (...) syntax.