This is the fourth article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:
Introduction to Common Table Expression (a.k.a CTE)
Multiple CTEs in a Single Query
Nested Common Table Expressions
Nested CTEs is a scenario where one CTE references another CTE in it.
EXAMPLE 1: Below is a basic example of a Nested CTE:
WITH FirstCTE AS (SELECT 1 EmployeeId, 'Shreeganesh Biradar' Name) , SecondCTE AS (SELECT EmployeeId, Name, 'India' Country FROM FirstCTE) SELECT * FROM SecondCTE
EXAMPLE 2: Below is another example of a Nested CTE:
This example uses the Employees table created in the previous article Introduction to Common Table Expression (a.k.a CTE). You can visit the link to create the table, if you have not created it already.
WITH FirstCTE AS (SELECT Id EmployeeId, Name, ManagerId FROM dbo.Employees WITH(NOLOCK) WHERE ManagerId is NULL) , SecondCTE AS (SELECT E.Id EmployeeId, E.Name, E.ManagerId, FCTE.Name ManagerName FROM dbo.Employees E WITH(NOLOCK) INNER JOIN FirstCTE FCTE ON E.ManagerId = FCTE.EmployeeId) SELECT * FROM SecondCTE