Common Table Expressions (CTE) Example In SQL SERVER

A common table expression i.e CTE which is used to the specific temporary result set by using SELECT, INSERT, UPDATE, or DELETE statement. So the user can do further operations on it. When the user uses joins queries on some particular table & in sub-queries, he needs the same set of records then code readability is slightly difficult, so use of CTE is makes code readability makes better.

Introduction
 
A common table expression, CTE, is used to a specific temporary result set by using SELECT, INSERT, UPDATE, or DELETE statement so, that the user can perform further operations on it. When the user uses Join queries on some particular table & in sub-queries, he needs the same set of records then the code readability is slightly difficult. So the use of CTE makes the code readability better. Common Table Expressions (CTE) have two types, recursive and non-recursive. For beginners, it is easy to understand.
 
Syntax
  1. WITH CTE_Name ([Select_Column_List])  
  2. AS  
  3. (Your_Query_Definition)  
  • CTE_Name is common table expression name.
  • Select_Column_List is column name list which user would like to select.
  • Your_Query_Definition - Specifies a SELECT statement whose result set populates the common table expressions.
For more info, click here.
  
Step 1 
 
I am going to create temp tables as follows Employee, Department, relationship employee & department i.e. EmpDeptRelation, an employee who conducted the number of lectures in the EmpLectures table.
 
First, we are going to check these temp tables are already present or not. If present, then drop that tables. Also, after the creation of tables, we need to insert some dummy data into that.
  1. if OBJECT_ID('tempdb..#Employee'is not null  
  2. Begin  1
  3. Drop Table #Employee  
  4. End    
  5.   
  6. if OBJECT_ID('tempdb..#Dept'is not null  
  7. Begin  
  8. Drop Table #Dept  
  9. End    
  10.   
  11. if OBJECT_ID('tempdb..#EmpDeptRelation'is not null  
  12. Begin  
  13. Drop Table #EmpDeptRelation  
  14. End   
  15.   
  16. if OBJECT_ID('tempdb..#EmpLectures'is not null  
  17. Begin  
  18. Drop Table #EmpLectures  
  19. End   
  20.   
  21.   
  22. if OBJECT_ID('tempdb..#tempDB'is not null  
  23. Begin  
  24. Drop Table #tempDB  
  25. End   
  26.   
  27.   
  28. CREATE TABLE #Employee  
  29. (  
  30. EmpId int,  
  31. EmpName varchar(100)  
  32. )  
  33. Insert Into #Employee values(1,'Rupesh')  
  34. Insert Into #Employee values(2,'Ashish')  
  35. Insert Into #Employee values(3,'Amol')  
  36. Insert Into #Employee values(4,'Vinayak')  
  37. Insert Into #Employee values(5,'Shital')  
  38. GO  
  39. --Select * from #Employee  
  40.   
  41.   
  42. CREATE TABLE #Dept  
  43. (  
  44. DeptId int,  
  45. DeptName varchar(100)  
  46. )  
  47. Insert Into #Dept values(1,'IT')  
  48. Insert Into #Dept values(2,'Admin')  
  49. Insert Into #Dept values(3,'HR')  
  50. GO  
  51. --Select * from #Dept  
  52.   
  53. CREATE TABLE #EmpDeptRelation  
  54. (  
  55. EDId int,  
  56. EmpId int,  
  57. DeptId int,  
  58. )  
  59. Insert Into #EmpDeptRelation values(1,1,1)  
  60. Insert Into #EmpDeptRelation values(2,2,1)  
  61. Insert Into #EmpDeptRelation values(3,3,1)  
  62. Insert Into #EmpDeptRelation values(4,4,2)  
  63. Insert Into #EmpDeptRelation values(5,5,3)  
  64. GO  
  65. --Select * from #EmpDeptRelation  
  66.   
  67. CREATE TABLE #EmpLectures  
  68. (  
  69. LId int,  
  70. EmpId int,  
  71. DeptId int,  
  72. LectDt datetime,  
  73. )  
  74. Insert Into #EmpLectures values(1,1,1,'09-15-2018')  
  75. Insert Into #EmpLectures values(2,1,1,'09-16-2018')  
  76. Insert Into #EmpLectures values(3,1,1,'09-18-2018')  
  77. Insert Into #EmpLectures values(4,2,1,'09-15-2018')  
  78. Insert Into #EmpLectures values(5,2,1,'09-16-2018')  
  79. Insert Into #EmpLectures values(6,3,1,'09-15-2018')  
  80. Insert Into #EmpLectures values(7,3,1,'09-17-2018')  
  81. Insert Into #EmpLectures values(8,4,2,'09-17-2018')  
  82. Insert Into #EmpLectures values(9,5,3,'09-17-2018')  
  83. GO  
  84.  --Select * from #EmpLectures  
Step 2
 
As per the above syntax of CTE, we will create some queries & execute that queries.
 
Firstly will fire only select queries on each table to check the records as,
  1. Select * from #Employee  
  2. Select * from #Dept  
  3. Select * from #EmpDeptRelation  
  4. Select * from #EmpLectures  
 Common Table Expressions (CTE) Example In SQL SERVER
Step 3
 
Now, let's say the user wants to get some data, such as the information of employee id, employee name, department ID & name from above tables. Then, we will use the join clause and as per the above CTE syntax, it will create CTE & will execute it as.
  1. WITH EmpDetails AS   
  2.     (  
  3.      Select E.EmpId, E.EmpName, D.DeptId, D.DeptName  
  4.      From #Employee E   
  5.      INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId  
  6.      INNER JOIN #Dept D on D.DeptId = EDR.DeptId  
  7.    )   

  8.   Select * from EmpDetails   
Now, run the above query. You will get the output as below.
 
Common Table Expressions (CTE) Example In SQL SERVER 
Step 4
 
Suppose, the user would like to get all lectures conducted by the employee and result set will be like Employee ID, employee name, department ID, department name, lecture date etc. Then in this case we will use above CTE from step 2 and apply into outer CTE and employee lecture table so will compare employee ID to select records.
  1. ;WITH EmpDetails AS   
  2. (  
  3.  Select E.EmpId, E.EmpName, D.DeptId, D.DeptName  
  4.  From #Employee E   
  5.  INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId  
  6.  INNER JOIN #Dept D on D.DeptId = EDR.DeptId  
  7.   )   
  8.   SELECT  ED.EmpId,  
  9.       ED.EmpName,  
  10.          ED.DeptId,  
  11.       ED.DeptName,  
  12.       EL.LectDt      
  13.  FROM  #EmpLectures EL  
  14.  OUTER APPLY   
  15.     (  
  16.         SELECT TOP 1 * FROM EmpDetails Where EmpDetails.EmpId = EL.EmpId  
  17.     ) AS ED  
Here, in the above query, I have created CTE name EmpDetails & this will be used further to get records/columns as EmpId,EmpName,DeptId, DeptName.
 
Now, run the above query and will get output as,
 
Common Table Expressions (CTE) Example In SQL SERVER
 
Note
Here is no necessity to select all columns names in the select query those who are used in CTE as in below query I have commented the DeptId, DeptName.  
  1. ;WITH EmpDetails AS   
  2. (  
  3.  Select E.EmpId, E.EmpName, D.DeptId, D.DeptName  
  4.  From #Employee E   
  5.  INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId  
  6.  INNER JOIN #Dept D on D.DeptId = EDR.DeptId  
  7.   )   
  8.   SELECT  ED.EmpId,  
  9.       ED.EmpName,  
  10.       --ED.DeptId,  
  11.       --ED.DeptName,  
  12.       EL.LectDt  
  13.  FROM  #EmpLectures EL  
  14.  OUTER APPLY   
  15.     (  
  16.         SELECT TOP 1 * FROM EmpDetails Where EmpDetails.EmpId = EL.EmpId  
  17.     ) AS ED  
Now, run the above query. Will get the output as -
 
Common Table Expressions (CTE) Example In SQL SERVER
 
Step 5
 
Suppose user would like to know the total number of lecture counts then will use below query,
  1. Select E.EmpId,COUNT(E.DeptId) as [TotalLectures] from #EmpLectures E Group by E.EmpId  
To get other details will use below query using CTE,
  1. ;With LectureCount AS  
  2.     (  
  3.         Select E.EmpId,COUNT(E.DeptId) as [TotalLectures] from #EmpLectures E Group by E.EmpId  
  4.     )  
  5.     Select E.EmpId, E.EmpName, LC.TotalLectures   
  6.     From #Employee E  
  7.     Outer Apply  
  8.     (  
  9.       Select Top 1 LectureCount.TotalLectures From LectureCount where LectureCount.EmpId = E.EmpId  
  10.     ) LC  
Now, run the above queries. Will get output as,
 
Common Table Expressions (CTE) Example In SQL SERVER 
 
Step 6
 
Now suppose, there is some requirement where you need to show the above data into row format using the PIVOT table. I will take data into some temp table from Step 3 same like & execute a selected query on the temp table as,
  1. ;WITH EmpDetails AS   
  2.     (  
  3.      Select E.EmpId, D.DeptId, D.DeptName, E.EmpName  
  4.      From #Employee E   
  5.      INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId  
  6.      INNER JOIN #Dept D on D.DeptId = EDR.DeptId  
  7.    )   
  8.   SELECT  ED.EmpId,  
  9.           ED.EmpName,  
  10.           ED.DeptId,  
  11.           ED.DeptName,  
  12.           EL.LectDt  
  13.     into #tempDB   
  14.   FROM  #EmpLectures EL  
  15.   OUTER APPLY   
  16.         (  
  17.             SELECT TOP 1 * FROM EmpDetails Where EmpDetails.EmpId = EL.EmpId  
  18.         ) AS ED  
  1. Select * from #tempDB  
Now apply pivote on above table as,
  1. SELECT *  
  2. FROM (  
  3.     SELECT   
  4.         EmpId,  
  5.         EmpName,  
  6.         DeptId,DeptName  
  7.     FROM #tempDB   
  8. as s  
  9. PIVOT  
  10. (  
  11.     SUM(DeptId)  
  12.     FOR [EmpId] IN ([1],[2],[3],[4],[5])  
  13. )AS pvt   
Now, run both the queries. You will get the output as,
 
Common Table Expressions (CTE) Example In SQL SERVER
 
Summary
 
In this article, you have learned to use the Common Table Expressions(CTE) in SQL Server.