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:

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] + SPACE(1) + sp2.[LastName] ,
st.[Name] ,
sp1.[SalesQuota] ,
sp1.[Bonus] ,
sp1.[CommissionPct] ,
sp1.[SalesYTD] ,
sp1.[SalesLastYear]
FROM [Sales].[SalesPerson] sp1
INNER JOIN [Sales].[vSalesPerson] sp2
ON sp2.[BusinessEntityID] = sp1.[BusinessEntityID]
INNER JOIN [Sales].[SalesTerritory] st
ON st.[TerritoryID] = sp1.[TerritoryID]
WHERE sp1.[TerritoryID] IS NOT NULL
)
SELECT  *
FROM    cteSalesPerson;

The query inside the CTE returns every salesperson's current and previous years' sales figures. The following are the columns returned by this CTE query: SalesPersonID, FullName, TerritoryName, SalesQuota, Bonus, CommissionPct, SalesYTD, and SalesLastYear.