Einführung in CTE: WITH

In der Programmierung kannst du einen Teil des Codes auslagern und ihm einen Namen geben – also eine Funktion erstellen. Genau so läuft das mit CTE. Du kannst einen SELECT-Subquery aus dem Hauptquery rausziehen, ihm einen Namen geben und ihn dann im Haupt-SQL-Query verwenden.

CTE (Common Table Expressions, oder gemeinsame Tabellenausdrücke) – das ist wie ein frischer Wind für alle, die genug von verschachtelten Queries haben. Sie machen SQL-Code nicht nur verständlich, sondern auch richtig elegant. Wenn du früher vor riesigen Subquery-Konstrukten gesessen hast und schon fast schielst – dann ist es Zeit, die "Magie" von CTE kennenzulernen.

Stell dir vor, du baust ein Haus. Normalerweise willst du am liebsten sofort Fenster einsetzen, Türen anschrauben (also direkt Subqueries reinhauen), auch wenn die Wände noch nicht stehen. Mit CTE läuft das anders: Erst machst du einen sauberen Entwurf – du erstellst eine temporäre Tabelle, wie einen Bauplan. Und dann, Schritt für Schritt, baust du die Etagen deines Queries auf. Stylisch, stabil, technisch.

Im Grunde sind CTE virtuelle Tabellen, die du on the fly mit einem SELECT-Query erstellst. So was wie Subqueries, nur cooler. Wenn du in der Programmierung einen Teil der Logik in eine eigene Funktion mit klarem Namen auslagern kannst, übernimmt im SQL das CTE diese Rolle. Du schreibst einen SELECT, gibst ihm einen Namen – und benutzt ihn dann als Teil eines großen, komplexen Queries. Nice, oder?

Beispiel für einen SQL-Query mit Subquery:

-- Hauptquery
SELECT *
FROM (
    SELECT *
    FROM studenten
    WHERE note > 75
) AS gefilterte_studenten; -- Subquery, der den Alias gefilterte_studenten bekommen hat

Subquery separat ausgelagert:

-- CTE/Subquery, der den Alias gefilterte_studenten bekommen hat
WITH gefilterte_studenten AS (
    SELECT *
    FROM studenten
    WHERE note > 75
)

-- Hauptquery
SELECT *
FROM gefilterte_studenten;

Kaum zu glauben, aber Subqueries gab es schon 20 Jahre vor CTE! Im SQL-89-Standard gab es schon Subqueries, aber CTE kamen erst mit SQL-2009 dazu.

Syntax von WITH

Ein CTE startet mit dem Schlüsselwort WITH und sieht ungefähr so aus:

WITH cte_name AS (
    SELECT ... -- dein Query hier
)

SELECT ...
FROM cte_name;

Hier:

• cte_name – das ist der Name deines CTE. Du kannst jeden sinnvollen Namen nehmen, zum Beispiel high_scores, filtered_data oder auch beste_studenten.
• In den Klammern () steht der Query, der die Daten für die weitere Nutzung vorbereitet.
• Nach der Definition vom CTE kannst du ihn wie eine normale Tabelle im Hauptquery verwenden.

Beispiel 1: Einfaches CTE

Schauen wir uns mal an, wie ein CTE in der Praxis funktioniert. Stell dir vor, wir haben eine Tabelle studenten – eine Liste von Studenten mit ihren Noten:

Unser Ziel – alle Studenten mit einer Note über 85 auswählen und ihre Daten anzeigen.

Variante ohne CTE:

Das geht mit einem Subquery so:

SELECT *
FROM (
    SELECT *
    FROM studenten
    WHERE note > 85
) AS gefilterte_studenten;

Und so, aber mit CTE – viel angenehmer zu lesen:

WITH gefilterte_studenten AS (
    SELECT *
    FROM studenten
    WHERE note > 85
)
SELECT *
FROM gefilterte_studenten;

Sieht doch gleich viel sauberer und verständlicher aus, oder? Wir haben die Datenvorbereitung (WITH) klar von dem Hauptteil des Queries (SELECT) getrennt. Das ist wie erst mal den Schreibtisch aufräumen, bevor du loslegst – direkt viel entspannter.

Beispiel 2: Mehrere CTE

Du kannst auch mehrere CTE in einem Query definieren. Das ist besonders praktisch, wenn du die Daten Schritt für Schritt vorbereiten willst.

Gegeben: Tabelle noten, in der die Noten der Studenten pro Kurs gespeichert sind:

Aufgabe: Für jeden Studenten die Durchschnittsnote finden und dann die auswählen, deren Note über 85 liegt.

Lösung mit mehreren CTE:

WITH studenten_durchschnitt AS (
    SELECT student_id, AVG(note) AS durchschnitt_note
    FROM noten
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, durchschnitt_note
    FROM studenten_durchschnitt 	-- wir greifen auf das erste CTE - studenten_durchschnitt - zu
    WHERE durchschnitt_note > 85
)

SELECT *
FROM high_achievers; -- wir greifen auf das zweite CTE - high_achievers - zu

Hier:

1. studenten_durchschnitt bereitet die Zwischendaten vor – die Durchschnittsnoten der Studenten.
2. high_achievers nutzt diese Daten, um nur die auszuwählen, deren Noten über 85 liegen.

Unterschied zwischen CTE und Subqueries

Spoiler: CTE ersetzen Subqueries nicht, aber manchmal sind sie viel praktischer.

Ein Subquery ist ein Query im Query. Sie sind praktisch, wenn du schnell ein Ergebnis brauchst, aber wenn es zu viele werden, wird der Code schnell chaotisch.

Beispiel:

SELECT *
FROM (
    SELECT student_id, AVG(note) AS durchschnitt_note
    FROM noten
    GROUP BY student_id
) AS studenten_durchschnitt
WHERE durchschnitt_note > 85;

Subqueries können im SELECT, im FROM, im WHERE und im HAVING stehen. Außerdem können sie auf Spalten des äußeren Queries zugreifen. Bei CTE ist das mit dem letzten Punkt schwierig.

CTE machen den Code dagegen viel lesbarer, was die Wartung erleichtert und Fehler reduziert. Anstatt einen Query in den anderen zu verschachteln, kannst du mit CTE einfach das Ergebnis eines Subqueries "benennen" und später verwenden.

WITH studenten_durchschnitt AS (
    SELECT student_id, AVG(note) AS durchschnitt_note
    FROM noten
    GROUP BY student_id
)

SELECT *
FROM studenten_durchschnitt
WHERE durchschnitt_note > 85;

CTE sind besonders praktisch, wenn du vorbereitete Daten mehrmals im selben Query brauchst.

Wann solltest du CTE verwenden?

• Wenn du einen komplexen Query in mehrere logische Schritte aufteilen willst.
• Wenn der Query lesbar und wartbar sein soll. Niemand hat Bock, sich durch verschachtelte Spaghetti-Queries zu kämpfen.
• Für die temporäre Vorbereitung von Daten, die nur im aktuellen Query gebraucht werden.

Abschließendes Beispiel: Kursanalyse

Lass uns alles zusammenbringen, was wir gelernt haben:

1. Wir finden Studenten mit hoher Durchschnittsnote.
2. Wir zeigen ihre Namen und die Kurse, für die sie eingeschrieben sind.

WITH studenten_durchschnitt AS (
    SELECT student_id, AVG(note) AS durchschnitt_note
    FROM noten
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM studenten_durchschnitt
    WHERE durchschnitt_note > 85
),
studenten_kurse AS (
    SELECT e.student_id, c.kurs_name
    FROM einschreibungen e
    JOIN kurse c ON e.kurs_id = c.kurs_id
)

SELECT ha.student_id, sk.kurs_name
FROM high_achievers ha
JOIN studenten_kurse sk ON ha.student_id = sk.student_id;

Schau mal, wie alles strukturiert ist:

1. Erst wurden die Durchschnittsnoten vorbereitet.
2. Dann wurden nur die besten Studenten ausgewählt.
3. Danach wurden sie mit den Kursen verknüpft.

Jetzt bist du offiziell bereit, CTE zu nutzen, um schöne, lesbare und mächtige SQL-Queries zu bauen.

Los geht’s – ran an deine eigenen Projekte!