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
7 thoughts on “Nested Common Table Expressions (i.e. CTE) – Sql Server”
That’s not nested, just more CTEs that reference earlier CTEs.
Nested would be:
WITH y AS (
WITH x AS (
SELECT * FROM MyTable
SELECT * FROM x
SELECT * FROM y
It isn’t possible to truly nest that like your example. That’s why the author is showing it written that way.
my thought exactly.
it is possible.. I just ran across a view in my production postgres9.1 server that has a nested cte..
two with statements…
the view starts with
WITH commission_summary AS (
WITH agents_list AS (
I went looking because this works.. and I thought you could not do this.