Recursive CTEs in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Recursive CTE in SQL?

A Recursive CTE (Common Table Expression) is a query that refers to itself to repeatedly execute and build a result set step-by-step, often used to work with hierarchical or tree-structured data.

beginner

What are the two main parts of a Recursive CTE?

1. Anchor member: The starting query that runs once.<br>2. Recursive member: The part that references the CTE itself to add more rows until a condition stops it.

intermediate

How do you stop a Recursive CTE from running forever?

You include a condition in the recursive member that eventually stops adding new rows, preventing infinite loops.

intermediate

Write a simple example of a Recursive CTE to generate numbers from 1 to 5.

WITH RECURSIVE numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5) SELECT * FROM numbers;

beginner

Why are Recursive CTEs useful for hierarchical data?

Because they can repeatedly join a table to itself to find parent-child relationships, making it easy to query trees like organization charts or folder structures.

What keyword starts a Recursive CTE in MySQL?

A START RECURSIVE

B WITH RECURSIVE

C RECURSIVE CTE

D BEGIN RECURSIVE

Recursive CTEs in MySQL start with the keyword 'WITH RECURSIVE'.

Which part of a Recursive CTE runs only once?

A Anchor member

B Recursive member

C Termination condition

D Final SELECT

The anchor member is the initial query that runs once to start the recursion.

What happens if a Recursive CTE has no stopping condition?

A It returns an empty result

B It returns only the anchor member rows

C It runs forever causing an error

D It runs once and stops

Without a stopping condition, the recursion never ends and causes an error or infinite loop.

Which of these is a valid use case for Recursive CTEs?

A Filtering rows by date

B Calculating factorials

C Sorting data alphabetically

D Flattening hierarchical data

Recursive CTEs are great for working with hierarchical data like trees or organizational charts.

In the example: WITH RECURSIVE numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5) SELECT * FROM numbers; What is the output?

A Numbers 1 to 5

B Only number 1

C Numbers 1 to 4

D Numbers 0 to 5

The query generates numbers from 1 up to 5 inclusive.

Explain how a Recursive CTE works step-by-step.

Think about how the query builds results like climbing steps.

Describe a real-life example where Recursive CTEs can be useful.

Imagine a company with managers and employees.