Was ist die WITH-Klausel in SQL?

In diesem Artikel erfahren Sie mehr über die SQL WITH-Klausel, die auch als Common Table Expression(CTE) bezeichnet wird. Anhand von Beispielen werden wir einige Anwendungsfälle und ihre Vorteile erläutern.

Einführung in die SQL WITH-Klausel

Die WITH-Klausel in SQL wurde im Standard-SQL eingeführt, um komplexe lange Abfragen zu vereinfachen, insbesondere solche mit JOINs und Unterabfragen. Eine WITH-Klausel, die oft auch als CTE- oder Subquery-Refactoring bezeichnet wird, definiert einen temporären Datensatz, dessen Ausgabe in nachfolgenden Abfragen referenziert werden kann.

Der beste Weg, die WITH-Klausel in SQL zu lernen, ist die Praxis. Ich empfehle LearnSQL.de's interaktiven Rekursive Abfragen Kurs. Er enthält über 100 Übungen, in denen die WITH-Klausel behandelt wird, angefangen bei den Grundlagen bis hin zu fortgeschrittenen Themen wie rekursiven WITH-Abfragen.

Die WITH-Klausel wird als "temporär" bezeichnet, da das Ergebnis nicht dauerhaft im Datenbankschema gespeichert wird. Sie fungiert als temporäre Ansicht, die nur für die Dauer der Abfrage existiert, d.h. sie ist nur während des Ausführungsbereichs der Anweisungen SELECT, INSERT, UPDATE, DELETE oder MERGE verfügbar. Sie ist nur in der Abfrage gültig, zu der sie gehört, wodurch es möglich ist, die Struktur einer Anweisung zu verbessern, ohne den globalen Namensraum unübersichtlich zu machen.

Die WITH-Klausel wird in Abfragen verwendet, in denen eine abgeleitete Tabelle ungeeignet ist. Daher wird sie als sauberere Alternative zu temporären Tabellen angesehen. Der Hauptvorteil der WITH-Klausel ist, dass sie hilft, lange und komplexe hierarchische Abfragen zu organisieren und zu vereinfachen, indem sie sie in kleinere, besser lesbare Teile zerlegt.

Die WITH-Klausel wurde erstmals 1999 in den SQL-Standard aufgenommen und ist heute in allen wichtigen RDBMS verfügbar. Einige häufige Anwendungen von SQL CTE sind:

• Mehrfacher Verweis auf eine temporäre Tabelle in einer einzigen Abfrage.
• Durchführung von Aggregationen auf mehreren Ebenen, wie z.B. die Ermittlung des Durchschnitts von Maximalwerten.
• Mehrmalige Durchführung einer identischen Berechnung im Rahmen einer größeren Abfrage.
• Verwendung als Alternative zur Erstellung eines Views in der Datenbank.

Im Folgenden sehen wir uns ein schnelles und einfaches Beispiel für die WITH-Klausel an, bei dem die Tabelle OrderDetails aus der bekannten Northwind-Datenbank verwendet wird. Das Ziel ist die Rückgabe der durchschnittlichen Bestellmenge pro Produkt_ID:

ABFRAGE:

WITH cte_quantity
AS
(SELECT
	SUM(Quantity) as Total
FROM OrderDetails
GROUP BY ProductID)

SELECT
	AVG(Total) average_product_quantity
FROM cte_quantity;

ERGEBNIS:

Anzahl der Datensätze: 1

Wenn Sie die Abfrage ohne die WITH-Klausel ausführen und stattdessen eine Unterabfrage verwenden würden, würde die Abfrage etwa so aussehen:

ABFRAGE:

SELECT
	AVG(Total) average_product_quantity
FROM
(SELECT
SUM(Quantity) as Total
FROM OrderDetails
GROUP BY ProductID)

Obwohl Sie vielleicht nicht viele greifbare Unterschiede zwischen den beiden Abfragen sehen, ist die aufgeschlüsselte Struktur, die eine WITH-Klausel ermöglicht, von unschätzbarem Wert, wenn Ihre Abfragen an Größe und Hierarchie zunehmen. Im Folgenden sehen Sie ein Beispiel dafür in Form einer verschachtelten WITH-Klausel. Weitere Beispiele finden Sie in einem unserer früheren Artikel zu diesem Thema - CTEs mit Beispielen erklärt.

Die Syntax der WITH-Klausel

Die allgemeine Reihenfolge der Schritte zur Ausführung einer WITH Klausel ist wie folgt:

1. Initiieren Sie WITH-Klausel
2. Geben Sie den Ausdrucksnamen für die zu definierende Abfrage an.
3. Optional: Geben Sie durch Kommata getrennte Spaltennamen an.
4. Nachdem Sie den Namen des Ausdrucks zugewiesen haben, geben Sie den Befehl AS ein. Die Ausdrücke sind in diesem Fall die benannten Ergebnismengen, die Sie später in der Hauptabfrage verwenden werden, um auf die CTE zu verweisen.
5. Schreiben Sie die Abfrage, die erforderlich ist, um den gewünschten temporären Datensatz zu erzeugen.
6. Wenn Sie mit mehreren CTEs oder WITH-Klauseln arbeiten, leiten Sie jede weitere durch ein Komma getrennt ein und wiederholen die Schritte 2-4. Eine solche Anordnung wird auch als verschachtelte WITH-Klausel bezeichnet.
7. Verweisen Sie auf die oben definierten Ausdrücke in einer nachfolgenden Abfrage mitSELECT, INSERT, UPDATE, DELETE oder MERGE

Die Syntax für die Implementierung einer WITH-Klausel ist in folgendem Pseudocode dargestellt:

--CTE
WITH Name_des_Ausdrucks_1 (spalte_1, spalte_2,…,spalte_n)
AS
(CTE Abfragedefinition 1),
Name_des_Ausdrucks_2 (spalte_1, spalte_2,…,spalte_n)
AS
(CTE Abfragedefinition 2)

--Endgültige Abfrage mit CTE
SELECT Ausdruck_A, Ausdruck_B, ...
FROM Name_des_Ausdrucks_2

Die WITH Klausel ist ein einfacher Ersatz für normale Unterabfragen. Der einzige Unterschied besteht darin, dass Sie dieselbe abgeleitete Ergebnismenge mehrmals in Ihrem Code wiederverwenden können, wenn Sie die WITH-Klausel verwenden, um eine CTE zu erzeugen. Dies ist bei Unterabfragen nicht möglich.

Wie wir oben sehen, sind die wichtigsten Ausführungsparameter für eine WITH Klausel:

• WITH: Wird zur Erstellung einer CTE oder der temporären Datenmenge(n) verwendet.
• Name_des_Ausdrucks (spalte_1, …, spalte_n): Der Name des virtuellen temporären Datensatzes, der in der Hauptabfrage verwendet wird, und spalte_1 bis spalte_n sind die Spaltennamen, die in nachfolgenden Abfrageschritten verwendet werden können.
• AS (....): Dieser Abschnitt definiert die Abfrage, die die CTE Name_des_Ausdrucks befüllt. Wenn eine verschachtelte CTE implementiert wird, bezieht sich die Abfrage innerhalb der zweiten AS wahrscheinlich auf die erste CTE.
• SELECT Ausdruck_A, Ausdruck_B FROM Name_des_Ausdrucks: Dieser Abschnitt spezifiziert die äußere Hauptabfrage, in der die SELECT-Anweisung (oder INSERT-, UPDATE- , DELETE- oder MERGE-Anweisungen) auf eine oder mehrere der erzeugten CTEs angewendet wird, um anschließend das gewünschte Ergebnis auszugeben.

Alle oben genannten Parameter müssen angegeben werden. Sie können die Klauseln WHERE, GROUP BY, ORDER BY und/oder HAVING je nach Bedarf verwenden.

Wenn eine Abfrage mit einer WITH-Klausel ausgeführt wird, wird zunächst die in der Klausel erwähnte Abfrage ausgewertet und die Ausgabe dieser Auswertung in einer temporären Relation gespeichert. Dann wird die Hauptabfrage, die mit der WITH-Klausel verbunden ist, unter Verwendung der erzeugten temporären Relation ausgeführt.

In diesem Beispiel wird eine verschachtelte WITH-Klausel mit der gleichen OrderDetails Tabelle wie oben verwendet. Eine verschachtelte WITH-Klausel oder verschachtelte CTEs beinhalten zwei CTEs innerhalb derselben Abfrage, wobei die zweite auf die erste verweist.

ZIEL: Ausgabe der durchschnittlichen Anzahl von Bestellungen oder Verkäufen nach EmployeeID für ShipperID 2 und ShipperID 3.

ABFRAGE:

--Erste CTE
WITH cte_sales
AS
(SELECT
	EmployeeID,
	COUNT(OrderID) as Orders,
	ShipperID
FROM Orders
GROUP BY EmployeeID, ShipperID),

--Zweite CTE (verschachtelt)
shipper_cte
AS
(SELECT *
FROM cte_sales
WHERE ShipperID=2 or ShipperID=3)

--Abfrage mit CTE
SELECT
	ShipperID, AVG(Orders) average_order_per_employee
FROM
shipper_cte
GROUP BY ShipperID;

ERGEBNIS:

Anzahl der Datensätze: 2

Hier berechnen wir die durchschnittliche Anzahl der Bestellungen pro Mitarbeiter, aber nur für ShipperID 2 und ShipperID 3. In der ersten CTE, cte_sales, werden die Bestellungen gezählt und nach EmployeeID und ShipperID gruppiert. In der zweiten CTE, shipper_cte, verweisen wir auf die erste CTE und definieren die ShipperID-Bedingungen mit einer WHERE-Klausel. In der Hauptabfrage beziehen wir uns dann nur auf die zweite CTE shipper_cte, um die durchschnittlichen Bestellungen pro Mitarbeiter nach ShipperID zu berechnen.

Weitere Nuancen der Syntax im Zusammenhang mit SQL WITH-Klauseln und CTEs werden in Modul #2 des Kurses Rekursive Abfragen erläutert, das auch eine Sammlung von Beispielen für fortgeschrittene Benutzer enthält.

Anwendungsfälle der SQL-Klausel WITH

Wann braucht man also eine WITH-Klausel wirklich? Nun, es gibt ein paar einzigartige Anwendungsfälle. Die meisten von ihnen sind auf Bequemlichkeit und Einfachheit bei der Entwicklung und Wartung von Abfragen ausgerichtet.

Die herausragenden Anwendungen und die damit verbundenen Vorteile von SQL CTEs lassen sich wie folgt zusammenfassen:

• Verbessert die Lesbarkeit des Codes - Die literarische Programmierung ist ein von Donald Kuth eingeführter Ansatz, der darauf abzielt, den Quellcode in der Reihenfolge der menschlichen Logik anzuordnen, so dass er mit minimalem Aufwand verstanden werden kann, indem man ihn wie einen Roman in einer sequentiellen Weise liest. Die SQL-Klausel WITH hilft genau dabei, indem sie virtuelle benannte Tabellen erstellt und große Berechnungen in kleinere Teile aufteilt. Diese können dann später in der Abfrage in der abschließenden SELECT oder einer anderen Anweisung kombiniert werden, anstatt alles in einen großen Brocken zu packen.
• Verbessert die Wartbarkeit des Codes - Hand in Hand mit der Lesbarkeit geht die Wartbarkeit. Da Ihre Abfragen und Datenbanken mit der Zeit immer größer werden, besteht immer die Notwendigkeit zur Fehlersuche und -behebung. Ein leichter zu lesender Code ist einfacher zu warten!
• Alternative zu einer Ansicht - CTEs können Ansichten ersetzen und SELECT, INSERT, UPDATE, DELETE oder MERGE verwenden. Dies kann besonders nützlich sein, wenn Sie nicht über die Systemrechte zum Erstellen eines Ansichtsobjekts verfügen oder wenn Sie keine Ansicht erstellen möchten, die nur in einer einzigen Abfrage verwendet werden soll.
• Überwindung von Anweisungsbeschränkungen - CTEs helfen bei der Überwindung von Beschränkungen wie SELECT-Anweisungsbeschränkungen, z.B. bei der Durchführung einer GROUP BY-Klausel unter Verwendung nicht-deterministische Funktionen.
• Verarbeitung hierarchischer Strukturen - Dies ist eine der fortgeschritteneren Anwendungen der CTE und wird durch so genannte rekursive CTEs erreicht. Rekursive Abfragen können auf sich selbst zurückgreifen und ermöglichen es Ihnen, komplexe hierarchische Modelle zu durchlaufen. Mehr dazu weiter unten.

Es gibt noch einige weitere Anwendungsfälle für CTEs, die in einem unserer früheren Artikel "When to use CTE" besprochen wurden. Der Rekursive Abfragen Kurs auf LearnSQL.de wird Ihnen helfen, das gesamte Thema des CTE mit detaillierten Beispielen aufzuschlüsseln, damit Sie das Thema mit praktischen Übungen meistern können.

Die rekursive WITH-Klausel

Die obigen Beispiele verwenden nicht-rekursive WITH-Klauseln. Rekursive CTEs sind eine der fortschrittlicheren Funktionalitäten der WITH-Klausel, die es erlaubt, innerhalb dieser CTE auf sich selbst zu verweisen. Dies erleichtert den Benutzern das Durchlaufen komplexer hierarchischer Datenstrukturen, wie z. B. Gerätestücklisten und Organigramme.

Wenn Sie mit der Rekursion in der Programmierung vertraut sind, beinhaltet die rekursive Funktion der WITH-Klausel in ähnlicher Weise einen Basisfall und den Rekursionsschritt.

Abschließende Überlegungen zur SQL WITH-Klausel

In diesem Artikel sind wir die grundlegende Syntax und einige Beispiele durchgegangen, wie und wann Sie WITH Klauseln oder CTEs definieren und verwenden können. Um ihre Implementierung in Ihrem eigenen Code besser zu verstehen, ist Übung der Schlüssel! Dazu empfehle ich den Rekursive Abfragen Kurs hier auf LearnSQL.de. In diesem Kurs finden Sie weitere praktische Beispiele und einzigartige Anwendungen der WITH-Klausel, mit interaktiven Übungen zur Verwendung von CTEs (rekursiv und nicht rekursiv) in Ihrem täglichen Arbeitsumfeld.

Wenn Sie die WITH-Klausel erst einmal richtig verstanden haben, werden Sie überrascht sein, wie viel besser Ihre SQL-Skripte aussehen können!