When recursion beats a regular join

Question

Accepted Answer

Recursive CTEs shine when the number of levels is not known in advance. A JOIN can handle one level, a self-join can handle two, but an arbitrarily deep hierarchy requires recursion. Other classic use cases include generating date series, computing transitive closures in graphs, and building bill-of-materials explosions. The other options are all better handled by simpler, non-recursive SQL.