SQL Server – Common Table Expression

By | 02/02/2022

In this post, we will see how to use a Common Table Expression (CTE) in Sql Server.
But first of all, what is a Common Table Expression?
From Microsoft web site:
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.
In a nutshell, a CTE is a temporary result set that we can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

We open Sql Server Management Studio and we import the DB AdventureWorks that we will use in this post.
We start taking the table HumanResources.Employee and we run this query:

SELECT TOP (1000) [BusinessEntityID]
      ,[NationalIDNumber]
      ,[LoginID]
      ,[OrganizationNode]
      ,[OrganizationLevel]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate]
 FROM [AdventureWorks2019].[HumanResources].[Employee]


Now, using CTE, we will run different queries:

IMPORTANT!
IN THIS POST, WE WILL RUN EASY QUERY THAT IN REALITY WE COULD CREATE WITHOUT CTE BUT, THE PURPOSE OF THIS POST IS ONLY TO SHOW HOW USING CTE.
THE COMPLEXITY OF SELECTS ISN’T THE GOAL OF THIS POST.


TAKING INFORMATION FOR FEMALE EMPLOYEES

-- FemaleEmployee is the name of the CTE
-- job, datebirth and sex, are the FemaleEmployee's properties 
with FemaleEmployee (job, datebirth, sex)
AS
(
	select JobTitle, BirthDate, 'F' from HumanResources.Employee
	where Gender = 'F'
)
select * from FemaleEmployee



TAKING INFORMATION FOR FEMALE AND MALE EMPLOYEES

-- FemaleEmployee is the name of the CTE
-- job, datebirth and sex, are the FemaleEmployee's properties 
with FemaleEmployee (job, datebirth, sex)
AS
(
	select JobTitle, BirthDate, 'F' from HumanResources.Employee
	where Gender = 'F'
)
-- we have to use ',' to use multiple CTE without the word 'with'
,
-- MaleEmployee is the name of the CTE
-- job, datebirth and sex, are the FemaleEmployee's properties 
MaleEmployee (job, datebirth, sex)
AS
(
	select JobTitle, BirthDate, 'M' from HumanResources.Employee
	where Gender = 'M'
)

select * from FemaleEmployee
union
select * from MaleEmployee


Obviously, we can use union inside a CTE:

;with FilterEmployee (job, datebirth, sex)
AS
(
	select JobTitle, BirthDate, 'M' from HumanResources.Employee
	where Gender = 'M'
	union
	select JobTitle, BirthDate, 'F' from HumanResources.Employee
	where Gender = 'F'
)
select * from FilterEmployee



TAKING ALL EMPLOYEES THAT HAVE TWO OR MORE ROWS IN THE TABLE EMPLOYEEPAYHISTORY

SELECT TOP (1000) [BusinessEntityID]
      ,[RateChangeDate]
      ,[Rate]
      ,[PayFrequency]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[HumanResources].[EmployeePayHistory]


;with PayHistory (BussinessId)
AS
(
   select distinct(BusinessEntityID) from [HumanResources].[EmployeePayHistory]
   group by BusinessEntityID
   having COUNT(BusinessEntityID)>1
)
select [BusinessEntityID]
       ,[JobTitle]
       ,[BirthDate]
       ,[Gender]
from [HumanResources].[Employee] Employee 
     join PayHistory on Employee.BusinessEntityID = PayHistory.BussinessId


INSERTING DATA INTO A TABLE CALLED FILTEREMPLOYEEPAYHISTORY, USING A CTE

CREATE TABLE [dbo].[FilterEmployeePayHistory](
	[BusinessEntityID] [int] NOT NULL,
	[JobTitle] [nvarchar](100) NOT NULL,
	[BirthDate] [date] NOT NULL,
	[Gender] [nchar](1) NOT NULL,
 CONSTRAINT [PK_FilterEmployeePayHistory] PRIMARY KEY CLUSTERED 
(
	[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


;with PayHistory (BussinessId)
 AS
 (
	select distinct(BusinessEntityID) from [HumanResources].[EmployeePayHistory]
	group by BusinessEntityID
	having COUNT(BusinessEntityID)>1
 )
,
EmployeeFilter(BusinessEntityID, JobTitle, BirthDate, Gender)
AS
(
select [BusinessEntityID]
       ,[JobTitle]
       ,[BirthDate]
       ,[Gender]
from [HumanResources].[Employee] Employee 
     join PayHistory on Employee.BusinessEntityID = PayHistory.BussinessId
)

insert into FilterEmployeePayHistory([BusinessEntityID], [JobTitle], [BirthDate], [Gender])
select BusinessEntityID, JobTitle, BirthDate, Gender from EmployeeFilter




Leave a Reply

Your email address will not be published.