A Common Table Expression, or CTE, is a named temporary result set defined using WITH. A CTE can be joined with other tables within the same SQL statement.
CTEs make complex JOIN queries easier to organize, read, test, and maintain.
Create a CTE containing customer sales totals and join it to customer information.
WITH CustomerSales AS (
SELECT
CustomerID,
SUM(OrderAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID
)
SELECT
c.CustomerName,
cs.TotalSales
FROM Customers c
INNER JOIN CustomerSales cs
ON c.CustomerID = cs.CustomerID;Used in reporting, data transformation, analytics, ranking, and complex business logic.
- CTEs can be used as JOIN sources.
- WITH defines the CTE.
- CTEs improve query readability.
- Aggregation can be performed inside a CTE.
- A CTE exists for the duration of the statement.
What is a CTE?
A CTE is a named temporary result set defined with the WITH clause and used within a SQL statement.
Why use a CTE with JOIN?
A CTE can separate complex preparation logic from the final JOIN, making the query easier to understand.