Itzik Ben-Gan continues a series on table expressions:
Recursive CTEs have many use cases. A classic category of uses has to do with handling of graph structures, like our employee hierarchy. Tasks involving graphs often require traversing arbitrary length paths. For example, given the employee ID of some manager, return the input employee, as well as all subordinates of the input employee in all levels; i.e., direct and indirect subordinates. If you had a known small limit on the number of levels that you might need to follow (the degree of the graph), you could use a query with a join per level of subordinates. However, if the degree of the graph is potentially high, at some point it becomes impractical to write a query with many joins. Another option is to use imperative iterative code, but such solutions tend to be lengthy. This is where recursive CTEs really shine—they enable you to use declarative, concise and intuitive solutions.
I’ll start with the basics of recursive CTEs before I get to the more interesting stuff where I cover searching and cycling capabilities.
Read the whole thing. There are some good uses of recursive common table expressions (I used them to figure out nested foreign key relationships, for example), so it’s worth your time.