Nell’ingegneria del software, è pratica comune raggruppare le istruzioni come unità piccole e facilmente comprensibili, ovvero funzioni o metodi. Ciò rende il codice riutilizzabile e migliora la leggibilità.
Anche se SQL ha anche funzioni e procedure, non sono gli strumenti giusti per costruire unità facilmente comprensibili e riutilizzabili. In SQL, né le funzioni né le procedure sono cittadini di prima classe allo stesso modo delle subquery.0 Il blocco di costruzione di SQL sono queries1-non istruzioni.,
Per rendere le query riutilizzabili, SQL-92 ha introdotto le viste. Una volta creata, una vista ha un nome nello schema del database in modo che altre query possano utilizzarla come una tabella.
SQL:1999 ha aggiunto la clausolawith per definire “viste con ambito di istruzione”. Non sono memorizzati nello schema del database: sono invece validi solo nella query a cui appartengono. Ciò rende possibile migliorare la struttura di un’istruzione senza inquinare lo spazio dei nomi globale.,
la Sintassi
with clausola è, semplicemente, un prefisso opzionale per select:2
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...La sintassi dopo la parola chiave with è la stessa come per create view: si inizia con il nome della query, e optionally3 e tra parentesi il nome delle colonne restituisce. La parola chiave as introduce infine la definizione stessa (la query)—di nuovo tra parentesi.,
With non è un comando autonomo come create viewè: deve essere seguito da select. Questa query (e le sottoquery che contiene) possono fare riferimento al nome della query appena definito nella clausolafrom.
Una singola clausola withpuò introdurre più nomi di query separandoli con una virgola (la parola chiave with non viene ripetuta)., Ognuna di queste query può fare riferimento ai nomi di query precedentemente definiti all’interno dello stesso with clause4 (una regola spesso trascurata—vedi Compatibilità):
WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...I nomi di query definiti utilizzando with mascherano tabelle o viste esistenti con lo stesso nome.5
Considerazioni sulle prestazioni
La maggior parte dei database elaborawith-query nello stesso modo in cui elaborano le viste: sostituiscono il riferimento alla query con la sua definizione e ottimizzano la query complessiva.,
Il database PostgreSQL era diverso fino alla versione 12: ottimizzava ogni querywith e l’istruzione principale indipendente l’una dall’altra.
Se una querywith viene riferita più volte, alcuni database memorizzano nella cache (cioè “materializzano”) il suo risultato per impedire la doppia esecuzione.
Per saperne di più su questo in “with Clausola: Impatti sulle prestazioni”.
Per conto mio
Mi guadagno da vivere con la formazione, altri servizi correlati a SQL e la vendita del mio libro. Ulteriori informazioni su https://winand.at/.,
i Casi d’Uso
-
Letterato SQL
-
Unit test su dati transitori
-
Assegnare i nomi alle colonne senza un nome conosciuto
Compatibilità
Il with clausola è stata introdotta con SQL:1999 come un insieme di caratteristiche opzionali. Lasciando da parte la variante recursive, le funzionalità sono T121 per le clausole with di primo livello e T122 per le clausole with nelle sottoquery.
La funzionalità di base di with è ben supportata., La singola area in cui i prodotti si comportano in modo diverso è la risoluzione dei nomi. È particolarmente degno di nota che with viene spesso trattato come with recursive.6 Le incompatibilità più sottili sono legate a nomi di tabelle qualificati (schema.table non può fare riferimento a una query with) 7 e le viste utilizzate nell’ambito delle query with (la query all’interno della vista non “vede” l’esterno with clausola).,8
Alternative conformi
Le viste possono coprire alcuni dei casi d’uso. Tuttavia, questo può facilmente portare a un numero irragionevole di visualizzazioni (“inquinamento dello spazio dei nomi”). In questi casi, le subquery sono spesso l’opzione migliore.
Estensioni proprietarie
con come prefisso DML (PostgreSQL, SQL Server, SQLite)
Alcuni database accettanowith come prefisso alle istruzioni DML (documenti: PostgreSQL, SQL Server, SQLite).,
SQL Server può anche utilizzare una querywith come destinazione per le istruzioni DML (fondamentalmente creando una vista aggiornabile).
Funzioni in with (Oracle)
Il database Oracle supporta le dichiarazioni di funzioni e procedure all’interno della clausola with dalla versione 12cR1 (documentazione).
DML con (PostgreSQL)
a Partire 9.1, il database PostgreSQL supporta le istruzioni DML (insert update delete) all’interno del corpo di with query (documentazione)., Quando si utilizza la clausola (anche proprietaria) returning dell’istruzione DML, la query with restituisce effettivamente i dati (ad esempio le righe appena inserite).