thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Advanced

Recursive CTE

A CTE that references itself to traverse hierarchical or graph data such as org charts or bills of materials.

Definition

A recursive CTE has an anchor member (the starting rows) and a recursive member joined back to the CTE, combined with UNION ALL, and iterates until no new rows appear. Oracle also offers CONNECT BY for hierarchies.

Example

WITH RECURSIVE org AS (SELECT EmpID, ManagerID, 1 AS lvl FROM Emp WHERE ManagerID IS NULL UNION ALL SELECT e.EmpID, e.ManagerID, o.lvl+1 FROM Emp e JOIN org o ON e.ManagerID = o.EmpID) SELECT * FROM org;

Exam tip

Recursion needs a termination condition; SQL Server stops at a default MAXRECURSION of 100 levels.

Related terms