Home » SQL Server CTE

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

 

 




Recommended Posts..

Recursive CTE

How to delete duplicate records from a table using CTE ?




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.