A CTE is a temporary table that can be reference within Select, update, or Delete statement.
Syntax
To create Comman table expression we below command:
WITH CTE_NAME (column1, column2, ..)
as
(query)
We have an Order table which contains below mentioned records:
We have another table called Stores which have the store information as show below:
Now i want to retrieve the number of order placed for the particular store number with use of CTE expression and then retrieving all the details for the store from the Stores table as shown below.
Creating CTE for retrieving StoreOrders
With StoreOrders(STOREID,TotalOrder)
as
(select STOREID,count(STOREID) as TotalOrder
from [dbo].[Orders]
group by STOREID
)
Now retrieving store information from the CTE Table and Stores table.
select * from StoreOrders
inner join [dbo].[stores]
on StoreOrders.STOREID=[stores].[id]
The CTE Table should be referenced within the select command which immediately follow the CTE Table. If not than select expression will not work, as shown below:
Multiple CTE Tables
We can create multiple CTE Table one after the another and use them within select statement.
In order to demonstrate the same let’s take an example:
We have two order table which is Electronic device while other Order table for Kirana. So now we will retrieve the total no of orders placed based on the storeId, below are the sample table records.
Electronics Orders
Kirana Orders
Now we will create two cte to retrieve the total number of Order placed for Electronics and Kirana.
with ElectronicOrders(STOREID,TotalOrder)
as
(
select STOREID,count(STOREID) as TotalOrder
from [dbo].[Orders] inner join
[dbo].[stores]
on [dbo].[Orders].STOREID=[stores].[id]
group by STOREID
),
KiranaOrders(STOREID,TotalOrder)
as
(
select STOREID,count(STOREID) as TotalOrder
from [dbo].[Order] inner join [dbo].[stores]
on [dbo].[Order].STOREID=cast ([dbo].[stores].[id] as nvarchar(20))
group by STOREID
)
select * from ElectronicOrders
union
select * from KiranaOrders
So as shown above we can create two table expression by using single With expression of CTE with (,) separated.
Now with the CTE the questions comes whether the CTE are updatable as Views?>
The answer is “Yes and No” depending upon the scenario where we can update the same. Lets take a scenario where we can update the same.
So here we have simple CTE expression
WITH Store_Name_Owner
as
(
select ID,STORENAME,STOREOWNER FROM stores
)
SELECT * FROM Store_Name_Owner
Let’t try and update the same.
WITH Store_Name_Owner
as
(
select ID,STORENAME,STOREOWNER FROM stores
)
update Store_Name_Owner set storeowner='Saillesh Pawar' where id=3267
So we have conclude that if have CTE table on one base table than we can update the CTE which in turn updates the base table. If we have multple base table in CTE but update allows updating only one base table than also update is allowed but not as expected.
Trying to update multiple base table CTE
WITH OrdersByStore
as
(
select [Order].ID,ORDERCREATEDDATE,storename,storeowner from [Order] inner join
stores on [Order].STOREID=CAST (stores.id as nvarchar(10))
)
update OrdersByStore set ORDERCREATEDDATE=GETDATE(),storename='Virender' where id=2
So we conclude if CTE is based on multiple table and the update affects multiple table than update doesn’t happen as shown above.
Recursive CTE
CTE provide great advantage by being able to self reference like self join in order to create Recuresive CTE. A recursive CTE is a CTE which executes itself based on the base query and than returning the subset data.
We have following table named tblPlayers of a team which consist of Coaches and players. There is one senior coach, multiple junior coach and rest are players under junior coaches as shown below:
So we want to disply the PlayerName,Coach and their Level with help of CTE.
We have a below hierarchy for the Players with their respective Coach. Shailender Panwar is main Head Coach. So Level wise Shailender Panwar level is 1, than Ravindra Negi and Nitin Negi level are 2 while others are at level 3.
with
PlayersCte(PlayerId,PlayerName,CoachId,[Level])
as
(
select PlayerId,PlayerName,CoachId,1 from tblPlayer where coachid is null
union All
select tblplayer.PlayerId,tblplayer.PlayerName,tblplayer.CoachId,PlayersCte.[Level]+1
from tblplayer join PlayersCte
on tblplayer.CoachId=PlayersCte.PlayerId
)
select PlayersCTE.PlayerName,ISNULL(CoachCTE.PlayerName,'Head Coach') as Coach,PlayersCTE.[Level] from
PlayersCte PlayersCTE
left join PlayersCte CoachCTE on PlayersCTE.CoachId=CoachCTE.PlayerId
In CTE what we did is first separated the Head Coach which has CoachId as null than returned and increment Level for each respective players based on the CTE level value.
Once we receieve the records we need to do self join to display the “Head Coach” in case of Null with self CTE join on coachId eqauls PlayerId.