with — komplexe Abfragen strukturieren


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

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 ist in dieser hinsicht anders: sie optimiert 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

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

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).

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.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

0

Funktionen die Tabellen als Ergebnis liefern (table-valued functions) sind schon recht nah am Bürger erster Klasse, aber immer noch Fremde aus Sicht des Optimizers. Wie würde man äußere Prädikate in eine Funktion hineinbringen (predicate pushdown)? Obwohl das bei Funktionen, die in SQL geschrieben sind möglich wäre, ist das wohl eher die Ausnahme als die Regel.

1

Genauer: query expressions.

2

Es ist auch vor values und table erlaubt.

3

Die Spaltennamen sind verpflichtend, wenn die Abfrage mehrere Spalten mit gleichem Namen liefert oder das rekursive-Schlüsselwort verwendet wird (SQL:2016-2, §7.17, Syntax Rule 3ji).

4

SQL:2016-2, §7.17, Syntax Rule 3ii

5

SQL:2016-2, §7.6 trifft zu, sodass der “otherwise” Absatz 8aiii cnicht angewendet werden kann.

6

With und with recursive unterscheiden sich bei der Sichtbarkeit innerhalb der with-Klausel selbst: Query-Namen, die in einer with-Klausel definiert werden, sind erst nach ihrer Definition sichtbar. Query-Namen, die in einer with recursive-Klausel definiert werden, sind überall in dieser Klausel sichtbar – also auch vor und in ihrer eigenen Definition.

7

SQL:2016-2, §5.4 Format: <query name> = <identifier> (ein Query-Name kann nicht qualifiziert werden). Auch Syntax Rule 5b.

8

SQL:2011-1, §6.3.3.1 Definition von generally contain, welche in SQL:2011-2, §7.6 nicht genutzt wird.

Ich persönlich hätte sehr gerne eine Möglichkeit mit with-Klauseln Tabellen in Views zu überschreiben (wie es SQLite macht). Das öffnet die Türe zu genersichen Views: ein und dieselbe View-Definition könnte mit verschiedenen Basis-Tabellen genutzt werden. Ohne diese Funktionalität muss man dafür auf Tabellen-Funktionen ausweichen.

„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz