Common Table Expressions
- a temporary resultset that can be defined within the execution scope of a single SELECT/INSERT/UPDATE/DELETE/CREATE VIEW.
- not stored as an object and lasts for the duration of the query
- self-referencing; can be referenced multiple times within the same query
WITH <CTE_NAME>[columns...]
AS
(
<Query Definition>
)
SELECT <column list>
FROM <CTE_NAME>
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
Structure of a CTE
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE.
WITH Sales_CTE(SalesPId, NumOrders, MaxDate)
AS
(
SELECT SalesPersonID, Count(*), Max(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EID, OS.NumOrders, OS.MaxDate, E.MgrID,
OM.NumOrders, OM.MaxDate
FROM HR.Employee E
JOIN Sales_CTE OS ON E.EID = OS.SalesPId
LEFT OUTER JOIN Sales_CTE OM ON E.MgrID = OM.SalesPId
ORDER BY E.EID
