in software engineering is het gebruikelijk om instructies te groeperen als kleine en gemakkelijk te begrijpen eenheden – namelijk functies of methoden. Dit maakt de code herbruikbaar en verbetert de leesbaarheid.
hoewel SQL ook functies en procedures heeft, zijn ze niet de juiste tools voor het bouwen van gemakkelijk te begrijpen en herbruikbare eenheden. In SQL zijn functies noch procedures eersteklas burgers op dezelfde manier als subqueries.0 de bouwsteen van SQL zijn queries1 – geen instructies.,
om queries herbruikbaar te maken, introduceerde SQL-92 weergaven. Eenmaal gemaakt, een weergave heeft een naam in de database schema, zodat andere query ‘ s kunnen gebruiken als een tabel.
SQL: 1999 voegde de with clausule toe om “statement scoped views”te definiëren. Ze worden niet opgeslagen in het databaseschema: in plaats daarvan zijn ze alleen geldig in de query waartoe ze behoren. Dit maakt het mogelijk om de structuur van een statement te verbeteren zonder de wereldwijde naamruimte te vervuilen.,
Syntax
De with component is, simpel gezegd, een optionele voorvoegsel voor select:2
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...De syntax na het sleutelwoord with is hetzelfde als voor create view: het begint met de naam van de query en optionally3 en tussen haakjes de naam van de kolommen in het resultaat. Het trefwoord as introduceert uiteindelijk de definitie zelf (de query)—opnieuw tussen haakjes.,
With is geen zelfstandig commando zoals create view is: het moet worden gevolgd door select. Deze query (en subqueries die het bevat) kan verwijzen naar de zojuist gedefinieerde query naam in hun from clausule.
een enkelewith clausule kan meerdere query namen introduceren door ze te scheiden met een komma (het with sleutelwoord wordt niet herhaald)., Elk van deze queries kan verwijzen naar de querynamen die eerder zijn gedefinieerd binnen dezelfde with clause4 (een vaak verwaarloosde regel—zie Compatibiliteit):
WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...Querynamen die zijn gedefinieerd met with maskeren bestaande tabellen of weergaven met dezelfde naam.5
Prestatieoverwegingen
De meeste databases verwerken with-queries op dezelfde manier als ze weergaven verwerken: ze vervangen de verwijzing naar de query door de definitie en optimaliseren de algehele query.,
De PostgreSQL database was anders tot versie 12: het optimaliseerde elkewith query en de belangrijkste verklaring onafhankelijk van elkaar.
als een with query meerdere keren wordt verwezen, worden sommige databases cache (dat wil zeggen “materialiseren”) het resultaat om dubbele uitvoering te voorkomen.
Lees hier meer over in “with Clausule: Performance Impacts”.
in eigen naam
Ik verdien mijn brood van opleiding, andere SQL-gerelateerde diensten en de verkoop van mijn boek. Meer informatie vindt u op https://winand.at/.,
Use Cases
-
Literate SQL
-
Unit tests on transient data
-
wijs namen toe aan kolommen zonder een bekende naam
Compatibiliteit
de with clausule werd geïntroduceerd met SQL:1999 als een set van optionele functies. Afgezien van derecursive variant zijn de functies T121 voor topniveauwith clausules en T122 voorwith clausules in subqueries.
de basisfunctionaliteit van with wordt goed ondersteund., Het enige gebied waar producten zich anders gedragen is naamresolutie. Het is vooral opmerkelijk dat with vaak wordt behandeld als with recursive.6 de subtielere onverenigbaarheden zijn gerelateerd aan gekwalificeerde tabelnamen (schema.table kan niet verwijzen naar een with query)7 en weergaven gebruikt in de scope van with queries (de query in de weergave “zie” de buitenste with clausule).,8
conforme alternatieven
weergaven kunnen enkele van de use cases bestrijken. Dit kan echter gemakkelijk leiden tot een onredelijk aantal weergaven (“namespace pollution”). In die gevallen zijn subqueries vaak de betere optie.
Proprietary Extensions
met als DML-prefix (PostgreSQL, SQL Server, SQLite)
sommige databases accepteren with als een prefix voor DML-statements (docs: PostgreSQL, SQL Server, SQLite).,
SQL Server kan ook een with query gebruiken als een doel voor DML statements (in principe het bouwen van een updatable view).
functies in met (Oracle)
De Oracle-Database ondersteunt functie-en procedureverklaringen binnen de with clausule sinds versie 12cR1 (documentatie).
DML in met (PostgreSQL)
beginnend met 9.1, ondersteunt de PostgreSQL-database DML-statements (insert, update, delete) binnen het lichaam van with queries (documentatie)., Bij gebruik van de (ook propriëtaire) returning clausule van de DML statement, de with query eigenlijk geeft gegevens (bijvoorbeeld de rijen net ingevoegd).