In this article, I have explained what is Common Table Expression(CTE) in SQL Server with examples, the Advantages of CTE and Recursive CTE in SQL Server.
What is CTE?
A CTE defines a temporary result set which you can then use in a SELECT statement.
It becomes a convenient way to manage complicated queries.CTE is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement.
You can also use a CTE in a CREATE view, as part of the view’s SELECT query.
Common Table Expressions are defined within the statement using the WITH operator.
Why Use CTE?
Readability – CTE's promote readability. Rather than lump all you query logic into one large query, create several CTE’s,
which are combined later in the statement. This lets you get the chunks of data you need and combine them in a final SELECT.
Substitute for a View – You can substitute a CTE for a view. This is useful if you don’t have permissions to create a view object
or you don’t want to create one as it is only used in this one query.
Recursion – Use CTE’s do create recursive queries, that is queries that can call themselves.
This is useful when you need to work on hierarchical data such as organization charts.
Code Maintablility - In SQL, we will use sub-queries to join the records or filter the records from a sub-query. Whenever we refer the same data or join the same set of records using a sub-query, the code maintainability will be difficult.
A CTE makes improved readability and maintenance.
Limitations – Overcome SELECT statement limitations, such as referencing itself (recursion), or performing GROUP BY using non-deterministic functions.
Ranking – Whenever you want to use ranking function such as ROW_NUMBER(), RANK(), NTILE() etc.
Syntax Of CTE :
WITH CTE_NAME (Column/s)
AS
(
SELECT column/s FROM Table_Name
)
SELECT column/s FROM CTE_NAMEHere, No. of the columns should match the no. of columns in select statement.
Multiple CTE can be created using WITH keyword and separated by comma.
Below is example of EmployeeCTE for displaying all Employee having salary greater than 25,000.
Before we see the example, let's create the query for creating tables of tblEmployee and tblDepartment:
Create Table tblDepartment
(
DeptId int primary key identity(1,1),
DeptName varchar(50)
)
Create Table tblEmployee
(
EmpId primary key identity(1,1),
DeptId int,
EmpFirstName varchar(50),
EmpLastName varchar(50),
EmpSal bigint,
EmpAge int NULL,
)Now, let's insert Some Data in it, so we can see various examples of CTE in SQL server
#tblDepartment Data :
INSERT [dbo].[tblDepartment] ([DeptId], [DeptName]) VALUES (1, N'IT')
INSERT [dbo].[tblDepartment] ([DeptId], [DeptName]) VALUES (2, N'Accounts')
INSERT [dbo].[tblDepartment] ([DeptId], [DeptName]) VALUES (3, N'HR')
INSERT [dbo].[tblDepartment] ([DeptId], [DeptName]) VALUES (4, N'Marketing')
INSERT [dbo].[tblDepartment] ([DeptId], [DeptName]) VALUES (5, N'Sales')
#tblEmployee Data :
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (1, 1, N'Riya', N'Sen', 20000, 25)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (2, 2, N'Sanvi', N'Parab', 15000, 24)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (3, 3, N'Sanju', N'Pathak', 50000, 30)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (4, 5, N'Reshma', N'Naik', 22000, 26)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (5, 1, N'Rohan', N'Sharma', 25000, 26)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (6, 2, N'Priya', N'Joshi', 18000, 27)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (7, 3, N'Manju', N'Sen', 23000, 30)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (8, 5, N'Shyam', N'Verma', 20000, 28)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (9, 4, N'Diya', N'Vaidya', 35000, 29)
INSERT [dbo].[tblEmployee] ([EmpId], [DeptId], [EmpFirstName], [EmpLastName], [EmpSal], [EmpAge]) VALUES (10, 4, N'Siddhi', N'jha', 15000, 30)
Example 1 :
Here, CTE acts as a simple derived table.
Example 2 :
If any operation like SELECT, INSERT, UPDATE, DELETE or Merge can be performed immediately after the CTE,
it throws an error as shown in below example :
Here, we will get error like this:
"Common table expression defined but not used."
This means we need to execute the EmployeeCTE SELECT immediately after the CTE definition completes.
Example 3 :
Below is an example of Update Operation :
The update operation is performed as above but the next set of select doesn’t work.
We can hit for a result set once and hit immediately after the CTE definition as below :
Example 5:
We can have multiple CTEs calls from one single query.
The below query returns Employee Details Enrolled in Different Departments :
Example 6:
For removing the duplicate Person Data, we will create following table structure :
Create Table tblPerson
(
PersonId int primary key identity(1,1),
PersonName varchar(50),
PersonProfession varchar(50)
)
INSERT [dbo].[tblPerson] ([PersonId], [PersonName], [PersonProfession]) VALUES (1, N'Rick', N'HR')
INSERT [dbo].[tblPerson] ([PersonId], [PersonName], [PersonProfession]) VALUES (2, N'Sara', N'IT')
INSERT [dbo].[tblPerson] ([PersonId], [PersonName], [PersonProfession]) VALUES (3, N'Sara', N'IT')
INSERT [dbo].[tblPerson] ([PersonId], [PersonName], [PersonProfession]) VALUES (4, N'Sheldon', N'Accounts')
INSERT [dbo].[tblPerson] ([PersonId], [PersonName], [PersonProfession]) VALUES (5, N'Ted', N'IT')And we will create CTE as below :
Above query below creates a temporary result set as :
SELECT PersonId,PersonName,PersonProfession,ROW_NUMBER()OVER(PARTITION BY PersonName,PersonProfession ORDER BY PersonId)AS RowID
FROM tblPerson
And later, we remove the duplicate rows with the ‘DELETE Statement’.
Recursive CTE:
A recursive CTE is a CTE that references itself.
It can be defined by dividing it into three parts
- Anchor Query: This is the first statement which is executed. This query will give the base data for the CTE.
- Separator: This is the middle part where in we generally use a UNION ALL and few more operators.
- Recursive Query: This is the main part, this is the CTE query which refers to the same CTE by recursion.
A recursive CTE is useful in querying hierarchical data such as organization charts where one employee reports to a manager.
Syntax Of Recursive CTE:
WITH expression_name (Column/s)
AS
(
initial_query
UNION ALL
recursive_query
)
SELECT * FROM expression_nameIt has three parts as below :
- An initial query returns the base result set of the CTE. The initial query is called an anchor member.
- A recursive query refers CTE.
Below is an example of Recursive CTE :
Table structure is used for this is as below :
Create table tblOrganization
(
EmpId int,
SupervisorId int,
Name varchar(100),
Role varchar(100)
)
insert into tblOrganization values(1,0,'Rima Parab','Software Senior Manager')
insert into tblOrganization values(2,1,'Rohan Sawant','Software Associate Manager')
insert into tblOrganization values(3,1,'Divya Naik','Software Team Lead')
insert into tblOrganization values(4,3,'Riya Sharma','Software Senior Analyst')
insert into tblOrganization values(5,3,'Ritu Yadav','Software Senior Analyst')
insert into tblOrganization values(6,3,'Ram Gupta','Software Analyst')
insert into tblOrganization values(7,4,'Shyam Patel','Associate Software Analyst')
Recursive CTE is created as below :
WITH OrgCTE(EmpId,SupervisorID,Employee,[Role],[Level])
AS
(
SELECT EmpId,SupervisorID,Name,[Role],0 FROM
tblOrganization WHERE SupervisorID=0
UNION ALL
SELECT O. EmpId,O.SupervisorID,O.Name,O.[Role],[Level]+1
FROM tblOrganization O
INNER JOIN OrgCTE OC
ON O.SupervisorID=OC.EmpId
)
select * from OrgCTE
Following is the base result set of the CTE structure :
SELECT EmpId,SupervisorID,Name,[Role],0
FROM tblOrganization WHERE SupervisorID=0
This is the input for the next recursive operation :
SELECT O.EID,O.SupervisorID,O.Name,O.[Role],[Level]+1
FROM tblOrganization O
INNER JOIN OrgCTE OC
ON O.SupervisorID=OC.Eid
This is equivalent to:
SELECT O.EID,O.SupervisorID,O.Name,O.[Role]
FROM tblOrganization O
INNER JOIN OrgCTE OC
ON O.SupervisorID= 1
When the result set is empty the recursion will stop and return the result in a union manner.
Thus the final result is:
That's it we are done.
You may also like to read:
SQL server connection string examples in C# & XML
Restore (Import) database from .bak file in SQL server (With & without scripts)
Buy us a coffee
Become a Patron