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.