Tree Calculations with Recursion

Abstract

Any procedural language supports some form of recursion. A procedure or function can call itself – if needed repeatedly until some condition has been reached. Typically they’ll also support iteration, which is related but not quite the same. SQL deals with sets of rows, not procedural logic, so how can you do recursion in SQL? It still concerns itself with sets of rows: first find a set of rows; then based on that set of rows, you apply some logic to find a second set of rows; then based on that set of rows, you apply the logic again (recursively) to find a third set of rows; and so you keep on going until you find no more rows. In this chapter the primary focus is on SQL recursion in the form of recursive subquery factoring that is the most directly applicable method of recursion in SQL.