共通テーブル式は、再帰できます。WITH の直後に RECURSIVE キーワードを使用すると、共通テーブル式は再帰的になります。1 つの WITH 句には、複数の再帰式を含めることもできます。また再帰共通テーブル式と非再帰共通テーブル式の両方を含めることができます。

再帰を使用すると、ツリー、またはツリーに似たデータ構造のテーブルをトラバースすることがはるかに簡単になります。再帰式を使用せずに単一の文内でそうした構造をトラバースする唯一の方法は、考えられる各レベルごとに 1 回ずつテーブルをそれ自体にジョインすることです。たとえば、報告階層に含まれるレベルが最大で 7 つある場合、Employees テーブルをそれ自体に 7 回ジョインする必要があります。会社の組織が変更され、新しい管理レベルが導入された場合は、クエリを再度記述する必要があります。

再帰共通テーブル式は、階層の任意の深さに対する関係を返すクエリを記述するのに便利な方法です。たとえば、社内の報告関係を表すテーブルがある場合、特定の 1 人に報告を行うすべての従業員を返すクエリを簡単に記述できます。

どの部署の従業員数が最も多いかを特定する問題を例にとります。SQL Anywhere サンプル・データベースの Employees テーブルは、架空の会社で働くすべての従業員をリストし、それぞれがどの部署で働いているかを示します。次のクエリは、部署 ID コードと各部署の従業員の総数をリストします。

SELECT DepartmentID, COUNT( * ) AS n
FROM Employees
GROUP BY DepartmentID;

このクエリは、次に示すように従業員の最も多い部署を抽出するために使用できます。

SELECT DepartmentID, n
FROM ( SELECT DepartmentID, COUNT( * ) AS n
       FROM Employees GROUP BY DepartmentID ) AS a
WHERE a.n =
  ( SELECT MAX( n )
    FROM ( SELECT DepartmentID, COUNT( * ) AS n
           FROM Employees GROUP BY DepartmentID ) AS b );

この文は正確な結果をもたらしますが、いくつか欠点もあります。最初の欠点は、サブクエリの繰り返しによってこの文の効率が悪くなることです。2 つ目の欠点は、この文ではサブクエリ間の関係が明確でないことです。

このような問題を回避する 1 つの方法として、ビューを作成し、そのビューを使用してクエリを再作成します。この方法によって、前述の問題を回避できます。

CREATE VIEW CountEmployees( DepartmentID, n ) AS
   SELECT DepartmentID, COUNT( * ) AS n
   FROM Employees GROUP BY DepartmentID; 

SELECT DepartmentID, n
   FROM CountEmployees
   WHERE n = ( SELECT MAX( n )
               FROM CountEmployees );

この方法の欠点は、ビューの作成時にデータベース・サーバがシステム・テーブルを更新しなければならないため、オーバヘッドが発生することです。ビューが頻繁に使用される場合は、この方法は合理的です。しかし、ビューが特定の SELECT 文内で 1 度しか使用されない場合は、代わりに共通テーブル式を使用することをおすすめします。共通テーブル式の詳細については、共通テーブル式の使用を参照してください。

再帰共通テーブル式には、「初期サブクエリ」 (シード) と、各繰り返しの間に結果セットにローを追加する「再帰サブクエリ」が含まれます。この 2 つの部分は、UNION ALL 演算子を使用してのみ接続できます。初期サブクエリは、通常の非再帰クエリで、最初に処理されます。再帰部分には、直前の繰り返しで追加されたローへの参照が含まれます。再帰は、繰り返しによって新しいローが生成されなくなったときに自動的に停止します。直前の繰り返しより前に選択されたローを参照する方法はありません。

再帰サブクエリの select リストは、初期サブクエリの select リストと数およびデータ型が一致する必要があります。データ型の自動変換が行えない場合は、一方のサブクエリの結果を明示的にキャストして、もう一方のサブクエリのデータ型に一致させます。