In der Softwareentwicklung ist es gängige Praxis, Anweisungen als kleine und leicht verständliche Einheiten zu gruppieren-nämlich Funktionen oder Methoden. Dies macht den Code wiederverwendbar und verbessert die Lesbarkeit.
Obwohl SQL auch über Funktionen und Prozeduren verfügt, sind sie nicht die richtigen Werkzeuge, um leicht verständliche und wiederverwendbare Einheiten zu erstellen. In SQL sind weder Funktionen noch Prozeduren erstklassige Bürger auf die gleiche Weise wie Unterabfragen.0 Der Baustein von SQL sind abfrage1-keine Anweisungen.,
Um Abfragen wiederverwendbar zu machen, führte SQL-92 Views ein. Nach der Erstellung hat eine Ansicht einen Namen im Datenbankschema, sodass andere Abfragen sie wie eine Tabelle verwenden können.
SQL: 1999 wurde die with Klausel hinzugefügt, um „statement scoped views“zu definieren. Sie werden nicht im Datenbankschema gespeichert, sondern sind nur in der Abfrage gültig, zu der sie gehören. Dies ermöglicht es, die Struktur einer Anweisung zu verbessern, ohne den globalen Namespace zu verschmutzen.,
Syntax
Die with Klausel ist einfach ausgedrückt ein optionales Präfix für select:2
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...Die Syntax nach dem Schlüsselwort with ist die gleiche wie für create view: Es beginnt mit dem Abfragenamen und optional3 und in Klammern der Name der zurückgegebenen Spalten. Das Schlüsselwort as führt schließlich die Definition selbst (die Abfrage) ein—wieder in Klammern.,
With ist kein eigenständiger Befehl wie create view ist: Es muss gefolgt werden von select. Diese Abfrage (und die darin enthaltenen Unterabfragen) können auf den gerade definierten Abfragenamen in der Klausel from verweisen.
Eine einzelnewith – Klausel kann mehrere Abfragenamen einführen, indem sie durch ein Komma getrennt werden (das Schlüsselwortwith wird nicht wiederholt)., Jede dieser Abfragen kann sich auf die Abfragenamen beziehen, die zuvor in derselben with clause4 definiert wurden (eine häufig vernachlässigte Regel—siehe Kompatibilität):
WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...Abfragenamen, die mit with definiert wurden, maskieren vorhandene Tabellen oder Ansichten mit demselben Namen.5
Leistungserwägungen
Die meisten Datenbanken verarbeiten with-Abfragen auf die gleiche Weise, wie sie Ansichten verarbeiten: Sie ersetzen den Verweis auf die Abfrage durch ihre Definition und optimieren die Gesamtabfrage.,
Die PostgreSQL-Datenbank war bis Version 12 anders: Sie optimierte jedewith Abfrage und die Hauptanweisung unabhängig voneinander.
Wenn auf eine with – Abfrage mehrmals Bezug genommen wird, speichern einige Datenbanken ihr Ergebnis (dh „materialisieren“), um eine doppelte Ausführung zu verhindern.
Lesen Sie mehr dazu in „with Klausel: Performance Impacts“.
In meinem eigenen Namen
verdiene ich meinen Lebensunterhalt mit Schulungen, anderen SQL-bezogenen Dienstleistungen und dem Verkauf meines Buches. Erfahren Sie mehr unter https://winand.at/.,
Use Cases
-
Literate SQL
-
Unit tests on transient data
-
Zuweisen von Namen zu Spalten ohne bekannten Namen
Kompatibilität
Die with Klausel wurde mit SQL eingeführt:1999 als set von optionalen Funktionen. Abgesehen von der Variante recursive sind die Funktionen T121 für Klauseln der obersten Ebene with und T122 fürwith Klauseln in Unterabfragen.
Die Grundfunktionalität von with wird gut unterstützt., Der einzige Bereich, in dem sich Produkte anders verhalten, ist die Namensauflösung. Es ist besonders bemerkenswert, dass with oft wie with recursivebehandelt wird.6 Die subtileren Inkompatibilitäten beziehen sich auf qualifizierte Tabellennamen (schema.table kann nicht auf eine with – Abfrage verweisen)7 und Ansichten, die im Bereich von with – Abfragen verwendet werden (die Abfrage in der Ansicht „sieht“ die äußere with – Klausel nicht).,8
Konforme Alternativen
Ansichten können einige der Anwendungsfälle abdecken. Dies kann jedoch leicht zu einer unangemessenen Anzahl von Ansichten führen („Namespace-Verschmutzung“). In diesen Fällen sind Unterabfragen oft die bessere Option.
Proprietäre Erweiterungen
mit als DML präfix (PostgreSQL, SQL Server, SQLite)
Einige datenbanken akzeptieren with als präfix zu DML anweisungen (docs: PostgreSQL, SQL Server, SQLite).,
SQL Server kann auch eine with – Abfrage als Ziel für DML-Anweisungen verwenden (im Grunde eine aktualisierbare Ansicht erstellen).
Funktionen in with (Oracle)
Die Oracle-Datenbank unterstützt Funktions-und Prozedurdeklarationen innerhalb der with – Klausel seit Version 12cR1 (Dokumentation).
DML in with (PostgreSQL)
Ab 9.1 unterstützt die PostgreSQL-Datenbank DML-Anweisungen (insert, update, delete) innerhalb des Körpers von with Abfragen (Dokumentation)., Bei Verwendung der (auch proprietären) returning – Klausel der DML-Anweisung gibt die Abfrage with tatsächlich Daten zurück (z. B. die gerade eingefügten Zeilen).