Che cos'è la clausola WITH in SQL?

In questo articolo si parlerà della clausola SQL WITH, nota anche come espressione di tabella comune (CTE). Verranno illustrati alcuni esempi per dimostrarne determinati casi d'uso e scoprirne i vantaggi.

Introduzione alla clausola SQL WITH

La clausola WITH è stata introdotta in SQL standard per semplificare le query lunghe e complesse, in particolare quelle con JOINs e le subquery. Spesso chiamata in modo intercambiabile CTE o refactoring di subquery, una clausola WITH definisce un insieme di dati temporanei il cui output è disponibile per essere consultato nelle query successive.

Il modo migliore per imparare l'uso della clausola WITH in SQL è esercitarsi. Vi consiglio il corso interattivo di LearnSQL.it Recursive Queries. Contiene oltre 100 esercizi che insegnano la clausola WITH partendo dalle basi per arrivare ad argomenti più complessi come le query WITH ricorsive.

La clausola WITH è considerata "temporanea" perché il risultato non viene memorizzato in modo permanente nello schema del database. Funziona come un risultato visibile solo temporaneamente e che esiste solo per la durata della query, cioè è disponibile solo durante l'esecuzione delle istruzioni SELECT, INSERT, UPDATE, DELETE, o MERGE. È valida solo nella query a cui appartiene, consentendo di migliorarne la struttura senza interferire con il namespace globale.

La clausola WITH viene utilizzata nelle query in cui non si può ottenere una tabella derivata. Pertanto, è considerata un'alternativa ovviabile alle tabelle temporanee. In parole povere, il vantaggio principale della clausola WITH è che aiuta a organizzare e semplificare query gerarchiche lunghe e complesse, suddividendole in parti più piccole e leggibili.

La clausola WITH è stata introdotta in SQL standard nel 1999 ed è ora disponibile in tutti i principali RDBMS. Alcune applicazioni comuni delle CTE in SQL sono:

• Fare riferimento a una tabella temporanea più volte in una singola query.
• Eseguire aggregazioni a più livelli, come la ricerca della media dei massimi.
• Eseguire lo stesso calcolo più volte nel contesto di una query più ampia.
• Usare una alternativa alla creazione di una vista nel database.

Vediamo di seguito un esempio semplice e veloce della clausola WITH, usando la tabella OrderDetails del noto database Northwind. L'obiettivo è scoprire la quantità media ordinata di ogni prodotto:

QUERY:

WITH cte_quantity
AS
(SELECT
	SUM(Quantity) as Total
FROM OrderDetails
GROUP BY ProductID)

SELECT
	AVG(Total) average_product_quantity
FROM cte_quantity;

RISULTATO:

Numero di risultati: 1

Se volessimo eseguire questa query senza la clausola WITH, utilizzando invece una subquery, il risultato avrebbe un aspetto simile a questo:

QUERY:

SELECT
	AVG(Total) average_product_quantity
FROM
(SELECT
SUM(Quantity) as Total
FROM OrderDetails
GROUP BY ProductID)

Sebbene non possiate notare molte differenze tangibili tra le due, la struttura suddivisa che comporta l'uso della clausola WITH sarà preziosa quando le query aumenteranno di dimensione e gerarchia. Ne vedremo un esempio qui di seguito, sotto forma di una clausola WITH annidata. Potete trovare altri esempi in uno dei nostri precedenti articoli sull'argomento: CTE spiegate con esempi.

La sintassi della clausola WITH

La sequenza generale dei passaggi per eseguire una clausola WITH è la seguente:

1. Iniziare l'operazione WITH.
2. Specificare il nome dell'espressione per la query da definire.
3. Facoltativo: Specificare i nomi delle colonne separati da virgole.
4. Dopo aver assegnato il nome dell'espressione, inserire il comando AS. Le espressioni, in questo caso, sono gli insiemi di risultati denominati che verranno utilizzati successivamente nella query principale per fare riferimento alla CTE.
5. Scrivere la query necessaria per produrre il set di dati temporanei desiderato.
6. Se si lavora con più di una CTE o clausole WITH, iniziare ogni clausola successiva separata da una virgola e ripetere i passaggi da 2 a 4. Questa disposizione è detta anche "n". Questa disposizione è chiamata anche clausola nidificata WITH.
7. Fare riferimento alle espressioni definite sopra in una query successiva usando SELECT, INSERT, UPDATE, DELETE, oppure MERGE.

La sintassi per eseguire una clausola WITH è mostrata nello pseudo-codice seguente:

--CTE
WITH expression_name_1 (column_1, column_2,…,column_n)
AS
(CTE query definition 1),
expression_name_2 (column_1, column_2,…,column_n)
AS
(CTE query definition 2)

--Final query using CTE
SELECT expression_A, expression_B, ...
FROM expression_name_2

La clausola WITH sostituisce le normali subquery. L'unica differenza è che si può riutilizzare lo stesso insieme di risultati derivati più volte nel codice quando si usa la clausola WITH per generare una CTE. Ciò non è possibile con le subquery.

Come si è visto sopra, i parametri di esecuzione fondamentali per una clausola WITH sono:

• WITH: Utilizzata per creare una CTE o, uno o più set di dati temporanei.
• expression_name (column_1, …, column_n): Il nome dell'insieme di dati temporanei virtuali che verrà utilizzato nella query principale, mentre da column_1 a column_n sono i nomi delle colonne che possono essere utilizzate nelle fasi successive della query.
• AS (....): Questa sezione definisce la query che popolerà il CTE expression_name. Se si usa una CTE annidata, la query all'interno del secondo AS farà probabilmente riferimento alla prima CTE.
• SELECT expression_A, expression_B FROM expression_name: Questa sezione specifica la query esterna principale in cui l'istruzione SELECT (o le istruzioni INSERT, UPDATE, DELETE o MERGE) viene utilizzata su una o più CTE generate per produrre successivamente il risultato desiderato.

Tutti i parametri citati sono obbligatori. È possibile scegliere di utilizzare le clausole WHERE, GROUP BY, ORDER BY e/o HAVING a seconda delle necessità.

Quando viene eseguita una query con una clausola WITH, prima viene valutata la query menzionata all'interno della clausola e il risultato di questa valutazione viene memorizzato in una relazione temporanea. Quindi, la query principale associata alla clausola WITH viene eseguita utilizzando la relazione temporanea prodotta.

Questo esempio mostra una clausola WITH annidata utilizzando la stessa tabella OrderDetails come sopra. Una clausola WITH annidata, o una CTE annidata, coinvolge due CTE all'interno della stessa query, la seconda delle quali fa riferimento alla prima.

OBIETTIVO: scoprire il numero medio di ordini, o di vendite effettuate, da EmployeeID per ShipperID 2 e ShipperID 3.

QUERY:

--1st CTE
WITH cte_sales
AS
(SELECT
	EmployeeID,
	COUNT(OrderID) as Orders,
	ShipperID
FROM Orders
GROUP BY EmployeeID, ShipperID),

--2nd CTE (nested)
shipper_cte
AS
(SELECT *
FROM cte_sales
WHERE ShipperID=2 or ShipperID=3)

--Query using CTE
SELECT
	ShipperID, AVG(Orders) average_order_per_employee
FROM
shipper_cte
GROUP BY ShipperID;

RISULTATO:

Numero di risultati: 2

In questo caso, calcoliamo il numero medio di ordini per dipendente, ma solo per ShipperID 2 e ShipperID 3. Nella prima CTE, cte_sales il numero di ordini viene contato e raggruppato per EmployeeID e ShipperID. Nella seconda CTE, shipper_cte si fa riferimento alla prima CTE e si definiscono le condizioni ShipperID utilizzando una clausola WHERE. Quindi, nella query principale, si fa riferimento solo alla seconda CTE, shipper_cte per calcolare la media degli ordini per dipendente in base a ShipperID.

Ulteriori sfumature della sintassi associata alle clausole SQL WITH e alle CTE sono descritte in dettaglio nel Modulo 2 del corso Recursive Queries, che contiene anche una raccolta di informazioni sulle clausole CTE.

Casi d'uso della clausola SQL WITH

Quando è necessario utilizzare la clausola WITH? Ci sono alcuni casi d'uso particolari. La maggior parte dei quali è orientata alla convenienza, facilità di sviluppo e manutenzione delle query.

Le applicazioni principali e i vantaggi associati alle CTE in SQL possono essere riassunti nei seguenti punti:

• Migliora la leggibilità del codice - La programmazione letteraria è un approccio introdotto da Donald Kuth, che mira a organizzare il codice sorgente secondo l'ordine della logica umana, in modo che possa essere compreso con il minimo sforzo, come se si leggesse un romanzo in modo sequenziale. La clausola SQL WITH fa esattamente questo, creando tabelle virtuali con nome e suddividendo i calcoli di grandi dimensioni in parti più piccole che possono essere combinate in un secondo momento, nella query finale SELECT, o in un'altra istruzione invece di raggruppare il tutto in un'unica estesa porzione di codice.
• Migliora la manutenzione del codice - La manutenzione va di pari passo con la leggibilità. Man mano che le query e i database aumentano nel tempo, sarà sempre più frequente la necessità di eseguire il debug e la risoluzione di problemi. Un codice più facile da leggere è un codice più facile da mantenere!
• Alternativa a una vista - Le CTE possono sostituire le viste e possono SELECT, INSERT, UPDATE, DELETE, o MERGE. Questo può essere particolarmente utile se non si dispone dei diritti di un sistema per creare una vista o se non si vuole creare una vista solo per essere usata in una singola query.
• Superare le limitazioni delle istruzioni - Le CTE possono servire per esempio a superare il limiti delle istruzioni SELECT, per eseguire funzioni non deterministiche usando GROUP BY.
• Elaborazione di strutture gerarchiche - Questa è una delle applicazioni più avanzate delle CTE e si ottiene con le cosiddette CTE ricorsive. Le query ricorsive possono fare riferimento a se stesse, consentendo di attraversare modelli gerarchici complessi. Continua a leggere per saperne di più.

Altri casi di utilizzo delle CTE sono illustrati in uno dei nostri precedenti articoli, "Quando usare le CTE". Il corso Recursive Queries su LearnSQL.it vi aiuterà a capire integralmente l'argomento delle CTE, con esempi dettagliati, per aiutarvi a padroneggiare questo aspetto con esercizi pratici.

La clausola ricorsiva WITH

Gli esempi precedenti utilizzano clausole WITH non ricorsive. Le CTE ricorsive sono una delle funzionalità più avanzate della clausola WITH, che consente di fare riferimento a se stessa all'interno della CTE. Questo facilita notevolmente la navigazione di strutture gerarchiche complesse di dati, come la distinta base dei materiali (Diba) e gli organigrammi.

Se avete familiarità con la programmazione ricorsiva, la funzione ricorsiva della clausola WITH incorpora analogamente un caso base e un passo ricorsivo.

Considerazioni finali sulla clausola WITH di SQL

In questo articolo abbiamo illustrato la sintassi di base e alcuni esempi di come e quando è possibile definire e utilizzare le clausole WITH o CTE. È fondamentale esercitarsi per comprendere meglio la loro reale applicazione nel codice! A tale scopo, vi suggerisco il corso Recursive Queries qui su LearnSQL.it. Nel corso, troverete ulteriori esempi pratici e applicazioni uniche della clausola WITH, con tutorial interattivi su come utilizzare le CTE (ricorsive e non ricorsive) nel vostro lavoro quotidiano.

Una volta acquisita una buona padronanza d'uso delle clausole WITH, vi sorprenderete di quanto possa migliorare la vostra scrittura del codice SQL!