CTE & Recursive CTE

CTE : Common table expression. A CTE is a temporary result set, that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE.

- Let's create the required Employee and Department tables, that we will be using for this demo.

SQL Script to create tblEmployee table:

CREATE TABLE tblEmployee

(

Id int Primary Key,

Name VARCHAR(30),

Gender VARCHAR(10),

DepartmentId int

)

SQL Script to create tblDepartment table

Create table tbldepartment

(DeptID int primary key,

DeptName VARCHAR(20))

Insert data into tblDepartment table

Insert into tblDepartment values (1,'IT')

(2,'Payroll')

(3,'HR')

(4,'Admin')

Insert data into tblEmployee table

Insert into tblEmployee values (1,'John', 'Male', 3),

(2,'Mike', 'Male', 2),

(3,'Pam', 'Female', 1),

(4,'Todd', 'Male', 4),

(5,'Sara', 'Female', 1),

(6,'Ben', 'Male', 3)

Before we write the query, let's look at the syntax for creating a CTE.

WITH cte_name (Column1, Column2, ..)

AS

( CTE_query )

SQL query using CTE:

With EmployeeCount(Department_ID, Total_Employees)

as (Select Department_ID, Count(*) as totalemployees

from tblemployee group by Departmentid)

Select DeptName, Totalemployees from tblDepartment join

employeecount on tbldepartment.deptid=employeecount.departmentid

order by Totalemployees

We define a CTE, using WITH keyword, followed by the name of the CTE. In our example, EmployeeCount is the name of the CTE. Within parentheses, we specify the columns that make up the CTE. DepartmentId and TotalEmployees are the columns of EmployeeCount CTE. These 2 columns map to the columns returned by the SELECT CTE query. The CTE column names and CTE query column names can be different. Infact, CTE column names are optional. However, if you do specify, the number of CTE columns and the CTE SELECT query columns should be same. Otherwise you will get an error stating - 'EmployeeCount has fewer columns than were specified in the column list'. The column list, is followed by the as keyword, following which we have the CTE query within a pair of parentheses.

EmployeeCount CTE is being joined with tblDepartment table, in the SELECT query, that immediately follows the CTE. Remember, a CTE can only be referenced by a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE. If you try to do something else in between, we get an error stating - 'Common table expression defined but not used'. The following SQL, raise an error.

With EmployeeCount(DepartmentId, TotalEmployees)

as

(

Select DepartmentId, COUNT(*) as TotalEmployees

from tblEmployee

group by DepartmentId

)

Select 'Hello'

Select DeptName, TotalEmployees

from tblDepartment

join EmployeeCount

on tblDepartment.DeptId = EmployeeCount.DepartmentId

order by TotalEmployees

It is also, possible to create multiple CTE's using a single WITH clause.

With EmployeesCountBy_Payroll_IT_Dept(DepartmentName, Total)

as

(

Select DeptName, COUNT(Id) as TotalEmployees

from tblEmployee

join tblDepartment

on tblEmployee.DepartmentId = tblDepartment.DeptId

where DeptName IN ('Payroll','IT')

group by DeptName

),

EmployeesCountBy_HR_Admin_Dept(DepartmentName, Total)

as

(

Select DeptName, COUNT(Id) as TotalEmployees

from tblEmployee

join tblDepartment

on tblEmployee.DepartmentId = tblDepartment.DeptId

group by DeptName

)

Select * from EmployeesCountBy_HR_Admin_Dept

UNION

Select * from EmployeesCountBy_Payroll_IT_Dept

-----------------------------------------------------------------------------------------------------------------------------------------

How does a recursive CTE work?

We will be using Employees table to understand how a recursive CTE works.

Here is the recursive CTE that gets the organization hierarchy based on an EmployeeId. Notice that the recursive CTE has got 2 parts (Anchor & Recursive Member).

WITH EmployeeCTE AS

(

-- Anchor

Select EmployeeId, EmployeeName, ManagerID

From Employees

Where EmployeeId = @ID

UNION ALL

-- Recursive Member

Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID

From Employees

JOIN EmployeeCTE

ON Employees.EmployeeId = EmployeeCTE.ManagerID

)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName

From EmployeeCTE E1

LEFT Join EmployeeCTE E2

ON E1.ManagerID = E2.EmployeeId

When David's EmployeeId is passed the query produces the following output.

Let's now discuss how the CTE executes line by line.

Step 1: Execute the anchor part and get result R0

Step 2: Execute the recursive member using R0 as input and generate result R1

Step 3: Execute the recursive member using R1 as input and generate result R2

Step 4: Recursion goes on until the recursive member output becomes NULL

Step 5: Finally apply UNION ALL on all the results to produce the final output