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.