SQL - Gemeinsame Tabellenausdrücke (CTE)

Hallo da draußen, zukünftige SQL-Zauberer! Heute machen wir uns auf eine aufregende Reise in die Welt der Gemeinsamen Tabellenausdrücke, kurz CTEs. Keine Sorge, wenn du neu im Programmieren bist – ich werde dich Schritt für Schritt durchführen, genau wie ich es in den letzten Jahren für unzählige Schüler getan habe. Hol dir dein Lieblingsgetränk, setze dich bequem hin, und tauchen wir ein!

Der SQL Gemeinsame Tabellenausdruck

Stell dir vor, du organisierst eine große Party (wer liebt nicht eine gute Analogie?). Du hast eine Liste von Aufgaben und möchtest sie in kleinere, handhabbare Teile aufteilen. Genau das macht ein Gemeinsamer Tabellenausdruck in SQL – er hilft uns, komplexe Abfragen in einfachere, lesbarere Teile zu zerlegen.

Ein CTE ist wie ein temporäres benanntes Ergebnis, auf das du in einer SELECT-, INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung Bezug nehmen kannst. Es wird innerhalb des Ausführungsbereichs einer einzigen Anweisung definiert. Denk daran als Erstellung einer temporären Tabelle, die nur für deine Abfrage existiert.

Schauen wir uns ein einfaches Beispiel an:

WITH cte_beispiel AS (
SELECT 'Hallo, CTE!' AS begrüßung
)
SELECT begrüßung FROM cte_beispiel;

In diesem Beispiel:

1. Beginnen wir mit dem WITH-Schlüsselwort, das den Anfang unseres CTE anzeigt.
2. Wir geben unserem CTE einen Namen: cte_beispiel.
3. Wir definieren, was unser CTE enthalten wird: in diesem Fall eine einfache SELECT-Anweisung, die eine Spalte namens 'begrüßung' mit dem Wert 'Hallo, CTE!' erstellt.
4. Nach der Definition des CTE haben wir unsere Hauptabfrage, die den CTE verwendet.

Wenn du das ausführst, wirst du sehen:

| begrüßung    |
|--------------|
| Hallo, CTE!  |

Ist das nicht toll? Wir haben gerade unseren ersten CTE erstellt!

Die MySQL WITH-Klausel

Nun, lassen wir uns über die WITH-Klausel in MySQL unterhalten. Es ist der magische Zauberstab, der unsere CTEs zum Leben erweckt. Die allgemeine Syntax sieht so aus:

WITH cte_name [(spalten_liste)] AS (abfrage)
SELECT * FROM cte_name;

Hier ist ein praktischerer Beispiel. Angenommen, wir haben eine Tabelle mit Mitarbeitern:

CREATE TABLE mitarbeiter (
id INT PRIMARY KEY,
name VARCHAR(50),
abteilung VARCHAR(50),
gehalt DECIMAL(10, 2)
);

INSERT INTO mitarbeiter VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

Nun verwenden wir einen CTE, um das Durchschnittsgehalt nach Abteilung zu finden:

WITH abt_durchschnittsgehalt AS (
SELECT abteilung, AVG(gehalt) AS durchschnittsgehalt
FROM mitarbeiter
GROUP BY abteilung
)
SELECT * FROM abt_durchschnittsgehalt
ORDER BY durchschnittsgehalt DESC;

Diese Abfrage gibt uns:

| abteilung | durchschnittsgehalt |
|-----------|---------------------|
| IT        | 62500.00            |
| Finance   | 55000.00            |
| HR        | 51000.00            |

Hier haben wir einen CTE verwendet, um das Durchschnittsgehalt für jede Abteilung zu berechnen, und dann haben wir aus diesem CTE ausgewählt, um die Ergebnisse anzuzeigen. Es ist, als hätten wir eine temporäre Tabelle mit den Durchschnittsgehältern erstellt, die wir dann in unserer Hauptabfrage verwendet haben.

CTE aus mehreren Tabellen

CTEs sind nicht auf nur eine Tabelle beschränkt. Wir können in unseren CTE-Definitionen genauso viele Tabellen verwenden wie in regulären Abfragen. Lassen wir eine Abteilungstabelle zu unserem Beispiel hinzufügen:

CREATE TABLE abteilungen (
id INT PRIMARY KEY,
name VARCHAR(50),
standort VARCHAR(50)
);

INSERT INTO abteilungen VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

Nun verwenden wir einen CTE, um Informationen aus beiden Tabellen zu kombinieren:

WITH abt_info AS (
SELECT m.abteilung,
AVG(m.gehalt) AS durchschnittsgehalt,
a.standort
FROM mitarbeiter m
JOIN abteilungen a ON m.abteilung = a.name
GROUP BY m.abteilung, a.standort
)
SELECT * FROM abt_info
ORDER BY durchschnittsgehalt DESC;

Dies gibt uns:

| abteilung | durchschnittsgehalt | standort      |
|-----------|---------------------|---------------|
| IT        | 62500.00            | San Francisco |
| Finance   | 55000.00            | Chicago       |
| HR        | 51000.00            | New York      |

In diesem Beispiel verknüpft unser CTE die Mitarbeiter- und Abteilungstabellen, berechnet das Durchschnittsgehalt und enthält die Standortinformationen.

Rekursiver CTE

Nun kommen wir zu etwas wirklich Interessantem – rekursiven CTEs! Diese sind wie die russischen Matrjoschka-Puppen der SQL-Welt. Ein rekursiver CTE bezieht sich auf sich selbst, was es ermöglicht, mit hierarchischen oder baumartigen Daten zu arbeiten.

Lassen wir ein einfaches Beispiel einer Mitarbeiterhierarchie erstellen:

CREATE TABLE mitarbeiter_hierarchie (
id INT PRIMARY KEY,
name VARCHAR(50),
vorgesetzter_id INT
);

INSERT INTO mitarbeiter_hierarchie VALUES
(1, 'Großer Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Mitarbeiter 1', 2),
(5, 'Mitarbeiter 2', 2),
(6, 'Mitarbeiter 3', 3);

Nun verwenden wir einen rekursiven CTE, um die gesamte Hierarchie anzuzeigen:

WITH RECURSIVE mitarbeiter_hierarchie_rekursiv AS (
SELECT id, name, vorgesetzter_id, 0 AS ebene
FROM mitarbeiter_hierarchie
WHERE vorgesetzter_id IS NULL

UNION ALL

SELECT m.id, m.name, m.vorgesetzter_id, mh.ebene + 1
FROM mitarbeiter_hierarchie m
JOIN mitarbeiter_hierarchie_rekursiv mh ON m.vorgesetzter_id = mh.id
)
SELECT * FROM mitarbeiter_hierarchie_rekursiv
ORDER BY ebene, id;

Diese Abfrage produziert:

| id | name       | vorgesetzter_id | ebene |
|----|------------|-----------------|-------|
| 1  | Großer Boss| NULL            | 0     |
| 2  | Manager A  | 1               | 1     |
| 3  | Manager B  | 1               | 1     |
| 4  | Mitarbeiter 1| 2              | 2     |
| 5  | Mitarbeiter 2| 2              | 2     |
| 6  | Mitarbeiter 3| 3              | 2     |

Dieser rekursive CTE beginnt mit dem obersten Mitarbeiter (Großer Boss) und findet dann rekursiv alle Mitarbeiter, die jedem Vorgesetzten berichten.

Vorteile von CTE

CTEs bieten mehrere Vorteile:

Vorteil	Beschreibung
Lesbarkeit	CTEs machen komplexe Abfragen lesbarer, indem sie sie in benannte Unterabfragen aufteilen.
Wiederverwendbarkeit	Du kannst einen CTE mehrmals innerhalb einer Abfrage referenzieren.
Rekursion	CTEs ermöglichen es, rekursive Abfragen zu schreiben, die für hierarchische Daten großartig sind.
Vereinfachung	Sie können komplexe Verknüpfungen und Unterabfragen vereinfachen.
Wartung	CTEs machen Abfragen einfacher zu warten und zu ändern.

Nachteile von CTE

Obwohl CTEs leistungsstark sind, haben sie einige Einschränkungen:

Nachteil	Beschreibung
Leistung	In einigen Fällen könnten CTEs nicht so gut performen wie abgeleitete Tabellen oder Ansichten.
Gültigkeitsbereich	CTEs sind nur im Gültigkeitsbereich der einzigen Anweisung gültig, in der sie definiert sind.
Komplexität	Für sehr einfache Abfragen könnte die Verwendung eines CTE unnötige Komplexität hinzufügen.
Datenbankunterstützung	Nicht alle Datenbankysteme unterstützen CTEs, obwohl die meisten modernen dies tun.

Und das war's, Leute! Wir haben die Welt der Gemeinsamen Tabellenausdrücke durchquert, von den grundlegenden Konzepten bis hin zu rekursiven Abfragen. Denke daran, dass das Erlernen jeder neuen Fähigkeit Übung erfordert. Also probiere CTEs in deinen eigenen Abfragen aus. Bereit zum Query-Schreiben!

Credits: Image by storyset