Common Table Expression, der WITH-Operator

Eine Common Table Expression (CTE) ist eine temporäre Ergebnismenge, auf die du in nachfolgenden Queries zugreifen kannst. Für das Schreiben einer CTE wird der Operator WITH verwendet.

MySQL 8.4
-- Beispiel für die Verwendung der WITH-Konstruktion
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot')

SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;

Ein Ausdruck mit WITH gilt als „temporär“, weil das Ergebnis nirgendwo dauerhaft im Datenbankschema gespeichert wird, sondern wie eine temporäre View nur für die Dauer der Query existiert, also nur während der Ausführung der Operatoren SELECT, INSERT, UPDATE, DELETE oder MERGE verfügbar ist. Eine CTE ist nur innerhalb der Query gültig, zu der sie gehört. Das verbessert die Struktur der Query, ohne den globalen Namensraum zu belasten.

Syntax des WITH-Operators

WITH cte_name [(spalte [, ...])] AS (unterabfrage)
    [, cte_name [(spalte [, ...])] AS (unterabfrage)] ...

Beispiel-Queries

1. Wir bauen die CTE Aeroflot_trips, die alle Flüge der Airline „Aeroflot“ enthält.

MySQL 8.4
WITH Aeroflot_trips AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot')

SELECT * FROM Aeroflot_trips;

2. Genauso bauen wir die CTE Aeroflot_trips, aber mit umbenannten Spalten.

MySQL 8.4
WITH Aeroflot_trips (aeroflot_plane, town_from, town_to) AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot')

SELECT * FROM Aeroflot_trips;

3. Mit dem WITH-Operator definieren wir mehrere CTEs.

MySQL 8.4
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Aeroflot'),
    Don_avia_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = 'Don_avia')

SELECT * FROM Don_avia_trips UNION SELECT * FROM  Aeroflot_trips;

Rekursion in CTEs

CTEs können auch für rekursive Queries eingesetzt werden, die Daten iterativ verarbeiten — zum Beispiel für hierarchische Datenstrukturen wie „Vorgesetzter — Untergebener“.

Syntax einer rekursiven CTE

Eine rekursive CTE besteht aus zwei Teilen, die durch UNION ALL getrennt werden:

• Der Startdatensatz ohne rekursive Verweise.
• Der rekursive Teil: eine Query, die sich auf die CTE selbst bezieht, um die Rekursion fortzusetzen.

WITH RECURSIVE cte_name [(spalte [, ...])] AS (
    -- Startdatensatz
    SELECT ausdruck [, ...]
    FROM tabellen_name
    [WHERE bedingung]

    UNION ALL

    -- Rekursiver Teil
    SELECT ausdruck [, ...]
    FROM cte_name
    INNER JOIN tabellen_name ON verbund_bedingung
    [WHERE bedingung]
)

SELECT * FROM cte_name;

Beispiel: Hierarchie von Vorgesetzten und Untergebenen

Schauen wir uns die Tabelle Employees an, die IDs von Mitarbeitenden und ihren Vorgesetzten enthält:

Gesucht sind alle Untergebenen von John Smith (id=1) auf allen Hierarchieebenen.

MySQL 8.4
WITH RECURSIVE Subordinates AS (
    -- Startdatensatz
    SELECT id, name, managerId
    FROM Employees
    WHERE managerId = 1

    UNION ALL

    -- Rekursiver Teil: Untergebene der Untergebenen
    SELECT e.id, e.name, e.managerId
    FROM Employees e
    INNER JOIN Subordinates s ON e.managerId = s.id
)

SELECT * FROM Subordinates;

Ablauf einer rekursiven CTE

1. Startdatensatz: Alle Mitarbeitenden mit managerId=1 werden ausgewählt (direkte Untergebene von John Smith).
2. Rekursiver Teil: Für jeden im Startdatensatz ausgewählten Mitarbeiter werden dessen Untergebene geholt (wo managerId gleich der id des ausgewählten Mitarbeiters ist).
3. Vereinigung: Die Ergebnisse aus Startdatensatz und rekursivem Teil werden mit UNION ALL zusammengeführt.
4. Rekursion: Der Vorgang wiederholt sich für jede neue Gruppe von Untergebenen, bis alle Hierarchieebenen abgedeckt sind.

Fazit

CTEs wurden in SQL eingeführt, um komplexe, lange Queries — insbesondere mit vielen Subqueries — zu vereinfachen. Ihr Hauptzweck ist, Queries lesbarer, einfacher zu schreiben und besser zu warten zu machen. Das wird erreicht, indem große und komplexe Queries in benannten Ausdrücken versteckt werden, die dann in der Hauptquery verwendet werden.