SQL For Dummies®

Chapter 12. Recursive Queries

In This Chapter

• Understanding recursive processing

• Defining recursive queries

• Finding ways to use recursive queries

One of the major criticisms of SQL, up through and including SQL‐92, was its inability to implement recursive processing. Many important problems that are difficult to solve by other means yield readily to recursive solutions. Extensions included in SQL:1999 allow recursive queries, greatly expanding the language's power. If your SQL implementation includes the recursion extensions, you can efficiently solve a large new class of problems. However, because recursion is not a part of core SQL, many implementations currently available do not include it.

What Is Recursion?

Recursion is a feature that's been around for years in programming languages such as Logo, LISP, and C++. In these languages, you can define a function (a set of one or more commands) that performs a specific operation. The main program invokes the function by issuing a command called a function call. If the function calls itself as a part of its operation, you have the simplest form of recursion.

A simple program that uses recursion in one of its functions provides an illustration of the joys and pitfalls of recursion. The following program, written in C++, draws a spiral on the computer screen. It assumes that the drawing tool is initially pointing toward the top of the screen, and includes three functions:

• The function line(n) draws a line n units long.

• The function left_turn( ...