CTE Notes

Table of Contents

1. What is a CTE
2. Recursive CTEs
- 2.1. Simple Recursive CTEs
- 2.2. A Realworld Hierarchical CTE Example

1. What is a CTE

In SQL, a CTE stands for Common Table Expression. It’s a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs help make complex queries easier to read and manage by breaking them down into simpler parts.

2. Recursive CTEs

2.1. Simple Recursive CTEs

The reference YouTube video

SQL for range of numbers

sql

WITH RecursiveCTE AS
(
    SELECT 1 as count                   -- Anchor

    UNION ALL                           -- Separator

    SELECT count + 1 FROM RecursiveCTE  -- Recursive Query
    WHERE count < 10                    -- Terminator
)
SELECT * FROM RecursiveCTE

output

1
.
.
.
10

SQL for range of dates

sql

declare @startdate date = '2022-01-01';
declare @enddate date = '2022-01-10';

WITH RecursiveCTE AS
(
    SELECT @startdate AS OrderDate

    UNION ALL

    SELECT DateAdd(d,1,OrderDate)
    FROM RecursiveCTE
    WHERE DateAdd(d,1,OrderDate) <= @enddate
)
SELECT * FROM RecursiveCTE

output

2022-01-01
.
.
.
2022-01-10

2.2. A Realworld Hierarchical CTE Example

First, the corporate structure:
1. Board of Directors
2. CEO
3. President
4. VPs e.g., COO, CFO, CTO
5. Directors
6. Managers
7. Supervisors

The sample Employee table

Click to show

Table 1. `Employee` table
EmpID	EmpName	Title	ManagerID
0			0
1	John Doe	CEO	10
2	Cindy Brown	President	1
5	Charles Smith	President	1
3	Alice Johnson	COO	2
4	Peter Clark	CFO	2
6	Aron McCloud	CTO	5
7	Paul Stevenson	Director	3
8	Kevin Starks	Manager	7
9	Sally Wilson	Supervisor	8
10	Gwen Lockwood	Board Member	0
11	Steven Duffy	Board Member	0

The CTE query

Click to show

sql

DECLARE @Employees TABLE (EmpID int,EmpName varchar(20), Title varchar(20), ManagerID int);
INSERT INTO @Employees VALUES (0,'','',0);
INSERT INTO @Employees VALUES (1,'John Doe','CEO',10);
INSERT INTO @Employees VALUES (2,'Cindy Brown','President',1);
INSERT INTO @Employees VALUES (5,'Charles Smith','President',1);
INSERT INTO @Employees VALUES (3,'Alice Johnson','COO',2);
INSERT INTO @Employees VALUES (4,'Peter Clark','CFO',2);
INSERT INTO @Employees VALUES (6,'Aron McCloud','CTO',5);
INSERT INTO @Employees VALUES (7,'Paul Stevenson','Director',3);
INSERT INTO @Employees VALUES (8,'Kevin Starks','Manager',7);
INSERT INTO @Employees VALUES (9,'Sally Wilson','Supervisor',8);
INSERT INTO @Employees VALUES (10,'Gwen Lockwood','Board Member',0);
INSERT INTO @Employees VALUES (11,'Steven Duffy','Board Member',0);

WITH Manager_CTE as
(
    Select 0 as Level
	,EmpName as StaffName
	,Title as StaffTitle
	,CAST('' AS varchar(20)) as ManagerName
	,CAST('' AS varchar(20)) as ManagerTitle
	,EMPID
    From @Employees
    Where EmpName = 'Gwen Lockwood'

    UNION ALL

    Select Level + 1
	,Staff.EmpName as StaffName
	,Staff.Title
	,Manager.StaffName as ManagerName
	,Manager.StaffTitle as ManagerTitle
	,Staff.EmpID
    From Manager_CTE as Manager
    INNER JOIN @Employees Staff ON Staff.ManagerID = Manager.EmpID

)
Select Level,ManagerName,ManagerTitle,StaffName,StaffTitle from Manager_CTE
	where Level > 0

The result

Click to show

Table 2. the results
Level	ManagerName	ManagerTitle	StaffName	StaffTitle
1	Gwen Lockwood	Board Member	John Doe	CEO
2	John Doe	CEO	Cindy Brown	President
2	John Doe	CEO	Charles Smith	President
3	Charles Smith	President	Aron McCloud	CTO
3	Cindy Brown	President	Alice Johnson	COO
3	Cindy Brown	President	Peter Clark	CFO
4	Alice Johnson	COO	Paul Stevenson	Director
5	Paul Stevenson	Director	Kevin Starks	Manager
6	Kevin Starks	Manager	Sally Wilson	Supervisor