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
