Bei einer Tabelle, die Unterstellungsbeziehungen in einer Firma darstellt, können Sie eine Abfrage schreiben, die alle einer bestimmten Person unterstellten Mitarbeiter zurückgibt.

Je nachdem, wie Sie die Abfrage schreiben, kann es sinnvoll sein, die Anzahl der Rekursionsstufen zu beschränken. Durch Beschränken der Anzahl der Stufen können Sie beispielsweise nur die obersten Managementebenen zurückgeben, was aber möglicherweise einige Mitarbeiter ausschließt, wenn die Hierarchie mehr Ebenen aufweist, als Sie angenommen haben. Wenn Sie für die Anzahl der Stufen keine Einschränkung festlegen, stellen Sie zwar sicher, dass kein Mitarbeiter ausgeschlossen wird, erzeugen aber möglicherweise unendliche Rekursionen, falls die Ausführung Schleifen erfordert, wenn z.B. ein Mitarbeiter direkt oder indirekt sich selbst unterstellt ist. Dies könnte in der Managementhierarchie einer Firma eintreten, wenn Mitarbeiter der Firma auch im Vorstand sitzt.

Die folgende Abfrage zeigt, wie Sie die Mitarbeiter anhand ihrer Managementebene auflisten. Ebene 0 stellt Mitarbeiter ohne Vorgesetzte dar. Ebene 1 stellt Mitarbeiter dar, die direkt einem Vorgesetzten der Ebene 0 unterstellt sind, Ebene 2 stellt Mitarbeiter dar, die direkt einem Vorgesetzten der Ebene 1 unterstellt sind, und so weiter.

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;

Die Bedingung innerhalb der rekursiven Abfrage, mit der die Anzahl der Managementebenen auf weniger als 20 beschränkt wird (m.mgmt leve < 20), wird als Stop-Bedingung bezeichnet und ist eine wichtige Vorsichtsmaßnahme. Sie verhindert eine unendliche Rekursion, falls die Tabellendaten eine Schleife enthalten.

Die max_recursive_iterations-Option kann ebenfalls dazu verwendet werden, endlos laufende rekursive Abfragen zu verhindern. Der Standardwert dieser Option ist 100. Rekursive Abfragen, die diese Anzahl von Wiederholungen überschreiten, werden beendet, verursachen aber einen Fehler. Auch wenn diese Option scheinbar die Bedeutung einer Stop-Bedingung vermindert, ist das nicht der Fall. Die Anzahl der bei jeder Wiederholung ausgewählten Zeilen wächst möglicherweise exponentiell, was ernsthafte Auswirkungen auf die Datenbank-Performance haben kann, bevor das Maximum erreicht ist. Stop-Bedingungen in rekursiven Abfragen sind eine Möglichkeit, entsprechende Grenzen in solchen Situationen zu setzen.

Rekursive allgemeine Tabellenausdrücke enthalten eine Anfangs-Unterabfrage, einen sogenannten Initialwert (Seed-Wert), und eine rekursive Unterabfrage, die bei jeder Wiederholung zusätzliche Zeilen zur Ergebnismenge hinzufügt. Die zwei Teile können nur mit dem UNION ALL-Operator verbunden werden. Die Anfangs-Unterabfrage ist eine gewöhnliche nicht-rekursive Abfrage und wird zuerst abgearbeitet. Der rekursive Abschnitt enthält eine Referenz zu den Zeilen, die während der vorherigen Wiederholung hinzugefügt wurden. Die Rekursion stoppt automatisch, sobald eine Wiederholung keine neuen Zeilen mehr generiert. Es gibt keine Möglichkeit, Zeilen zu referenzieren, die vor der vorherigen Wiederholung ausgewählt wurden.

Die SELECT-Liste der rekursiven Unterabfrage muss der der anfänglichen Unterabfrage in Anzahl und Datentyp entsprechen. Wenn eine automatische Konvertierung der Datentypen nicht durchgeführt werden kann, passen Sie explizit die Ergebnisse der einen Unterabfrage an, damit sie jenen in der anderen Unterabfrage entsprechen.

• Mehrere rekursive allgemeine Tabellenausdrücke
• max_recursive_iterations-Option