with — komplexe Abfragen strukturieren


In der imperativen Programmierung ist es üblich, Instruktionen in kleine, leicht verständliche Gruppen zusammenzufassen – in Funktionen oder Methoden. Dadurch kann man die Wiederverwendung des Codes ermöglichen, aber auch um die Lesbarkeit verbessern.

Obwohl SQL auch Funktionen und Prozeduren bietet, sind diese nicht das vorrangige Werkzeug um komplexe SQL-Abfragen verständlich zu gestalten oder die Wiederverwendung zu ermöglichen. In SQL sind weder Funktionen noch Prozeduren Bürger erster Klasse, wie es Unterabfragen sind0.

In SQL sind Abfragen – nicht Instruktionen – die Grundbausteine von Programmen. Daher hat SQL Views (Ansichten), um Abfragen als solches abzuspeichern, damit sie wiederverwendet werden können.

Darüber hinaus gibt es seit SQL:1999 die with-Klausel, mit der man „Views“ innerhalb einer Abfrage definieren kann. Da diese with-Abfragen nur innerhalb einer Anweisung sichtbar sind, kann man damit die Struktur dieser Anweisung verbessern, ohne den globalen Namensraum zu beschmutzen („namespace pollution“).

Die Funktionalität der with-Klausel ist auch unter dem Namen Common Table Expression (CTE) und Sub-Query Factoring bekannt. Die rekursive Form der with-Klausel wird in einem anderen Artikel behandelt.

Syntax

With ist ein Präfix für select1:

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 der optional2 und in Klammern von den Namen der Ergebnis-Spalten gefolgt wird. Das Schlüsselwort as leitet die Definition ein (also die Abfrage) – wiederum in Klammern.

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 nicht wiederholt. Jeder dieser with-Abfragen kann bereits Query-Namen verwenden, die zuvor in derselben with-Klausel definiert wurden3 (siehe Kompatibilität):

WITH query_name1 AS (
     SELECT ...
     )
   , query_name2 AS (
     SELECT ...
       FROM query_name1
        ...
     )
SELECT ...

Query-Namen die mit with definiert werden, verdecken gleichnamige Tabellen und Views4.

Performanceaspekte

Normalerweise behandeln Datenbanken with-Abfragen genauso wie Views: sie ersetzen den Verweis auf ein with-Query durch seine Definition und optimieren danach die Abfrage als Ganzes.

Mit Stand 9.55 geht die PostgreSQL-Datenbank hier anders vor: sie behandelt die with-Klausel als Optimierungs-Grenze. Die Abfragen in der with-Klausel werden getrennt geplant (optimiert). Dadurch kann es im Vergleich zu Unterabfragen, die direkt in die from-Klausel geschrieben werden, zu erheblichen Performanceunterschieden kommen.

Da der SQL-Standard nicht vorschreibt, wie Abfragen zu optimieren sind, sind beide Ansätze Standard-Konform.

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 gut unterstützt. Der einzige Bereich, bei dem sich verschiedenen Produkten unterschiedlich verhalten ist die Namensauflösung. Besonders auffällig ist, dass with oft wie with recursive behandelt wird6. 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

Oracle: Funktionen in with

Ab Version 12cR1 unterstützt die Oracle-Datenbank Funktions- und Prozedur-Deklaration in der with-Klausel (Dokumentation).

PostgreSQL: DML in with

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.

SQL Server: with als DML-Präfix

SQL Server akzeptiert ab Version 2005 with als Präfix für DML-Anweisungen (Dokumentation). Der so definierte Query-Name kann auch das Ziel der DML-Anweisung sein (ähnlich eines updatable Views).

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

Fußnoten

  1. 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 (push down)? Obwohl das bei Funktionen, die in SQL geschrieben sind möglich wäre, ist das wohl eher die Ausnahme als die Regel.

  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 wurde (SQL:2011-2, §7.13, Syntax Rule 3gi).

  4. SQL:2011-2, §7.13, Syntax Rule 3e.

  5. SQL:2011-2, §7.6 Syntax Rule 12a trifft zu, sodass der “otherwise” Absatz 12c nicht angewendet werden kann.

  6. Siehe “Allow the CTE (Common Table Expression) optimization fence to be optionally disabled” in der PostgreSQL Todo Liste.

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

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

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