Recursive CTEs in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple recursive CTE

Given the following recursive CTE, what will be the output rows?

MySQL

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 3
)
SELECT * FROM numbers ORDER BY n;

A 1, 2, 3

B 1, 2, 3, 4

C 1, 2

D Syntax error

💡 Hint

Think about how the recursion stops when n reaches 3.

The CTE starts with 1, then recursively adds 1 until n is less than 3, so it outputs 1, 2, and 3.

📝 Syntax

intermediate

Identify the syntax error in recursive CTE

Which option contains a syntax error in the recursive CTE definition?

A WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 FROM cte WHERE val < 5) SELECT * FROM cte;

B WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 FROM cte WHERE val <= 5) SELECT * FROM cte;

C WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 cte WHERE val < 5) SELECT * FROM cte;

D ;etc MORF * TCELES )5 < lav EREHW etc MORF 1 + lav TCELES LLA NOINU lav SA 1 TCELES( SA etc EVISRUCER HTIW

💡 Hint

Look carefully at the recursive SELECT statement syntax.

Option C is missing the FROM keyword before 'cte' in the recursive SELECT, causing a syntax error.

❓ optimization

advanced

Optimizing recursive CTE for performance

Which option best improves performance of this recursive CTE that generates a sequence of numbers up to 1000?

MySQL

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

A Add an index on the CTE column 'num' to speed recursion

B Limit recursion depth by adding OPTION (MAXRECURSION 1000)

C Rewrite the CTE to use a JOIN instead of UNION ALL

D Use a non-recursive numbers table instead of recursive CTE

💡 Hint

Think about alternatives to recursion for generating sequences.

Using a pre-built numbers table is more efficient than recursive CTE for large sequences.

🧠 Conceptual

advanced

Understanding termination condition in recursive CTEs

Why is it important to have a proper termination condition in a recursive CTE?

A To prevent infinite recursion and possible server crash

B To make the query run faster by skipping recursion

C To allow the CTE to run multiple times in parallel

D To enable the CTE to return duplicate rows

💡 Hint

Think about what happens if recursion never stops.

Without a termination condition, recursion never ends, causing infinite loops and resource exhaustion.

🔧 Debug

expert

Debugging unexpected output in recursive CTE

Given this recursive CTE, why does it output duplicate rows?

MySQL

WITH RECURSIVE cte AS (
  SELECT 1 AS val
  UNION ALL
  SELECT val + 1 FROM cte WHERE val < 3
  UNION ALL
  SELECT val + 1 FROM cte WHERE val < 3
)
SELECT * FROM cte ORDER BY val;

A Because the CTE is missing an ORDER BY clause inside

B Because the recursive part has two UNION ALL clauses causing duplicates

C Because the WHERE condition is incorrect and allows duplicates

D Because the base case SELECT is missing a DISTINCT keyword

💡 Hint

Look at how many recursive SELECTs are combined with UNION ALL.

Having two UNION ALL recursive SELECTs adds the same rows twice, causing duplicates.

Practice

1. What is the main purpose of a recursive CTE in MySQL?

easy

A. To create temporary tables for storing large datasets

B. To repeatedly execute a query to explore hierarchical or connected data

C. To speed up simple SELECT queries without joins

D. To permanently store data in the database

Solution

1. Step 1: Understand recursive CTE concept

   A recursive CTE runs a base query and then repeatedly runs a recursive part referring back to itself to explore connected data.

2. Step 2: Identify the purpose

   This process is mainly used to handle hierarchical or tree-like data, such as organizational charts or folder structures.

3. Final Answer:

   To repeatedly execute a query to explore hierarchical or connected data -> Option B

4. Quick Check:

   Recursive CTE = hierarchical data exploration [OK]

Hint: Recursive CTEs repeat queries to explore connected data [OK]

Common Mistakes:

• Confusing recursive CTEs with temporary tables
• Thinking recursive CTEs speed up all queries
• Assuming recursive CTEs permanently store data

2. Which of the following is the correct basic syntax to start a recursive CTE in MySQL?

easy

A. WITH cte_name RECURSIVE (anchor_query UNION recursive_query) SELECT * FROM cte_name;

B. CREATE RECURSIVE TABLE cte_name AS (anchor_query UNION recursive_query);

C. WITH RECURSIVE cte_name AS (anchor_query UNION ALL recursive_query) SELECT * FROM cte_name;

D. SELECT RECURSIVE * FROM cte_name WHERE condition;

Solution

1. Step 1: Recall MySQL recursive CTE syntax

   MySQL uses the keyword WITH RECURSIVE followed by the CTE name and a query combining an anchor and recursive part with UNION ALL.

2. Step 2: Match syntax options

   WITH RECURSIVE cte_name AS (anchor_query UNION ALL recursive_query) SELECT * FROM cte_name; correctly shows this syntax, including UNION ALL and the final SELECT from the CTE.

3. Final Answer:

   WITH RECURSIVE cte_name AS (anchor_query UNION ALL recursive_query) SELECT * FROM cte_name; -> Option C

4. Quick Check:

   WITH RECURSIVE + UNION ALL = correct syntax [OK]

Hint: Look for WITH RECURSIVE and UNION ALL keywords [OK]

Common Mistakes:

• Using UNION instead of UNION ALL in recursive part
• Trying to CREATE RECURSIVE TABLE (not valid)
• Misplacing RECURSIVE keyword after CTE name

3. Given the table employees(id, manager_id, name), what will this query return?

WITH RECURSIVE subordinates AS (
  SELECT id, name FROM employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.name FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

medium

A. All employees who report directly or indirectly to employee with id 2

B. Only the employee with id 2

C. All employees who manage employee with id 2

D. Syntax error due to missing anchor query

Solution

1. Step 1: Analyze anchor query

   The anchor selects the employee with id = 2 as the starting point.

2. Step 2: Analyze recursive part

   The recursive part joins employees whose manager_id matches the current subordinates' id, thus finding direct reports, then their reports, and so on.

3. Step 3: Understand final output

   The query returns employee 2 and all employees reporting to them directly or indirectly.

4. Final Answer:

   All employees who report directly or indirectly to employee with id 2 -> Option A

5. Quick Check:

   Recursive join on manager_id finds all subordinates [OK]

Hint: Recursive join on manager_id finds all subordinates [OK]

Common Mistakes:

• Confusing manager and subordinate roles
• Thinking it returns only direct reports
• Assuming syntax error due to anchor query presence

4. Identify the error in this recursive CTE:

WITH RECURSIVE cte AS (
  SELECT id FROM items WHERE parent_id IS NULL
  UNION
  SELECT i.id FROM items i JOIN cte ON i.parent_id = cte.id
)
SELECT * FROM cte;

medium

A. Anchor query should use WHERE parent_id = 0 instead of IS NULL

B. Recursive part should use LEFT JOIN instead of JOIN

C. CTE name cannot be 'cte', must be more descriptive

D. Missing UNION ALL causes infinite recursion or wrong results

Solution

1. Step 1: Check recursive CTE syntax

   Recursive CTEs require UNION ALL to combine anchor and recursive parts to avoid duplicate elimination.

2. Step 2: Understand effect of UNION vs UNION ALL

   Using UNION removes duplicates each iteration, which can cause infinite recursion or incorrect results in recursive queries.

3. Final Answer:

   Missing UNION ALL causes infinite recursion or wrong results -> Option D

4. Quick Check:

   Recursive CTEs must use UNION ALL [OK]

Hint: Always use UNION ALL in recursive CTEs to avoid infinite loops [OK]

Common Mistakes:

• Using UNION instead of UNION ALL in recursive CTE
• Assuming NULL vs 0 matters for recursion logic
• Thinking JOIN type affects recursion correctness

5. You want to list all ancestors of an employee in a company hierarchy stored in employees(id, manager_id, name). Which recursive CTE query correctly finds all managers up the chain for employee with id 5?

hard

A. WITH RECURSIVE ancestors AS (
SELECT id, manager_id, name FROM employees WHERE id = 5
UNION ALL
SELECT e.id, e.manager_id, e.name FROM employees e
JOIN ancestors a ON e.id = a.manager_id
)
SELECT * FROM ancestors WHERE id != 5;

B. WITH RECURSIVE ancestors AS (
SELECT id, manager_id, name FROM employees WHERE id = 5
UNION ALL
SELECT e.id, e.manager_id, e.name FROM employees e
JOIN ancestors a ON e.manager_id = a.id
)
SELECT * FROM ancestors WHERE id != 5;

C. WITH RECURSIVE ancestors AS (
SELECT id, manager_id, name FROM employees WHERE manager_id = 5
UNION ALL
SELECT e.id, e.manager_id, e.name FROM employees e
JOIN ancestors a ON e.manager_id = a.id
)
SELECT * FROM ancestors;

D. WITH RECURSIVE ancestors AS (
SELECT id, manager_id, name FROM employees WHERE id = 5
UNION
SELECT e.id, e.manager_id, e.name FROM employees e
JOIN ancestors a ON e.id = a.manager_id
)
SELECT * FROM ancestors WHERE id != 5;

Solution

1. Step 1: Understand the goal

   We want to find all managers (ancestors) of employee 5, moving up the chain via manager_id.

2. Step 2: Analyze join condition

   To move up, join employees where employee id matches the manager_id of current ancestor (e.id = a.manager_id).

3. Step 3: Check anchor and recursive parts

   Anchor selects employee 5; recursive part finds their manager, then that manager's manager, etc.

4. Final Answer:

   WITH RECURSIVE ancestors AS (SELECT id, manager_id, name FROM employees WHERE id = 5 UNION ALL SELECT e.id, e.manager_id, e.name FROM employees e JOIN ancestors a ON e.id = a.manager_id) SELECT * FROM ancestors WHERE id != 5; -> Option A

5. Quick Check:

   Join on e.id = a.manager_id moves up hierarchy [OK]

Hint: Join on e.id = a.manager_id to move up the hierarchy [OK]

Common Mistakes:

• Reversing join condition to move down instead of up
• Using UNION instead of UNION ALL causing errors
• Starting anchor with manager_id = 5 instead of id = 5