Recursive CTE for hierarchical data in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Recursive CTE in SQL?

A Recursive CTE (Common Table Expression) is a SQL query that refers to itself to process hierarchical or tree-structured data, allowing you to retrieve parent-child relationships in a single query.

beginner

What are the two main parts of a Recursive CTE?

1. Anchor member: The initial query that returns the root rows.<br>2. Recursive member: The query that references the CTE itself to find child rows, repeating until no more children are found.

intermediate

Why use Recursive CTEs instead of multiple queries for hierarchical data?

Recursive CTEs simplify querying hierarchical data by using a single query to traverse all levels, improving readability and performance compared to running multiple queries or loops.

intermediate

In a Recursive CTE, what stops the recursion from running forever?

The recursion stops when the recursive member query returns no new rows, meaning there are no more child records to process.

advanced

Example: What does this Recursive CTE do?<br>

WITH RECURSIVE EmployeeHierarchy AS (  SELECT EmployeeID, ManagerID, 1 AS Level FROM Employees WHERE ManagerID IS NULL  UNION ALL  SELECT e.EmployeeID, e.ManagerID, eh.Level + 1 FROM Employees e JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID) SELECT * FROM EmployeeHierarchy;

This query builds a hierarchy of employees starting from top-level managers (where ManagerID is NULL) and recursively finds all employees under each manager, adding a 'Level' column to show depth in the hierarchy.

What keyword starts a Recursive CTE in SQL?

A RECURSIVE CTE

B WITH RECURSIVE

C SELECT RECURSIVE

D START RECURSIVE

Recursive CTEs begin with the keyword 'WITH RECURSIVE' followed by the CTE name and query.

In a Recursive CTE, what does the anchor member do?

A Defines the base rows to start recursion

B Joins tables together

C Limits the number of recursion levels

D Deletes duplicate rows

The anchor member defines the starting rows, usually the root nodes in hierarchical data.

How does a Recursive CTE know when to stop recursion?

A When the recursive query returns no new rows

B When a maximum recursion level is reached

C When a timeout occurs

D When the user cancels the query

Recursion stops naturally when the recursive member returns no new rows to add.

Which SQL clause is used to combine the anchor and recursive parts in a Recursive CTE?

A ORDER BY

B JOIN

C GROUP BY

D UNION ALL

UNION ALL combines the anchor member and recursive member results in Recursive CTEs.

What kind of data is best suited for Recursive CTEs?

A Flat tabular data without relationships

B Random unstructured text

C Hierarchical or tree-structured data

D Single row data

Recursive CTEs are designed to handle hierarchical data like organizational charts or folder trees.

Explain how a Recursive CTE works to retrieve hierarchical data.

Think about starting from the top and finding children step by step.

Describe a real-life example where you would use a Recursive CTE.

Consider any data that looks like a tree or hierarchy.