In this article, I have explained what is Common Table Expression(CTE) with example, Advantages of CTE and Recursive CTE in SQL.

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 a view, as part of the view’s SELECT query.

Common Table Expressions are defined within the statement using the WITH operator. 

Why to Use CTE?

 Readability – CTE’s promote readability. Rather than lump all you query logic into one large query, create several CTE’s,

which are the 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_NAME

Here,  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.”

Which 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 .

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_name

 It has three parts as below :

  1. An initial query returns the base result set of the CTE. The initial query is called an anchor member.
  2. 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

Difference between Stored procedure and function in SQL Server (Lists differences and explains each of them separately.)

Restore (Import) database from .bak file in SQL server (With & without scripts)