CTE di SQL: Una panoramica completa delle espressioni di tabella comuni

Questo articolo fornisce una guida dettagliata a tutto ciò che occorre sapere sulle espressioni di tabella comuni (CTE), dalla definizione di base alle query ricorsive più complesse. Se il database che utilizzate supporta le CTE, ecco tutto ciò che vi serve per sfruttarle al meglio.

Le espressioni di tabella comuni - chiamate anche CTE, clausole WITH o query ricorsive (anche se quest'ultimo nome è in realtà un'applicazione specifica) - sono una caratteristica relativamente nuova di SQL. Il loro scopo è quello di semplificare le query complesse, rendendole più facili da leggere, comprendere e mantenere. Per avere un quadro completo della situazione, si consiglia di iniziare a leggere che cos'è un'espressione di tabella comune. Quindi, allacciate le cinture e preparatevi, perché questo articolo vi guiderà attraverso tutte le diverse risorse CTE (corsi, esercizi, esempi e spiegazioni) disponibili sul nostro sito web. LearnSQL.it sito web.

Le CTE sono utili per suddividere query complesse e di grandi dimensioni in parti più piccole e comprensibili, proprio come le viste, le subquery e le tabelle temporanee. Inoltre, come le viste, le subquery e le tabelle temporanee, è possibile combinare le CTE per ottenere un risultato finale. Tuttavia, il vantaggio delle CTE è che non generano oggetti che persistono o occupano spazio nel database (come le viste e le tabelle temporanee). Inoltre, sono più facili da leggere e interpretare rispetto alle subquery.

Se seguite il nostro Query ricorsive corso, avrete una panoramica completa sulle CTE di SQL. Imparerete la sintassi delle CTE e i diversi modi di utilizzarle. Avrete a disposizione oltre 100 esercizi interattivi che potrete eseguire liberamente sul nostro database online. Tutto ciò che serve è una conoscenza di base di SQL e un browser con una connessione a Internet.

Sintassi CTE

La forma generale di una CTE SQL inizia con una clausola WITH seguita da una definizione di query (una normale istruzione SELECT ) a cui viene assegnato un nome. La definizione è seguita da un'istruzione SELECT che si riferisce alla query CTE con il nome che le è stato assegnato, proprio come se si trattasse di una tabella o di una vista. Ad esempio:

WITH cte_name AS (cte_query_definition)
SELECT *
FROM cte_name;

La clausola WITH dirige la query e contiene una sottoquery a cui viene assegnato un nome. La query principale (situata dopo la clausola WITH ) è una normale istruzione SELECT che può utilizzare la sottoquery con nome tutte le volte che è necessario.

Quando si esegue una query che contiene CTE, il motore di database esegue prima le query CTE, salvando i risultati delle query CTE fino al termine dell'esecuzione dell'intera query; i risultati CTE possono essere considerati risultati intermedi. Utilizza questi risultati intermedi come tabelle per comporre il risultato finale. Quindi restituisce il risultato finale e scarta i risultati intermedi generati in precedenza.

Un esempio di base di CTE

Vediamo un semplice esempio. Abbiamo uno schema con tre tabelle: employee, division, e payment. È necessario ottenere un elenco dei dipendenti con la loro retribuzione massima e la divisione a cui appartiene ciascun dipendente. È possibile creare una CTE che risolva innanzitutto la retribuzione massima di ciascun dipendente in una sottoquery chiamata max_paid. Poi, nella principale SELECT, si esegue una join max_paid con employee e division per ottenere il risultato finale:

WITH max_paid (employee_id, max_payment) AS (
	SELECT
		emp.employee_id,
		MAX(pay.payment) AS max_payment
	FROM
		employee AS emp
INNER JOIN payment AS pay ON
pay.employee_id = emp.employee_id
	GROUP BY
		emp.employee_id
	)
SELECT
	emp.employee_id,
	emp.name AS employee_name,
	div.name AS division_name,
	mp.max_payment
FROM max_paid mp
	INNER JOIN employee AS emp ON
		emp.employee_id = mp.employee_id
	INNER JOIN division AS div ON
		div.division_id = emp.division_id;

Quando il database esegue questa query, costruisce innanzitutto un set di dati temporaneo con i risultati della query CTE e lo denomina max_paid. Poi, nella SELECT esterna, usa max_paid come se fosse una tabella. Si unisce max_paid con le tabelle reali dello schema (employee e division) per costruire il risultato finale.

Per una comprensione più approfondita della sintassi CTE di SQL, vi consiglio questi 5 esempi pratici di clausole WITH.

Più CTE in una singola query

È possibile utilizzare più di una CTE nella stessa query. Per farlo è sufficiente separare ogni definizione di CTE con una virgola:

WITH cte1 AS (
	SELECT ...
	FROM ...
),
cte2 AS (
	SELECT ...
	FROM ...
)
SELECT ...
FROM cte1 JOIN cte2, ...

Ogni CTE può fare uso di altre CTE definite in precedenza nella stessa query; questa operazione si chiama annidamento. Ciò consente alle CTE di scomporre query grandi e complesse in sottoquery più piccole (e più gestibili). È possibile risolvere gradualmente ogni parte di un problema fino a semplificarlo in un'unica soluzione finale SELECT.

L'esempio seguente utilizza due CTE annidate che ottengono informazioni da una tabella chiamata sales. La prima CTE, SalesByDayRegionottiene i totali delle vendite per giorno e per regione. La seconda, SalesByDaysi basa su SalesByDayRegion per ottenere i totali delle vendite per ogni giorno. L'ultima SELECT unisce entrambe le CTE per calcolare la percentuale di vendite per ogni regione in relazione ai totali giornalieri.

Ecco la query:

WITH SalesByDayRegion AS (
	SELECT
		day,
		region,
		SUM(amount) AS AmountByDayRegion
	FROM 	Sales
	GROUP BY day, region
	),
	SalesByDay AS (
	SELECT
		day
		SUM(GroupedAmount1) AS AmountByDay
	FROM 	Sales
	GROUP BY day
	)
SELECT
	sdr.day,
	sdr.region,
	AmountByDayRegion
	AmountByDayRegion / AmountByDay AS Percentage
FROM SalesByDayRegion sdr 
INNER JOIN SalesByDay sd
ON sdr.day = sd.day;

Query ricorsive

WITH Le clausole, cioè le CTE, danno a SQL la possibilità di implementare la ricorsione. Questo elimina la necessità di farlo con altri linguaggi di programmazione, ottenendo così una maggiore efficienza nell'esecuzione delle query.

In qualsiasi linguaggio di programmazione, la ricorsione viene implementata facendo in modo che una funzione o una procedura si richiami ripetutamente fino a quando non viene soddisfatta una condizione di terminazione. Ogni iterazione ricorsiva aggiunge o modifica i dati risultanti dall'iterazione precedente e fornisce i risultati all'iterazione successiva. Quando la condizione di terminazione è soddisfatta, viene restituito il risultato finale.

In SQL, la ricorsione è possibile perché le CTE possono fare riferimento a se stesse. Nella maggior parte dei dialetti SQL (tranne Transact SQL), per indicare la ricorsione si usa la parola RECURSIVE dopo WITH. Per saperne di più su cosa sia una CTE ricorsiva, si veda qui.

Le CTE ricorsive sono composte dai seguenti elementi: un membro di ancoraggio, un membro ricorsivo, un controllo di terminazione e un'invocazione.

Il membro di ancoraggio stabilisce il punto di partenza della ricorsione. Questa parte del CTE deve essere risolvibile senza invocare se stessa. Il membro ricorsivo utilizza i risultati di un'iterazione precedente per elaborare o aggiungere altri dati ai risultati, ottenendo un nuovo insieme di risultati per l'iterazione successiva o, quando la condizione di terminazione è soddisfatta, un insieme di risultati per il membro di invocazione.

Il membro di ancoraggio e il membro ricorsivo vengono combinati utilizzando l'operatore UNION. Ciò significa che gli insiemi di risultati di entrambi i membri devono avere la stessa struttura di colonne: le colonne devono essere nello stesso ordine e devono avere gli stessi tipi di dati.

Nell'esempio seguente, si utilizza una CTE ricorsiva per calcolare il fattoriale dei numeri da 1 a 5:

WITH RECURSIVE factorial(n, factorial) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, (n +1) * factorial FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

In questo esempio, il membro di ancoraggio della CTE ricorsiva è:

SELECT 1, 1

E il membro ricorsivo, insieme alla condizione di terminazione, è la seguente SELECT:

SELECT n + 1, (n +1) * factorial 
FROM factorial 
WHERE n < 5

L'invocazione è semplicemente SELECT * della CTE factorial. Si noti che questo SELECT tratta factorial come se fosse una tabella. Se siamo interessati a vedere solo le prime tre righe del risultato del calcolo del fattoriale, possiamo aggiungere questa informazione all'invocazione:

SELECT * 
FROM factorial 
WHERE n <= 3;

Questo esempio è estremamente semplice. Le CTE ricorsive possono essere utilizzate per risolvere esigenze più complesse, come l'attraversamento di un insieme di dati che rappresenta una struttura ad albero. Si potrebbero utilizzare per esplorare l'organigramma di un'azienda, come vedremo in un esempio successivo.

Se volete approfondire la ricorsività in SQL, consultate il nostro corso onlineQuery ricorsive .

I vantaggi delle espressioni di tabella comuni

Le espressioni di tabella comuni offrono molti vantaggi, dal miglioramento della comprensibilità delle query all'attraversamento di strutture di dati gerarchiche. È persino possibile disegnare immagini con SQL. Come punto di partenza, vi suggerisco di leggere gli articoli già citati, Le CTE spiegate con esempi e 5 esempi pratici di CTE in SQL. Poi, continuate a leggere per capire quando è utile ed efficace usare le CTE.

Facilitare l'organizzazione e la chiarezza delle query

Le CTE consentono di risolvere query complesse suddividendole in query più piccole, più gestibili e più facili da leggere. A sua volta, la possibilità di utilizzare CTE annidate consente di procedere gradualmente verso una soluzione.

Una CTE è equivalente a una sottoquery, ma con un vantaggio molto importante: è possibile assegnare a una CTE un nome e utilizzarla ripetutamente in diverse parti della query. È come se si creasse una vista, con la differenza che la vista è un oggetto permanente del database. La CTE esiste solo finché si esegue la query, poi scompare senza lasciare traccia. L'esempio che segue mostra come creare una CTE per ottenere dati di riepilogo da una tabella sales e poi usare questa CTE per fare diverse query:

WITH TotalSalesByCategory AS (
	SELECT customer_category, SUM(amount) AS total_sales
	FROM sales
	GROUP BY customer_category
	)
SELECT 'big customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('A', 'B', 'C')
UNION
SELECT 'medium customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('D', 'E', 'F')
UNION
SELECT 'small customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('G', 'H', 'I');

Scoprite come ridurre le query in questi articoli:

• Come organizzare le query SQL con le CTE
• Migliorare la leggibilità delle query con le espressioni di tabella comuni

Aggregazioni a più livelli

Abbiamo visto diverse opzioni per includere più CTE nella stessa query. In questo modo, si moltiplica la potenza delle CTE per ridurre la complessità di una query e migliorarne la leggibilità. Le opzioni per l'utilizzo di più CTE in SQL si riducono a:

1. CTE multiple indipendenti.
2. CTE annidate.
3. Utilizzare una CTE ricorsiva insieme ad altre non ricorsive.

Scoprite come sfruttare le CTE combinate nei seguenti articoli:

• Come scrivere più CTE in SQL
• Come utilizzare 2 CTE in una singola query SQL
• È possibile utilizzare più dichiarazioni WITH in SQL?

Interrogazione di dati gerarchici

I dati gerarchici sono caratterizzati da una relazione ordinata tra i loro elementi. In generale, i dati gerarchici sono organizzati in livelli; è possibile scoprire quali dati si trovano "sopra" o "sotto" altre informazioni.

Nei database relazionali, le relazioni gerarchiche possono essere stabilite all'interno di una tabella facendo in modo che ogni riga contenga un campo che le permetta di essere correlata a una riga di livello superiore all'interno della stessa tabella. In questo modo, è possibile costruire strutture di dati gerarchiche con righe "genitore" e righe "figlio", solitamente rappresentate visivamente come una struttura ad albero.

Le CTE vengono utilizzate anche per interrogare i dati gerarchici. Per saperne di più su come interrogare i dati gerarchici in SQL, leggete l'articolo.

Un tipico esempio di struttura di dati gerarchici è l'organigramma di un'azienda, dove le righe della tabella contengono i dati dei dipendenti e ogni dipendente fa riferimento al proprio capo. Per maggiori dettagli, leggete come trovare tutti i dipendenti di ogni manager in SQL.

Divertitevi a disegnare immagini con le CTE

Cercate un modo divertente per scrivere query ingegnose in SQL? Non si possono creare grandi opere d'arte con SQL, ma si possono realizzare disegni schematici utilizzando le CTE ricorsive.

Abbiamo già detto che è possibile utilizzare la ricorsione per attraversare strutture di dati gerarchiche ad albero, come ad esempio un organigramma. Forse vi sorprenderà sapere che è possibile disegnare e attraversare strutture gerarchiche con SQL. Per scoprirlo, leggete questo articolo sull'uso di SQL per disegnare un albero di Natale piuttosto particolare.

Alternative alle CTE di SQL

Tralasciando la ricorsione (che in SQL è una virtù unica delle CTE), il resto dei vantaggi offerti dalle CTE può essere ottenuto con subquery, viste e tabelle temporanee. Confrontiamo ciascuna di esse con una CTE.

CTE vs. subquery

Le subquery in SQL devono essere scritte tra parentesi e incluse nel resto degli elementi della query principale. Ciò significa che non danno molta chiarezza alla query. Le CTE vengono scritte separatamente, all'inizio della query e all'interno della loro clausola WITH. Questo rende la lettura della query molto più semplice. Se non si è interessati a vedere il funzionamento interno della CTE, si può trascurare la clausola WITH e leggere solo la SELECT principale per capire i risultati.

Per maggiori dettagli sulle differenze tra CTE e subquery, si può leggere questo articolo che illustra le differenze tra CTE e subquery.

Inoltre, le subquery non sono riutilizzabili. Se si desidera utilizzare il risultato di una subquery in diverse parti di una query, è necessario riscriverla ogni volta. Le CTE, invece, possono essere riutilizzate quante volte si vuole nella SELECT principale o in altre CTE della stessa query. Questo migliora la facilità di lettura e le prestazioni della query: ogni CTE viene eseguita una sola volta e i suoi risultati sono disponibili per tutta la query.

Infine, è bene notare che le sottoquery all'interno della clausola FROM hanno un alias che viene utilizzato nel resto della query per accedere ai loro risultati. Le altre subquery incluse nell'elenco di colonne di una clausola SELECT o WHERE non hanno un alias. Le CTE hanno sempre un nome che viene utilizzato per fare riferimento ad esse nel resto della query.

CTE e viste

Le viste hanno molto in comune con le CTE, ma c'è una differenza fondamentale tra loro. Le viste rimangono come oggetti nel database finché non vengono rimosse. Le CTE esistono solo nel contesto della query. Una volta terminata l'esecuzione della query, la CTE scompare. Per saperne di più, leggete questo articolo sulle differenze tra CTE e viste.

CTE e tabelle temporanee

Anche le tabelle temporanee presentano molte analogie con le CTE. Il ciclo di vita di una tabella temporanea termina con la sessione del database in cui la tabella temporanea è stata creata, quindi non ci si deve preoccupare di lasciare oggetti inutili che ingombrano lo schema del database. Ma c'è una differenza fondamentale: la creazione della tabella temporanea deve essere effettuata con un comando separato dalla query che la utilizza. Non è possibile creare una tabella temporanea all'interno di una query SELECT; se lo si facesse, si tratterebbe di una sottoquery, non di una tabella temporanea.

Se si crea una tabella temporanea e poi ci si accorge che è necessario aggiungervi una colonna o modificare qualcos'altro, è necessario cancellarla e ricrearla. Con una CTE, invece, è sufficiente apportare la modifica alla sua definizione e rieseguire la query.

Le CTE in diversi DBMS

Le CTE sono apparse nello standard SQL del 1999. La loro implementazione in ciascuno dei più diffusi sistemi di gestione di database (DBMS) è stata la seguente:

• PostgreSQL: versione 8.4, luglio 2009
• MS SQL Server: versione 2012, maggio 2012
• Oracle Database: versione 9.2, giugno 2002
• MySQL: versione 8.0, aprile 2018
• MariaDB: versione 10.2.2, maggio 2018

Google BigQuery offre anche il supporto per le CTE. Poiché viene eseguito sul Cloud come un data warehouse completamente gestito, è sufficiente sapere che attualmente offre il supporto per i CTE.

La sintassi delle CTE SQL è la stessa per tutti i DBMS. L'unica eccezione è rappresentata da MS SQL Server (T-SQL), dove la sintassi per scrivere una CTE ricorsiva non richiede l'uso della parola chiave RECURSIVE. Per saperne di più sulle CTE in MS SQL Server, leggete "La ricorsione in SQL Server " e guardate gli esempi di CTE in SQL Server.

Mettere in pratica le conoscenze sulle CTE di SQL

In questo articolo avete imparato tutto sulle espressioni comuni di tabella in SQL. Ora è necessario mettere in pratica queste conoscenze. Il modo migliore per farlo è seguire uno dei nostri corsi specifici sulle CTE e sulle query ricorsive, dove troverete tonnellate di esercizi interattivi.

Questi corsi sono rivolti a studenti, analisti di database intermedi e data scientist principianti. L'unico requisito è una certa padronanza dell'SQL di base, come le istruzioni SELECT, JOINs, GROUP BY, ecc.

Potete scegliere il nostro corso standard Query ricorsive, che è indipendente dal dialetto SQL, oppure scegliere un corso specifico per il DBMS di vostra scelta:

• Query ricorsive in MS SQL Server
• Query ricorsive in MySQL 8
• Query ricorsive in PostgreSQL

Nessuno di questi corsi richiede l'utilizzo di database propri. La nostra piattaforma vi fornisce un database pronto all'uso, completo di schema e popolato di informazioni. Questi ambienti di pratica sono sicuri; non dovete preoccuparvi di commettere errori, perché non c'è la possibilità di rompere nulla.

Consultate queste altre preziose risorse per rafforzare la vostra conoscenza delle query di WITH:

• Le 5 migliori domande del colloquio su SQL CTE
• 11 esercizi sulle espressioni tabellari comuni SQL

L'importanza di conoscere le CTE in SQL

La padronanza delle espressioni di tabella comuni (CTE) e delle query ricorsive di SQL inizia con la comprensione delle loro basi per poi passare alle applicazioni più complesse. Le nostre risorse curate rendono l'apprendimento delle CTE piacevole e pratico.

Per mantenere le proprie competenze in materia di CTE, è bene esercitarsi quotidianamente. Quando vi trovate di fronte a query complesse, valutate se l'uso di una o più CTE possa semplificare il compito, suddividendolo in parti più piccole e gestibili. Presto potreste ritrovarvi a utilizzare le CTE come routine nelle vostre attività SQL.

Inoltre, vi consiglio di consultare il nostro corsoQuery ricorsive per approfondire la conoscenza delle CTE in SQL. Vi fornirà gli strumenti per affrontare con sicurezza anche le query più complesse. Buon apprendimento!