Code & Queries

Was sind Common Table Expressions (CTEs)?

Common Table Expressions, kurz CTEs, bieten eine mächtige Möglichkeit in der Strukturierten Abfragesprache (SQL), temporäre Ergebnismengen zu erstellen und zu verwenden. Diese temporären Resultsets können innerhalb einer einzelnen SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung verwendet werden. Sie erleichtern die Lesbarkeit und Wartbarkeit von SQL-Abfragen und ermöglichen es, komplexe Abfragen aufzuteilen und sie modular zu gestalten.

---

Warum sollten wir CTEs verwenden?

1. Verbesserte Lesbarkeit: Durch das Aufteilen komplexer Abfragen in logische Abschnitte wird der Code übersichtlicher.
2. Wiederverwendbarkeit: Innerhalb einer Abfrage können CTEs mehrfach verwendet werden.
3. Rekursive Abfragen: CTEs unterstützen rekursive Abfragen, was bei Hierarchien oder Baumstrukturen sehr nützlich ist.
4. Optimierung: Manche Datenbank-Management-Systeme (DBMS) optimieren CTEs besser als alternative Methoden wie Unterabfragen.

---

Syntax einer CTE

Die grundlegende Syntax für eine CTE sieht wie folgt aus:

WITH cte_name AS (
    -- SQL-Abfrage, die das temporäre Resultset definiert
)
SELECT * FROM cte_name;

Beispiel 1: Einfache CTE

Angenommen, wir haben eine Tabelle employees mit den Spalten id, name und salary. Wir möchten die Mitarbeiter mit einem Gehalt über 50.000 anzeigen.

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT * 
FROM high_salary_employees;

In diesem Beispiel erstellt die CTE high_salary_employees ein temporäres Resultset, das nur die Mitarbeiter mit einem Gehalt über 50.000 enthält. Die äußere SELECT-Anweisung gibt dieses Resultset dann aus.

---

Verkettete CTEs

Es ist möglich, mehrere CTEs in einer Abfrage zu verketten. Jede CTE kann auf die vorherige verweisen.

Beispiel 2: Verkettete CTEs

Angenommen, wir möchten nicht nur die Mitarbeiter mit einem hohen Gehalt anzeigen, sondern auch ihre durchschnittliche Gehaltsdifferenz zur Gesamtbelegschaft berechnen.

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
),
average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT hse.name, hse.salary, (hse.salary - av.avg_salary) AS salary_difference
FROM high_salary_employees hse, average_salary av;

Hier erstellen wir zwei CTEs:

1. high_salary_employees: Filtert die Mitarbeiter mit einem Gehalt über 50.000.
2. average_salary: Berechnet den Durchschnittsgewinn aller Mitarbeiter.

Die äußere SELECT-Anweisung kombiniert diese beiden CTEs, um die Gehaltsdifferenz zu berechnen.

---

Rekursive CTEs

Eine der beeindruckendsten Funktionen von CTEs ist ihre Fähigkeit, rekursive Abfragen durchzuführen. Dies ist besonders nützlich bei der Arbeit mit Hierarchien oder Baumstrukturen, wie Organisationen oder Kategorien.

Beispiel 3: Rekursive CTE für Organisationshierarchie

Angenommen, wir haben eine Tabelle employees mit den Spalten id, name und manager_id, wobei manager_id die ID des Vorgesetzten eines Mitarbeiters darstellt. Wir möchten alle Untergebenen eines bestimmten Mitarbeiters finden.

WITH RECURSIVE employee_hierarchy AS (
    -- Ankerabfrage: Startpunkt der Hierarchie
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1 -- Beginne mit dem Mitarbeiter mit ID 1

    UNION ALL

    -- Rekursiver Teil: Suche nach Untergebenen
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

In diesem Beispiel: - Die Ankerabfrage startet die Rekursion mit dem Mitarbeiter mit der ID 1. - Der rekursive Teil fügt in jeder Iteration die Untergebenen des aktuellen Mitarbeiters hinzu. - Das Resultset enthält schließlich alle Mitarbeiter in der Hierarchie unter dem Startmitarbeiter.

---

CTEs vs. Unterabfragen

CTEs unterscheiden sich von Unterabfragen in mehreren Aspekten:

Merkmal	CTEs	Unterabfragen
Lesbarkeit	Besser strukturiert und leichter lesbar	Kann schwer lesbar sein
Wiederverwendbarkeit	Innerhalb einer Abfrage wiederverwendbar	Muss oft dupliziert werden
Performance	Optimierung durch DBMS	Kann ineffizient sein

---

Limitationen von CTEs

Obwohl CTEs viele Vorteile bieten, gibt es auch einige Einschränkungen:

1. Gültigkeitsbereich: Eine CTE ist nur innerhalb der Abfrage gültig, in der sie definiert wurde.
2. Keine Indexierung: CTEs können nicht indiziert werden, was bei sehr großen Datensätzen zu Leistungsproblemen führen kann.
3. Keine direkte Persistierung: CTEs sind temporär und können nicht persistent gespeichert werden.

---

Schlussfolgerung

Common Table Expressions sind eine leistungsstarke Erweiterung von SQL, die die Lesbarkeit und Modularität von Abfragen erheblich verbessert. Sie eignen sich besonders gut für komplexe Abfragen und rekursive Operationen. Durch die Verwendung von CTEs können Entwickler ihre SQL-Codebasis klarer und wartbarer gestalten.

---