SQL Server CTE

SQL Server CTE (Common Table Expression) is temporary result set that is used to reference within another SELECT, INSERT, UPDATE, or DELETE statement.

CTE always returns a result set. It provides a easy way to deal with complex Queries , subqueries, or multiple joins.

SQL server provides one more type of CTE that is Recursive CTE.

Syntax

WITH expression_name[(column_name [,...])]
AS
  (CTE_definition)
Sql code;

Lets Look at an example of CTE in SQL.

SQL SERVER CTE

Following Statement uses CTE to returns the employee whose first name contains the letter ‘G’ .

With CTE_EmpDetails

AS ( SELECT *

FROM DimEmployee E where FirstName like '%G%'
)

SELECT * FROM CTE_EmpDetails

 

Using CTE As a View

When we define the CTE we give the result a name as well its columns.  In this way a CTE acts like a VIEW.

Lets look at an example to define a columns to CTE .

Following statement uses the CTE to returns the details of Employees and their Sales Territory details.

WITH CTE_EmpSalesDetails

( EmployeeKey , FirstName , LastName , Title, SalesTerritoryKey,

SalesTerritoryRegion, SalesTerritoryCountry, SalesTerritoryGroup )

AS (

SELECT E.EmployeeKey , E.FirstName , E.LastName ,

E.Title, S.SalesTerritoryKey, S.SalesTerritoryRegion,

S.SalesTerritoryCountry, S.SalesTerritoryGroup

FROM DimEmployee E INNER JOIN DimSalesTerritory S

ON E.SalesTerritoryKey=S.SalesTerritoryKey

)

SELECT * FROM CTE_EmpSalesDetails WHERE SalesTerritoryKey in ( 1,2,4,5,6)

 

 

An Example of Using Multiple CTE

Following statement uses the multiple CTE and join them to returns the details of Employees and their Sales Territory details.

WITH CTE_EmpSalesDetails ( EmployeeKey, FirstName, LastName, Title, 
SalesTerritoryKey,SalesTerritoryRegion, SalesTerritoryCountry, 
SalesTerritoryGroup )

AS ( SELECT E.EmployeeKey, E.FirstName, E.LastName, E.Title, S.SalesTerritoryKey, 
S.SalesTerritoryRegion, S.SalesTerritoryCountry, S.SalesTerritoryGroup
FROM DimEmployee E INNER JOIN DimSalesTerritory S
ON E.SalesTerritoryKey=S.SalesTerritoryKey
) ,

CTE_EmpFilter ( EmployeeKey )

AS ( SELECT E.EmployeeKey

FROM DimEmployee E where FirstName like '%G%'
)

SELECT * FROM CTE_EmpSalesDetails CTE_ES INNER JOIN CTE_EmpFilter CTE_E

ON CTE_ES.EmployeeKey = CTE_E.EmployeeKey

 

 

Also Read..

Recursive CTE

How to delete duplicate records from a table using CTE ?