Recursive Selects using Common Table Expressions

Let face it: one thing relational databases can be particularly clumsy is recursion. Oracle as the primitive "connect internal", SQL Server 2005 as finally address it with CTEs (Common Table Expressions).

Here's a sample from infoq:

;WITH FirstCTE (column1, column2) AS
(SELECT column1, column2 FROM MyTable),

SecondCTE(column1, column2) AS
(SELECT column1, column2 FROM OtherTable)

Select * FROM FirstCTE UNION ALL Select * FROM SecondCTE

An article in October's MSDN Magazine outlines the rules of recursion.

1. Create the query that returns the top level (this is the anchor member).
2. Write a recursive query (this is the recursive member).
3. UNION the first query with the recursive query.
4. Make sure you have a case where no rows will be returned (this is your termination check).