[Solved] What is wrong with this recursive CTE?WITH RECURSIVE Tree AS (SELECT id, parent_id FROM Nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id FROM Nodes n JOIN Tree t ON n.id = t.... | SQL

SQL - Common Table Expressions (CTEs)

What is wrong with this recursive CTE?

WITH RECURSIVE Tree AS (SELECT id, parent_id FROM Nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id FROM Nodes n JOIN Tree t ON n.id = t.parent_id) SELECT * FROM Tree;

ARecursive CTE cannot join on same table

BJOIN condition is reversed; should be n.parent_id = t.id

CMissing ORDER BY clause in final SELECT

DAnchor member should select WHERE parent_id IS NOT NULL

Step-by-Step Solution

Solution:

Step 1: Analyze JOIN condition
The recursive join should link child nodes to their parents by matching n.parent_id to t.id.
Step 2: Identify reversed condition
The query incorrectly joins on n.id = t.parent_id, which reverses parent-child relationship.
Final Answer:
JOIN condition is reversed; should be n.parent_id = t.id -> Option B
Quick Check:
Correct join: child.parent_id = parent.id [OK]

Quick Trick: Join child.parent_id to parent.id in recursive CTE [OK]

Common Mistakes:

Reversing join condition
Changing anchor WHERE clause incorrectly
Thinking ORDER BY is mandatory

Master "Common Table Expressions (CTEs)" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

What is wrong with this recursive CTE?

Step 1: Analyze JOIN condition

Step 2: Identify reversed condition

Final Answer:

Quick Check:

Want More Practice?