SQL - Common Table Expressions (CTEs)
Why does a recursive CTE require the UNION ALL operator instead of just UNION when processing hierarchical data?
Why does a recursive CTE require the UNION ALL operator instead of just UNION when processing hierarchical data?
hard📝 Conceptual Q10 of 15
Step-by-Step Solution
Solution:
Step 1: Understand difference between UNION and UNION ALL
UNION removes duplicate rows, UNION ALL keeps all rows including duplicates.Step 2: Why duplicates matter in recursion
Recursive CTEs rely on duplicates to continue building hierarchy; removing duplicates can stop recursion early.Final Answer:
UNION ALL preserves duplicates needed for recursion; UNION removes them causing incomplete results -> Option AQuick Check:
Recursive CTE needs UNION ALL to keep duplicates [OK]
Quick Trick: Use UNION ALL to keep duplicates for recursion to work [OK]
Common Mistakes:
- Thinking UNION ALL sorts results
- Believing UNION ALL is faster due to indexes
- Confusing recursive and non-recursive CTE requirements
Master "Common Table Expressions (CTEs)" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes