Common Table Expressions (CTE) Example In SQL SERVER

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

WITH CTE_Name ([Select_Column_List])
AS
(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.

if OBJECT_ID('tempdb..#Employee') is not null
Begin  1
Drop Table #Employee
End

if OBJECT_ID('tempdb..#Dept') is not null
Begin
Drop Table #Dept
End

if OBJECT_ID('tempdb..#EmpDeptRelation') is not null
Begin
Drop Table #EmpDeptRelation
End

if OBJECT_ID('tempdb..#EmpLectures') is not null
Begin
Drop Table #EmpLectures
End


if OBJECT_ID('tempdb..#tempDB') is not null
Begin
Drop Table #tempDB
End


CREATE TABLE #Employee
(
EmpId int,
EmpName varchar(100)
)
Insert Into #Employee values(1,'Rupesh')
Insert Into #Employee values(2,'Ashish')
Insert Into #Employee values(3,'Amol')
Insert Into #Employee values(4,'Vinayak')
Insert Into #Employee values(5,'Shital')
GO
--Select * from #Employee


CREATE TABLE #Dept
(
DeptId int,
DeptName varchar(100)
)
Insert Into #Dept values(1,'IT')
Insert Into #Dept values(2,'Admin')
Insert Into #Dept values(3,'HR')
GO
--Select * from #Dept

CREATE TABLE #EmpDeptRelation
(
EDId int,
EmpId int,
DeptId int,
)
Insert Into #EmpDeptRelation values(1,1,1)
Insert Into #EmpDeptRelation values(2,2,1)
Insert Into #EmpDeptRelation values(3,3,1)
Insert Into #EmpDeptRelation values(4,4,2)
Insert Into #EmpDeptRelation values(5,5,3)
GO
--Select * from #EmpDeptRelation

CREATE TABLE #EmpLectures
(
LId int,
EmpId int,
DeptId int,
LectDt datetime,
)
Insert Into #EmpLectures values(1,1,1,'09-15-2018')
Insert Into #EmpLectures values(2,1,1,'09-16-2018')
Insert Into #EmpLectures values(3,1,1,'09-18-2018')
Insert Into #EmpLectures values(4,2,1,'09-15-2018')
Insert Into #EmpLectures values(5,2,1,'09-16-2018')
Insert Into #EmpLectures values(6,3,1,'09-15-2018')
Insert Into #EmpLectures values(7,3,1,'09-17-2018')
Insert Into #EmpLectures values(8,4,2,'09-17-2018')
Insert Into #EmpLectures values(9,5,3,'09-17-2018')
GO
 --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,

Select * from #Employee
Select * from #Dept
Select * from #EmpDeptRelation
Select * from #EmpLectures

 

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.

WITH EmpDetails AS
    (
     Select E.EmpId, E.EmpName, D.DeptId, D.DeptName
     From #Employee E
     INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId
     INNER JOIN #Dept D on D.DeptId = EDR.DeptId
   )

  Select * from EmpDetails

Now, run the above query. You will get the output as below.

 

 

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.

;WITH EmpDetails AS
(
 Select E.EmpId, E.EmpName, D.DeptId, D.DeptName
 From #Employee E
 INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId
 INNER JOIN #Dept D on D.DeptId = EDR.DeptId
  )
  SELECT  ED.EmpId,
      ED.EmpName,
         ED.DeptId,
      ED.DeptName,
      EL.LectDt
 FROM  #EmpLectures EL
 OUTER APPLY
    (
        SELECT TOP 1 * FROM EmpDetails Where EmpDetails.EmpId = EL.EmpId
    ) 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,

 

 

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.  

;WITH EmpDetails AS
(
 Select E.EmpId, E.EmpName, D.DeptId, D.DeptName
 From #Employee E
 INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId
 INNER JOIN #Dept D on D.DeptId = EDR.DeptId
  )
  SELECT  ED.EmpId,
      ED.EmpName,
      --ED.DeptId,
      --ED.DeptName,
      EL.LectDt
 FROM  #EmpLectures EL
 OUTER APPLY
    (
        SELECT TOP 1 * FROM EmpDetails Where EmpDetails.EmpId = EL.EmpId
    ) AS ED

Now, run the above query. Will get the output as -

 

 

Step 5

 

Suppose user would like to know the total number of lecture counts then will use below query,

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,

;With LectureCount AS
    (
        Select E.EmpId,COUNT(E.DeptId) as [TotalLectures] from #EmpLectures E Group by E.EmpId
    )
    Select E.EmpId, E.EmpName, LC.TotalLectures
    From #Employee E
    Outer Apply
    (
      Select Top 1 LectureCount.TotalLectures From LectureCount where LectureCount.EmpId = E.EmpId
    ) LC

Now, run the above queries. Will get output as,

 

 

 

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,

;WITH EmpDetails AS
    (
     Select E.EmpId, D.DeptId, D.DeptName, E.EmpName
     From #Employee E
     INNER JOIN #EmpDeptRelation EDR ON EDR.EmpId = E.EmpId
     INNER JOIN #Dept D on D.DeptId = EDR.DeptId
   )
  SELECT  ED.EmpId,
          ED.EmpName,
          ED.DeptId,
          ED.DeptName,
          EL.LectDt
    into #tempDB
  FROM  #EmpLectures EL
  OUTER APPLY
        (
            SELECT TOP 1 * FROM EmpDetails Where EmpDetails.EmpId = EL.EmpId
        ) AS ED

Select * from #tempDB

Now apply pivote on above table as,

SELECT *
FROM (
    SELECT
        EmpId,
        EmpName,
        DeptId,DeptName
    FROM #tempDB
) as s
PIVOT
(
    SUM(DeptId)
    FOR [EmpId] IN ([1],[2],[3],[4],[5])
)AS pvt

Now, run both the queries. You will get the output as,

 

 

Summary

 

In this article, you have learned to use the Common Table Expressions(CTE) in SQL Server.