Temporäre Tabellen mit WITH erstellen

Temporäre Tabellen sind ein super Weg, um Zwischenergebnisse zu speichern, mit denen du später weiterarbeiten kannst. Das ist wie beim Programmieren: Statt immer wieder denselben fetten Codeblock zu kopieren, packst du ihn einmal in eine Variable und nutzt ihn dann entspannt weiter. Im SQL-Universum übernehmen temporäre Tabellen oft diese "Variablen"-Rolle.

Aber CTE macht das Ganze noch einfacher – damit kannst du temporäre Tabellen ohne extra Aufwand bauen:

• Kein Stress mit Löschen.

  Ein CTE lebt nur während der Ausführung des Queries. Sobald SQL fertig ist – verschwindet das CTE wie ein unsichtbarer Assistent, der alles erledigt und dann spurlos verschwindet.

• Der Code wird klarer.

  Ein CTE zu bauen ist viel entspannter, als sich mit dem Erstellen und Löschen einer physischen temporären Tabelle rumzuschlagen. Alles ist direkt sichtbar und auf den Punkt.

• Perfekt für "Einmal"-Aufgaben.

  Wenn du Daten nur in einem Schritt bearbeiten willst – ist ein CTE ideal. Schnell, sauber, ohne Nebenwirkungen.

CTE-Syntax als temporäre Tabelle

Erstmal die CTE-Syntax auffrischen:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Hier erstellt WITH einen temporären "Tabellen-Ausdruck", der im gesamten Query verfügbar ist, in dem er definiert wurde. Dieser Ausdruck sieht aus wie eine Tabelle, lebt aber nur bis zum Ende des Queries.

Beispiel: Lass uns die Überflieger zählen

Lass uns mit einem CTE eine temporäre Tabelle bauen, um die Studenten zu finden, deren Durchschnittsnote (grade) über 90 liegt. Danach geben wir ihre Liste aus.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Hier machen wir folgendes:

• Wir nutzen WITH, um die temporäre Tabelle high_achievers zu erstellen.
• Im CTE gruppieren wir die Noten (grades) für jeden Studenten (student_id) und berechnen den Durchschnitt.
• Im SELECT *-Query greifen wir einfach auf die temporäre Tabelle high_achievers zu, als wäre es eine normale Tabelle.

Vergleich: CTE vs. temporäre Tabellen

Manchmal fragt man sich: Was ist eigentlich der Unterschied zwischen CTEs und temporären Tabellen, die mit CREATE TEMP TABLE gebaut werden?

Hier ein Beispiel für eine klassische temporäre Tabelle (CREATE TEMP TABLE) mit denselben Daten:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- Vergiss nicht, die Tabelle zu löschen!

Und das gleiche Query mit CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Wann CTE, wann temporäre Tabelle?

CTEs sind top, wenn du schnell Zwischendaten vorbereiten und direkt weiterverarbeiten willst – ohne extra Aufwand. Du musst dich nicht ums Löschen kümmern: Das CTE verschwindet automatisch, sobald das Query fertig ist. Es ist direkt im SQL-Code sichtbar, versteckt sich nicht irgendwo außerhalb wie eine temporäre Tabelle. Das macht Queries sauberer, einfacher und verständlicher, besonders wenn du mehrere Verarbeitungsschritte hintereinander hast. Außerdem kannst du CTEs kombinieren, ineinander verschachteln und komplexe Logik bauen – dazu später mehr.

So eine Struktur ist super, wenn das Query einmalig ist und die Daten nur in diesem Query gebraucht werden. Wenn du das Ergebnis aber mehrfach in verschiedenen Teilen des Systems nutzen willst oder Zwischendaten über die ganze Session behalten möchtest – dann sind temporäre Tabellen die zuverlässigere Wahl. Besonders bei großen Datenmengen und wenn Performance wichtig ist: In solchen Fällen laufen physische temporäre Tabellen stabiler und schneller.

Es kommt also auf die Aufgabe an: CTE ist ein schneller, eleganter Helfer für lokale Datenverarbeitung. Die temporäre Tabelle ist das Arbeitspferd für komplexere und längere Szenarien.

Beispiel: Daten aggregieren

Angenommen, wir haben eine Tabelle enrollments, in der steht, welche Studenten in welchen Kursen eingeschrieben sind. Wir wollen wissen, wie viele Studenten pro Kurs eingeschrieben sind – aber nur Kurse mit mehr als 5 Studenten zählen.

Mit CTE geht das so:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Mehrere CTEs: Schritte aufbauen

Was, wenn du die Aufgabe in mehrere Schritte aufteilen willst? Zum Beispiel erst die Studenten mit hohem Durchschnitt finden und dann ihre Kurse raussuchen? Kein Problem!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Jetzt gibt's im Query zwei CTEs:

1. high_achievers findet die Überflieger.
2. student_courses ordnet Studenten den Kursen zu.

Das Ergebnis: Eine Liste der Studenten mit hohem Durchschnitt und ihren Kursen.

Typische Fehler bei CTEs

Zu viele Daten. CTEs werden im Speicher von PostgreSQL materialisiert. Wenn du ein CTE mit riesigem Ergebnis baust, kann das das Query verlangsamen oder den Speicher sprengen.

Overkill. Wenn du CTEs nutzt, wo ein normaler Subquery einfacher wäre, wird's schnell unübersichtlich.

Vergessene Indizes. Wenn die Daten im CTE aus großen Tabellen ohne Indizes kommen, wird das Query langsam.