社内の報告関係を表すテーブルがある場合、特定の 1 人に報告を行うすべての従業員を返すクエリを記述できます。

クエリの記述方法によって、再帰レベル数を制限できます。たとえば、レベル数を制限して、トップレベルの管理者のみを返すことができますが、指揮系統が予期したよりも長い場合、除外される従業員も出てきます。レベル数を制限しない場合は、従業員が除外されることはありません。ただし、ある従業員が直接または間接的に自分自身に対してレポートするなど、実行に循環が必要な場合は、無限の再帰が発生する可能性があります。この状況は、会社の従業員が取締役会の一員である場合などに、社内の管理階層内で発生することがあります。

次のクエリは、管理レベルで従業員をリストする方法を示しています。level 0 は、管理者を持たない従業員を表します。level 1 は、level 0 の管理職の 1 人に直接報告を行う従業員を表し、level 2 は、level 1 の管理職に直接報告を行う従業員を表します。以下、同様に続きます。

WITH RECURSIVE
  manager ( EmployeeID, ManagerID,
            GivenName, Surname, mgmt_level ) AS
( ( SELECT EmployeeID, ManagerID,       -- initial subquery
           GivenName, Surname, 0
    FROM Employees AS e
    WHERE ManagerID = EmployeeID )
  UNION ALL
  ( SELECT e.EmployeeID, e.ManagerID,   -- recursive subquery
           e.GivenName, e.Surname, m.mgmt_level + 1
    FROM Employees AS e JOIN manager AS m
     ON   e.ManagerID =  m.EmployeeID
      AND e.ManagerID <> e.EmployeeID
      AND m.mgmt_level < 20 ) )
SELECT * FROM manager
ORDER BY mgmt_level, Surname, GivenName;

管理レベルを 20 未満に制限する再帰クエリ内の条件 (m.mgmt leve < 20) は停止条件と呼ばれ、重要な予防措置です。これにより、テーブルデータに循環が含まれている場合に、無限再帰を回避できます。

max_recursive_iterations オプションは、暴走する再帰クエリをキャッチするために使用することもできます。このオプションのデフォルト値は 100 です。この反復数を超えた再帰クエリは終了しますが、エラーが発生します。このオプションがあれば停止条件は重要でないように見えるかもしれませんが、通常はそうではありません。各反復の間に選択されるローの数は急激に増える可能性があり、最大に達する前にパフォーマンスに深刻な影響を与えることがあります。再帰クエリ内に停止条件を設けるのは、各状況に適した制限を設定する手段です。

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

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

• 複数の再帰共通テーブル式
• max_recursive_iterations オプション