A common table expression (CTE) is a temporary result set that your query can reference. You can use a CTE just as you would any other table. However, when the query ends, the CTE is deleted from the memory. We also use CTEs to create recursive queries, simplify complex query logic, and create multiple references of the same table.
To create a CTE, use a WITH
clause outside the SELECT
statement. The following is the basic syntax of a CTE:
WITH cte_name ([(column_name [,...n])]) AS (CTE_query_definition)
The following is an explanation of the arguments of the CTE syntax:
cte_name
: This is the name of the CTE you have referenced in the querycolumn_name
: This is the name of the column; note that it is an optional argument
The following is an example of the structure of a CTE:
WITH cteSalesPerson ( [SalesPersonID], [FullName], [TerritoryName], [SalesQuota], [Bonus], [CommissionPct], [SalesYTD], [SalesLastYear] ) AS ( SELECT sp1.[BusinessEntityID] , sp2.[FirstName] ...