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..
How to delete duplicate records from a table using CTE ?
4,092 total views, 2 views today