Wann sollte ich einen gemeinsamen Tabellenausdruck (CTE) verwenden?

Möchten Sie mehr über Common Table Expressions oder CTEs erfahren? Möchten Sie wissen, wann CTEs nützlich sind? Lesen Sie weiter - wir diskutieren in diesem Artikel.

Wenn Sie schon einmal von gemeinsamen Tabellenausdrücken gehört haben, haben Sie sich wahrscheinlich gefragt, was sie bewirken. Und selbst wenn nicht, ist es gut, dass Sie hier sind! CTEs können sehr nützlich sein, vor allem, wenn Sie bereits die Grundlagen von SQL beherrschen, z. B. das Auswählen, Ordnen und Filtern von Daten und das Verbinden von Tabellen. Kommen wir gleich zur Sache und sehen wir uns an, was CTEs sind und wann und wie man sie verwendet.

Was ist ein gemeinsamer Tabellenausdruck?

Ein gemeinsamer Tabellenausdruck (CTE) ist eine relativ neue SQL-Funktion. Er wurde in SQL:1999, der vierten SQL-Revision, eingeführt, und von 1999 bis 2002 wurden ISO-Standards für diese Version von SQL herausgegeben.

CTEs wurden erstmals 2005 in SQL Server eingeführt, dann stellte PostgreSQL sie ab Version 8.4 im Jahr 2009 zur Verfügung. MySQL wartete etwas länger und stellte sie 2018 ab Version 8.0 zur Verfügung. Einfach ausgedrückt handelt es sich um einen temporären Datensatz, der von einer Abfrage zurückgegeben und dann von einer anderen Abfrage verwendet wird. Er ist temporär, weil das Ergebnis nirgendwo gespeichert wird; es existiert nur, wenn die Abfrage ausgeführt wird.

Es gibt zwei Arten von CTEs:

• nicht rekursiv
• rekursiv

Ich werde in diesem Artikel nur auf die nicht-rekursive CTE eingehen und die rekursive CTE erst am Ende kurz erwähnen.

Die grundlegende Syntax der (nicht rekursiven) CTE lautet wie folgt:

WITH expression_name AS (CTE definition)

Wie Sie sehen können, wird eine WITH -Anweisung verwendet. Aus diesem Grund werden CTEs auch WITH Abfragen genannt. Nach dem WITH definieren Sie in Klammern einen CTE. CTE-Definition bedeutet einfach, dass Sie eine SELECT Abfrage schreiben, die Ihnen ein Ergebnis liefert, das Sie in einer anderen Abfrage verwenden möchten.

Wie Sie sehen können, geschieht dies mit Hilfe einer WITH-Anweisung. Aus diesem Grund werden CTEs auch WITH-Abfragen genannt. Nach WITH definieren Sie ein CTE in Klammern. CTE-Definition bedeutet einfach, dass Sie eine SELECT-Abfrage schreiben, die Ihnen ein Ergebnis liefert, das Sie in einer anderen Abfrage verwenden möchten.

SELECT ... 
FROM expression_name

Sie definieren Ihre SELECT Abfrage und verweisen dann auf Ihr CTE, wobei Sie es wie jede andere Tabelle nach der FROM Klausel verwenden.

Wenn Sie mehr über CTEs lesen möchten, bevor Sie zu den Beispielen übergehen, finden Sie hier einen Artikel, der sie sehr gut erklärt.

Die CTE-Syntax

Schauen wir uns nun an, wie die CTE-Syntax in der Praxis funktioniert. Nehmen wir an, es gibt eine Datenbank, die verschiedene Daten der Universität mit den folgenden drei Tabellen enthält:

• students
• subjects
• exams

Die Tabelle students hat die folgenden Spalten:

• iddie ID des Studenten, ein Primärschlüssel
• first_nameVorname des Studenten
• last_nameNachname des Studenten

Die nächste Tabelle ist die subjects Tabelle, die die Daten enthält:

• idID des Fachs, ein Primärschlüssel
• subject_name: der Name des Fachs

Die dritte Tabelle ist die exams Tabelle, die die folgenden Daten speichert:

• id: die ID der abgelegten Prüfung
• exam_date: das Datum, an dem die Prüfung abgelegt wurde
• subject_idID des Fachs, ein Fremdschlüssel aus der Tabelle subjects
• student_iddie ID des Studenten, der die Prüfung abgelegt hat, ein Fremdschlüssel aus der Tabelle students

Ihre Aufgabe ist es, die Durchschnittsnote der Schüler zu berechnen. Dann müssen Sie für jeden Schüler, dessen Durchschnittsnote über 8,5 liegt, seinen Vor- und Nachnamen und seine Durchschnittsnote anzeigen und ihn als "außergewöhnlichen" Schüler bezeichnen. Wie würden Sie dies mit einem CTE tun?

Der Code, der Ihnen das gewünschte Ergebnis liefert, kann wie folgt geschrieben werden:

WITH grade_average AS (
SELECT	s.id,
		s.first_name,
		s.last_name,
		AVG (e.grade) AS average_grade 
FROM students s JOIN exams e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name
)

SELECT	first_name,
		last_name,
		average_grade,
		'exceptional' AS tag
FROM grade_average
WHERE average_grade>8.5;

Zuerst müssen Sie Ihr CTE definieren. Wie Sie bereits gelernt haben, geschieht dies mit einer WITH -Anweisung. Es folgt der Name des CTE, der in diesem Fall grade_average in diesem Fall. Eine CTE-Abfrage wird zwischen den Klammern definiert. Für sich genommen ist sie nicht kompliziert; es handelt sich um eine ganz normale SELECT Abfrage. Sie wählt id, first_name und die last_name aus der Tabelle students. Sie berechnet auch die Durchschnittsnote, indem sie die Spalte grade aus der Tabelle exams. Das Ergebnis wird in der neuen Spalte average_grade angezeigt. Die Tabellen students und exams werden über die entsprechende Schüler-ID-Spalte aus jeder Tabelle verknüpft. Das Ergebnis wird nach den Spalten id, first_name, und last_name der Tabelle students. Die Datensätze werden gruppiert, da Sie das Ergebnis nach Schülern erhalten müssen.

Nachdem die CTE definiert ist, gibt es eine weitere SELECT Abfrage, die die CTE verwendet. Diese Abfrage wählt die Spalten first_name, last_name, und average_grade aus der CTE aus, grade_average. Sie weist außerdem den Wert "exceptional" zu. Es gibt eine WHERE Klausel am Ende, um nur die Schüler mit einem Notendurchschnitt über 8,5 anzuzeigen.

Wenn Sie die Abfrage ausführen, erhalten Sie die Namen von drei außergewöhnlichen Schülern.

Verwendung von mehr als einem CTE in einer Abfrage

Es ist möglich, mehr als eine CTE in einer Abfrage zu definieren und zu verwenden. Trennen Sie dazu jeden CTE mit einem Komma und verwenden Sie eine WITH Anweisung nur bei der Definition des ersten CTE.

Ich möchte Ihnen ein Beispiel zeigen. Mit den gleichen Tabellen wie im vorherigen Beispiel haben Sie folgende Aufgabe: Zeigen Sie die Namen der Fächer und ihre jeweiligen Durchschnitts- und Mindestnoten an, aber nur für die Fächer, in denen alle die Prüfung bestanden haben, d. h. ihre Note ist 5 oder höher.

Um das gewünschte Ergebnis zu erhalten, sollte Ihre Abfrage wie folgt aussehen:

WITH subject_average AS (
SELECT	su.id,
su.subject_name,
		AVG (e.grade) AS subject_average_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
),

min_grade AS (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS subject_min_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
HAVING MIN (e.grade) > 5
)

SELECT	sa.id,
		sa.subject_name,
		sa.subject_average_grade
FROM subject_average sa JOIN min_grade m ON sa.id =m.id;

Zunächst wird ein CTE namens subject_average definiert. Sie selektiert die Spalten id und subject_name aus der Tabelle subjects. Anschließend werden die Durchschnittsnoten anhand der Daten aus der Tabelle exams und ordnet die Ergebnisse in die neue Spalte subject_average_grade ein. Dann gruppiert es die Daten, um das Ergebnis nach Fach zu erhalten.

Jetzt definieren Sie das zweite CTE. Erinnern Sie sich daran, was ich vorhin gesagt habe - Sie trennen CTEs mit Kommas und schreiben die zweite CTE ohne die WITH-Anweisung. Die zweite CTE heißt hier min_grade. Sie wählt ebenfalls id und subject_name aus der Tabelle subjects berechnet dann die Mindestnoten und zeigt das Ergebnis in der neuen Spalte subject_min_grade an. Sie gruppiert die Daten wie in der ersten CTE. Da Sie das Ergebnis nur für die Fächer benötigen, in denen alle Schüler bestanden haben, verwenden Sie eine HAVING Klausel, um nur die Fächer auszuwählen, in denen die Mindestnote 5 oder höher ist.

Schließlich schreiben Sie die SELECT-Abfrage, die die Fach-ID, den Fachnamen und die Durchschnittsnote für jedes Fach, das die Kriterien erfüllt, anzeigt. Es gibt nur zwei solcher Fächer:

Sobald Sie die Grundlagen von CTEs gelernt haben, gibt es den Kurs Rekursive Abfragen mit vielen weiteren Beispielen, in denen Sie das Schreiben der Syntax üben können.

Wann verwendet man CTEs?

Mit CTEs können Sie mehrstufige Aggregationen durchführen. Was sind CTEs?

Kehren wir zu den Tabellen zurück, die wir in den vorherigen Beispielen verwendet haben. Die Aufgabe besteht nun darin, die durchschnittliche Mindestnote und die durchschnittliche Höchstnote nach Fach zu berechnen.

Wo würden Sie beginnen? Wenn Sie logisch denken, sollten Sie zunächst die Mindest- und Höchstnoten pro Fach ermitteln und dann den Durchschnitt der Ergebnisse pro Fach berechnen. Das ist ganz einfach - der Code sieht wie folgt aus:

SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade,
		AVG (MIN (e.grade)) AS avg_min_grade,
		AVG (MAX (e.grade)) AS avg_max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name;

Logischerweise wird zunächst versucht, die Mindest- und Höchstnote pro Fach zu berechnen und dann den Durchschnitt dieser Werte. Voilà! Wenn Sie den Code nun ausführen, erhalten Sie eine Meldung, die wie folgt aussieht:

Msg 130, Level 15, State 1, Line 16
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Hm, nicht das Ergebnis, das Sie sich erhofft haben? Das liegt daran, dass SQL Konstruktionen wie AVG (MIN (e.grade)) nicht zulässt. Ihre Gedanken waren richtig, aber Sie müssen sie mit einer CTE in einen SQL-Code übersetzen. Das geht folgendermaßen:

WITH min_max_grade AS (
SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
)

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM min_max_grade;

Die CTE hat den Namen min_max_grade. Sie enthält eine SELECT Anweisung, die die Mindest- und Höchstnoten nach Fach berechnet, wie ich es in der Abfrage, die die Fehlermeldung zurückgegeben hat, vorhatte. Das Ergebnis wird in den neuen Spalten min_grade und max_grade angezeigt. Ein CTE hilft nun, Ihre Logik in Code zu übersetzen.

Nachdem Sie die CTE definiert haben, schreiben Sie eine SELECT Anweisung, die den Durchschnitt von min_grade und max_grade aus der CTE errechnet. Das Ergebnis wird in den neuen Spalten avg_min_grade und avg_max_grade angezeigt. Jetzt, wo Sie es sehen, ist es ganz einfach, oder?

CTEs sind auch sehr hilfreich, wenn Sie lange und komplexe Abfragen organisieren müssen. Die Verwendung von CTEs verbessert die Lesbarkeit Ihres Codes, da der Code in einzelne Schritte aufgeteilt wird. Es wird einfacher, den Code zu ändern oder Fehler zu korrigieren. Wenn Sie darauf bestehen würden, keine CTEs zu verwenden, könnte Ihr Code wie folgt aussehen:

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
	FROM subjects su JOIN exams e ON su.id = e.subject_id
	GROUP BY su.id, su.subject_name
) AS min_max;

Verglichen mit der Lösung, die eine CTE verwendet, wirkt dies etwas unübersichtlich und ist schwieriger zu lesen. Das Lesen von Unterabfragen kann schwierig sein, weil Sie zuerst darüber nachdenken müssen, was die einzelnen Unterabfragen tun, dann zur Hauptabfrage zurückkehren und sie alle irgendwie im Kopf miteinander verbinden müssen. Außerdem widerspricht die Verwendung von Unterabfragen der logischen Denkweise und der Art und Weise, wie Sie über die Schritte nachdenken, die Sie zur Lösung führen würden. Erinnern Sie sich daran, dass Sie das Problem in zwei Schritte unterteilt haben: Berechnen Sie zunächst die Mindest- und Höchstnoten für jedes Fach, und berechnen Sie dann den Durchschnitt aus den Mindest- und Höchstnoten. Der CTE-Code spiegelt diese Reihenfolge genau wider.

Die Logik im Code mit einer Unterabfrage ist das Gegenteil von dem, was Sie sich bei der Lösung gedacht haben. Hier schreiben wir zuerst, dass wir einen Durchschnitt der Noten wollen, und geben dann in der Subquery an, dass der Durchschnitt aus den Mindest- und Höchstnoten gebildet werden soll. Wenn Sie eine Unterabfrage verwenden, läuft die Art und Weise, wie Sie den Code schreiben, in der Regel den logischen Überlegungen zuwider.

Und wenn der Code mit einer Subquery in diesem einfachen Beispiel weniger lesbar und schwieriger zu verstehen ist als der Code mit einer CTE, stellen Sie sich vor, wie es wäre, wenn Sie komplexere Abfragen schreiben müssten! Sie würden sich am Kopf kratzen und sich wirklich anstrengen, nur um zu verstehen, was jeder Teil des Codes tut. Schwierigkeiten, einen Code zu verstehen, können sehr frustrierend sein. Hier können Ihnen CTEs helfen.

Sie haben wahrscheinlich bemerkt, dass CTEs Unterabfragen sehr ähnlich sind. Vielleicht haben Sie sich gefragt, warum ich CTEs verwende, wenn alles, was ich getan habe, auch mit Unterabfragen hätte getan werden können. Das stimmt, aber abgesehen davon, dass sie besser lesbar sind, haben CTEs einen großen Vorteil gegenüber Unterabfragen: Die Ergebnisse einer CTE können mehr als einmal in einer Abfrage verwendet werden. Wenn Sie mehr über dieses Thema erfahren möchten, empfehle ich Ihnen, sich über die Unterschiede zwischen CTEs und Unterabfragen zu informieren.

Ich habe bereits erwähnt, dass CTEs nicht rekursiv oder rekursiv sein können. Bisher haben wir uns nur mit nicht rekursiven CTEs beschäftigt. Rekursive CTEs sind CTEs, die auf sich selbst verweisen; dabei geben sie das Teilergebnis zurück und wiederholen den Vorgang, bis sie das Endergebnis liefern. Mit rekursiven CTEs lassen sich die Möglichkeiten von CTEs voll ausschöpfen; sie sind nützlich bei der Verarbeitung hierarchischer Strukturen wie Bäumen und Graphen.

Haben Sie den Dreh raus, wie man CTEs verwendet?

In diesem Artikel haben wir einige der Grundlagen von CTEs behandelt. Sie haben gelernt, was ein CTE ist, seine Syntax verstanden und einige einfache Beispiele durchgesehen, um ein Gefühl dafür zu bekommen, was CTEs leisten können. Ich habe auch einige gängige Verwendungsmöglichkeiten von CTEs aufgezeigt, um Ihnen zu helfen, einen Weg zu finden, sie in Ihrem Studium oder bei Ihrer Arbeit einzusetzen. Ich hoffe, Ihnen einige gute Hinweise gegeben zu haben; jetzt sind Sie an der Reihe, das Gelernte in die Praxis umzusetzen.

Wenn Sie Fragen oder Anmerkungen haben, lassen Sie es mich im Kommentarbereich wissen!