In der Welt der relationalen Datenbanken gibt es oft Situationen, in denen Daten in einer hierarchischen Struktur organisiert sind. Das kann von einfachen Kategorien und Unterkategorien bis hin zu komplexen Organisationsstrukturen reichen. Um solche Hierarchien effektiv abzufragen, benötigen wir rekursive SQL-Abfragen. In diesem Blogbeitrag werden wir uns mit rekursiven SQL-Abfragen in SQL Server, MySQL und PostgreSQL befassen. Ob Sie ein Datenbankprofi oder ein technikbegeisterter Anfänger sind, dieser Beitrag wird Ihnen helfen, die Grundlagen der rekursiven SQL-Abfragen zu verstehen.
Rekursive SQL-Abfragen in SQL Server:
In SQL Server können wir rekursive Abfragen mit der Common Table Expression (CTE) erstellen. Eine CTE ist eine temporäre Ergebnismenge, die innerhalb des Geltungsbereichs einer einzelnen SELECT-, INSERT-, UPDATE-, DELETE- oder CREATE VIEW-Anweisung definiert ist. Um eine rekursive CTE zu erstellen, verwenden wir die UNION ALL-Anweisung, um die Basis- und Rekursionsfälle zu verbinden. Im vorherigen Beispiel haben wir eine rekursive CTE verwendet, um eine Hierarchie von Organisationsstrukturen zu durchlaufen und ihre jeweilige Ebene abzurufen.
-- Sicht erstellen
CREATE VIEW OrganizationalUnitWithLevel AS
WITH Hierarchie (OuID, ParentId, Bezeichnung, Ebene)
AS
(
-- Basisfall: Root-Element
SELECT OuID, ParentId, Bezeichnung, 0 AS Ebene
FROM OrganizationalUnit
WHERE OuID = 'ABC12345-ABBA-32D2-C340-FEDCBA543210'
UNION ALL
-- Rekursiver Fall: Kind-Elemente
SELECT ou.OuID, ou.ParentId, ou.Bezeichnung, h.Ebene + 1
FROM OrganizationalUnit ou
JOIN Hierarchie h ON ou.ParentId = h.OuID
)
SELECT * FROM Hierarchie;
Rekursive SQL-Abfragen in MySQL:
In MySQL können wir ebenfalls rekursive CTEs verwenden, um Hierarchien abzufragen. Die Syntax ist SQL Server sehr ähnlich, aber es gibt einige Unterschiede in der Verwendung von Datentypen. Hier ist ein Beispiel, wie Sie rekursive CTEs in MySQL verwenden können:
CREATE TABLE OrganizationalUnit (
OuID CHAR(36) PRIMARY KEY,
ParentId CHAR(36),
Bezeichnung VARCHAR(255),
FOREIGN KEY (ParentId) REFERENCES OrganizationalUnit(OuID)
);
-- Rekursive CTE
WITH RECURSIVE Hierarchie AS (
SELECT OuID, ParentId, Bezeichnung, 0 AS Ebene
FROM OrganizationalUnit
WHERE OuID = 'ABC12345-ABBA-32D2-C340-FEDCBA543210'
UNION ALL
SELECT ou.OuID, ou.ParentId, ou.Bezeichnung, h.Ebene + 1
FROM OrganizationalUnit ou
JOIN Hierarchie h ON ou.ParentId = h.OuID
)
SELECT * FROM Hierarchie;
hier noch der SQL Code für Beispieldaten:
CREATE TABLE OrganizationalUnit (
OuID uniqueidentifier PRIMARY KEY,
ParentId uniqueidentifier,
Bezeichnung nvarchar(255)
);
-- Beispielwerte einfügen
INSERT INTO OrganizationalUnit (OuID, ParentId, Bezeichnung)
VALUES ('ABC12345-ABBA-32D2-C340-FEDCBA543210', NULL, 'Root'),
('D2E3F4A5-ABBA-32D2-C340-FEDCBA987654', 'ABC12345-ABBA-32D2-C340-FEDCBA543210', 'Level 1'),
('E3F4A5B6-ABBA-32D2-C340-FEDCBA987654', 'D2E3F4A5-ABBA-32D2-C340-FEDCBA987654', 'Level 2');
Rekursive SQL-Abfragen in PostgreSQL:
Auch in PostgreSQL können wir rekursive CTEs verwenden, um Hierarchien abzufragen. Die Syntax ist sowohl SQL Server als auch MySQL ähnlich. Hier ist ein Beispiel, wie Sie rekursive CTEs in PostgreSQL verwenden können:
CREATE TABLE OrganizationalUnit (
OuID UUID PRIMARY KEY,
ParentId UUID REFERENCES OrganizationalUnit(OuID),
Bezeichnung VARCHAR(255)
);
-- Rekursive CTE
WITH RECURSIVE Hierarchie AS (
SELECT OuID, ParentId, Bezeichnung, 0 AS Ebene
FROM OrganizationalUnit
WHERE OuID = 'ABC12345-ABBA-32D2-C340-FEDCBA543210'
UNION ALL
SELECT ou.OuID, ou.Parent
das Resultat sieht dann so aus:
Rekursivität ist ein grundlegendes Konzept in der Informatik und bezieht sich auf die Fähigkeit einer Funktion oder eines Algorithmus, sich selbst aufzurufen, um ein Problem in kleinere Teile zu zerlegen und so eine Lösung zu finden. Rekursive Algorithmen sind besonders nützlich für das Durchsuchen und Manipulieren von hierarchischen und verschachtelten Datenstrukturen, wie z. B. Verzeichnisbäume, Organigramme und genealogische Stammbäume. In der Welt der relationalen Datenbanken können rekursive SQL-Abfragen verwendet werden, um ähnliche Probleme bei der Arbeit mit hierarchischen Datenstrukturen zu lösen.
“Um Rekursion zu verstehen, muss man zuerst Rekursion verstehen.”
– Quelle unbekannt
Deep Dive
Schritt für Schritt erklärt. Zunächst definieren wir eine Common Table Expression (CTE) namens “Hierarchie”. Die CTE besteht aus zwei Teilen: dem Basisfall und dem Rekursionsfall. Der Basisfall ist die Startposition unserer Hierarchie, meist das Root-Element. Im Rekursionsfall verwenden wir die Ergebnisse des vorherigen Schritts, um weitere Daten aus der Tabelle abzurufen, die auf der aktuellen Ebene der Hierarchie liegen.
Im Beispiel-Query haben wir die CTE “Hierarchie” definiert, die aus zwei SELECT-Anweisungen besteht. Die erste SELECT-Anweisung ist der Basisfall und wählt das Root-Element aus der Tabelle “OrganizationalUnit” aus. Wir geben der Ebene des Root-Elements den Wert 0.
Die zweite SELECT-Anweisung ist der Rekursionsfall. Hier verbinden wir die Tabelle “OrganizationalUnit” mit der CTE “Hierarchie” basierend auf der Bedingung, dass die “ParentId” der “OrganizationalUnit”-Tabelle gleich der “OuID” der “Hierarchie”-CTE ist. Wir erhöhen die “Ebene” um 1, um die Hierarchieebene des aktuellen Elements zu bestimmen. Die beiden SELECT-Anweisungen werden mit der UNION ALL-Anweisung kombiniert, um die rekursive Abfrage zu erstellen.
Die rekursive CTE wird ausgeführt, indem sie iterativ auf sich selbst zurückgreift und dabei die Hierarchie durchläuft, bis keine weiteren Elemente gefunden werden, die die Verknüpfungsbedingung erfüllen. Schließlich wählen wir alle Ergebnisse aus der CTE “Hierarchie” aus, um die vollständige Hierarchie mit ihren jeweiligen Ebenen darzustellen.
