[Solved] Why does this recursive CTE cause an error or infinite loop?WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 FROM cte) SELECT * FROM cte WHERE val < 5; | SQL

SQL - Common Table Expressions (CTEs)

Why does this recursive CTE cause an error or infinite loop?

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

AMissing termination condition in the recursive part

BIncorrect use of UNION instead of UNION ALL

CAnchor member is invalid

DWHERE clause should be inside the CTE

Step-by-Step Solution

Solution:

Step 1: Examine Recursive Member
Recursive SELECT lacks a WHERE clause to stop recursion.
Step 2: Effect of Missing Condition
Without a termination condition, recursion continues indefinitely.
Step 3: WHERE Clause Location
WHERE outside CTE filters final output but does not stop recursion.
Final Answer:
Missing termination condition in the recursive part -> Option A
Quick Check:
Always include recursion stop condition inside CTE [OK]

Quick Trick: Termination condition must be inside recursive member [OK]

Common Mistakes:

Master "Common Table Expressions (CTEs)" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Why does this recursive CTE cause an error or infinite loop?