In der Softwareentwicklung ist es üblich, Instruktionen in kleine, leicht verständliche Einheiten zusammenzufassen – z. B. in Funktionen oder Methoden. Dadurch wird der entsprechende Programmteil wiederverwendbar und besser lesbar.
Obwohl SQL auch Funktionen und Prozeduren hat, sind sie nicht das richtige Werkzeug um leicht erfassbare, wiederverwendbar Einheiten zu erstellen. In SQL sind weder Funktionen noch Prozeduren Bürger erster Klasse, wie es Unterabfragen sind.0 Der Grundbaustein von SQL sind Abfragen1 – nicht Instruktionen.
Um Abfragen wiederverwendbar zu machen gibt es seit SQL-92 Views (Ansichten). Sobald ein View mit create angelegt wurde, hat er einen Namen im Datenbankschema und kann in Abfragen wie eine Tabelle genutzt werden.
In SQL:1999 wurden mit der With-Klausel „auf eine Anweisung begrenzte Views“ eingeführt: sie werden nicht gespeichert, sondern gelten nur in der Abfrage, in der sie definiert sind. Dadurch kann man die Struktur einer Anweisung verbessern, ohne den globalen Namensraum zu belasten („namespace pollution“).
Syntax
Vereinfacht gesagt ist die With-Klausel ist ein optionales Präfix für Select2:
WITH query_name (column_name1, ...) AS
(SELECT ...)
SELECT ...Nach dem Schlüsselwort With entspricht die Syntax der von create view: es beginnt mit dem Query-Namen gefolgt von einer optionalen Liste3, die den Ergebnisspalten namen zuweist. Danach folgt das Schlüsselwort as, das die eigentliche Definition einleitet (also die Abfrage).
Im Gegensatz zu create view ist With keine eigenständige Anweisung: select muss unmittelbar folgen. Diese Abfrage (und deren Unterabfragen) können den eben definierten Query-Namen in der from-Klausel verwenden.
Eine einzelne With-Klausel kann durch Komma getrennt mehrere Query-Namen definieren – das Schlüsselwort With wird dabei nicht wiederholt. Jeder dieser With-Abfragen kann bereits auf Query-Namen zugreiffen, die zuvor in derselben With-Klausel definiert wurden (siehe Kompatibilität):4
WITH query_name1 AS (
SELECT ...
)
, query_name2 AS (
SELECT ...
FROM query_name1
...
)
SELECT ...Query-Namen die mit With definiert werden, verdecken gleichnamige Tabellen und Views.5
Performanceaspekte
Die meisten Datenbanken behandeln With-Abfragen genauso wie Views: sie ersetzen den Verweis auf eine With-Abfrage durch die Definition und optimieren danach die Abfrage als Ganzes.
Die PostgreSQL-Datenbank war vor Version 12 in dieser Hinsicht anders: sie optimierte jede With-Abfrage und die Hauptanweisung unabhängig voneinander.
Wenn eine With-Abfrage in einer Abfrage mehrfach referenziert wird, speichern manche Datenbanken das Ergebnis zwischen, um die Mehrfachausführung der With-Abfrage zu verhindern.
Mehr dazu in „WITH-Klausel: Performance Auswirkungen“.
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
Anwendungsfälle
Kompatibilität
Die With-Klausel wurde mit SQL:1999 als Sammlung optionaler Funktionen eingeführt. Wenn man die recursive-Variante außer Acht lässt, sind das T121 für With-Klauseln auf oberster Ebene und T122 für With-Klauseln in Unterabfragen.
Die Grundfunktion von With wird weitgehend unterstützt. Der einzige Bereich, bei dem sich die Produkte unterscheiden, ist die Namensauflösung. Besonders auffällig ist, dass With oft wie With recursive behandelt wird.6 Etwas subtilere Abweichungen vom Standard betreffen qualifizierte Tabellennamen (schema.tabelle kann keine With-Abfrage bezeichnen7) und Views, die im Sichtbarkeitsbereich einer With-Abfrage genutzt werden (die Abfrage im View kann das äußere With nicht „sehen“8).
- Nur ohne Spaltennamen:
WITH name AS (SELECT…) - Nur ohne Spaltennamen:
WITH name AS (SELECT…)• CTEs in Unterabfragen können globale CTEs nicht sehen - CTEs in Unterabfragen können globale CTEs nicht sehen
- Unterstützt eine proprietäre Variante:
with … insert … select
Konforme Alternativen
Einige Anwendungsfälle können auch mit Views umgesetzt werden. Das kann jedoch schnell zu einer unverhältnismäßigen Zahl von Views führen („namespace pollution“). Daher ist in diesen Fällen die Verwendung von Unterabfragen oft die bessere Option.
Proprietäre Erweiterungen
With als DML-Präfix (PostgreSQL, SQL Server, SQLite)
Einige Datenbanken akzeptieren With als Präfix für DML-Anweisungen (Doku: PostgreSQL, SQL Server, SQLite).
Bei SQL Server kann eine With-Abfrage auch das Ziel der DML-Anweisung sein (ähnlich eines updatable Views).
Funktionen in With (Oracle)
Ab Version 12cR1 unterstützt die Oracle-Datenbank Funktions- und Prozedur-Deklaration in der With-Klausel (Dokumentation).
DML in With (PostgreSQL)
Die PostgreSQL-Datenbank unterstützt ab Version 9.1 DML-Anweisungen (insert, update, delete) in der With-Klausel (Dokumentation). Wenn man dann noch die (ebenfalls proprietäre) returning-Klausel der DML-Anweisung nutzt, liefert die With-Klausel auch ein Ergebnis – z. B. die eingefügten Zeilen.
