Nested CTE(Recursive Common Table Expression)

Hey guys ! I hope you are doing best in your bright career.Today I am going to discuss an important topic; Nested CTE( Common Table Expression).Many of us are much familiar about this topic i.e. CTE but many of us may not familiar,so I am going start with basics.

A common table expression (CTE) is a bit like a temporary table.It’s transient, lasting only as long as the query requires it. Temporary tables are available for use during the lifetime of the session of the query running the code or until they are explicitly dropped.

It is a subquery that exists only in memory, so it doesn’t require special permissions or unnecessary physical disk operations.Unlike a traditional subquery, the CTE is a named object that can be reused and referenced much like a table, enabling far greater flexibility.

If you need a set of data to exist for this amount of time only, then a CTE can be better in performance terms than a temporary table.

A CTE is defined before it is used in the query script, beginning with the word WITH followed by a list of the output columns in parentheses, then the word AS followed by a complete SELECT statement in parentheses,

For more information and syntax of CTE, please refer to MSDN.

Lets try to understand with an example. Suppose we have a table employee with below structure-

and script is given below-

CREATE TABLE [dbo].[employee](

 [id] [int] IDENTITY(1,1) NOT NULL,

 [EmpName] [varchar](50) NULL,

 [Dept] [nchar](10) NULL,

 [Salary] [nchar](10) NULL,

 [mgrID] [int] NULL

)

INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (1, 'Atul', 'IT', '11000', NULL)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (2, 'Subash', 'IT', '9000', 1)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (3, 'Randhir','IT', '5000', 1)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (4, 'Brijesh', 'IT', '7000',3)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (5, 'Amit', 'HR ', N'15000', 1)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (6, 'Ashish', 'HR', '8100', 5)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (7, 'Anoop', 'IT', '8000', 5)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (8, 'Abhishek', 'HR', '9000', 5)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (9, 'Shyamlal', 'IT', '8500', 3)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (10, 'Rahul', 'IT', '4000', 3)
INSERT [dbo].[employee] ([id], [EmpName], [Dept], [Salary], [mgrID]) VALUES (11, 'Arun', 'HR', '6000', 6)

Suppose we have to get employee name with salary below than 8000,we can easily get through select query-

  select EmpName,Salary from employee where Salary<8000

but now we are going to use CTE, and query is-

 ;With LowSalary as
  (
   select EmpName,Salary from employee where Salary<8000 
  )
 select * from LowSalary

and the result set  like this-

In the above example we are using SELECT statement but we can use INSERT, UPDATE, or DELETE statements also in CTE.

This is just a simple example of CTE, but now we think that what is benefit of using it because we can get above resultset with just write a single line.Now I am going to explain uses of CTE in complex query.

In this query we are also implement Nested CTE(recursive CTE).

Suppose we need a result set of employee name with manager name,then we need to implement Nested CTE in query.

;With EmpCTE as
  (
   select * from employee 
   ), MgrCTE as
   (
   select employee.EmpName,EmpCTE.empname as ManagerName from employee inner join EmpCTE
   on  EmpCTE.id=employee.mgrID
   )
select * from MgrCTE order by ManagerName

and the result set is given below-

and suppose we need to get employee name and their manager name,whose salary is less than 8000,then we write following query-

;With EmpCTE as
  (
   select * from employee 
   ), MgrCTE as
   (
   select employee.EmpName,employee.Salary,EmpCTE.empname as ManagerName from employee inner join EmpCTE
   on  EmpCTE.id=employee.mgrID
   )
   select * from MgrCTE where Salary<8000  

and result set is-

SQL programmers find CTE of immense use and thanks to this feature, complexities in programming life have been considerably simplified. I hope I have been able to justify CTE reasonably well in this article.