Cos'è un CTE in T-SQL? Una guida approfondita per i principianti con 7 esempi

Una Common Table Expression (CTE) è una potente funzione T-SQL che semplifica la creazione di query in SQL Server. Le CTE funzionano come tabelle virtuali (con record e colonne) che vengono create al volo durante l'esecuzione di una query. Vengono consumate dalla query e distrutte dopo l'esecuzione della stessa.

In alcuni casi, come quando la query si aspetta dati in un formato specifico e le tabelle di origine hanno i dati in un altro formato, una CTE può fare da ponte per trasformare i dati nelle tabelle di origine nel formato previsto dalla query.

In questo articolo spiegheremo come creare una CTE in T-SQL, il dialetto SQL utilizzato da Microsoft SQL Server. Per saperne di più sulle CTE in T-SQL, vi consiglio il nostro corso interattivo Query ricorsive in MS SQL Server. Contiene oltre 100 esercizi pratici sulle CTE in T-SQL. Potrete mettere in pratica da soli tutto ciò che viene trattato in questo articolo!

Espressioni di tabella comuni (CTE) in T-SQL

Durante l'esecuzione di una query, è possibile fare riferimento alla CTE come a una qualsiasi tabella normale. Possono essere utilizzate nelle istruzioni SELECT, INSERT, UPDATE e DELETE. Le CTE non facevano parte del linguaggio SQL originale. Sono state introdotte come nuova funzionalità nella definizione dello standard SQL nel 1999 (SQL 3). Nel 2005 sono state rese disponibili in SQL Server.

Le query SQL di esempio mostrate in questo articolo si baseranno sulla tabella olympic_games. La tabella contiene i risultati di tutti i giochi olimpici, compresi i 3 record (oro, argento e bronzo) per ogni sport.

Esempio 1: una semplice CTE

Per spiegare la sintassi CTE in T-SQL, scriviamo una semplice query. Supponiamo di voler creare un report con i Paesi e il numero di volte in cui ogni Paese ha vinto un metallo d'oro in una maratona. Possiamo creare una prima CTE chiamata gold_in_marathon per restituire le righe delle medaglie d'oro nelle maratone maschili e femminili. Nella CTE gold_in_marathon, abbiamo bisogno solo delle colonne city, year e country.

Si noti che tutte le CTE iniziano con la parola chiave WITH, seguita da parentesi. Tra le parentesi si scrive la query che si vuole che la CTE restituisca. Di seguito è riportata la query completa. La CTE è evidenziata in grassetto:

WITH gold_in_marathon AS
(
  SELECT 
    city, 
    year, 
    country
  FROM olympic_games
  WHERE medal_type = 'Gold' 
  AND sport IN ('Marathon Men', 'Marathon Women')
)
SELECT 
  country, 
  count(*) AS gold_medals_in_marathon 
FROM gold_in_marathon
GROUP BY country
ORDER BY gold_medals_in_marathon DESC;

Nella query T-SQL di cui sopra, è possibile identificare due query diverse. La prima è definita dalla clausola WITH racchiusa tra parentesi: è la query che definisce il contenuto della CTE. Si tratta di una normale query SQL: si possono inserire tutte le diverse caratteristiche SQL (WHERE, GROUP BY, HAVING, UNION, ecc.). Il database utilizza il risultato per creare una tabella virtuale chiamata gold_in_marathon. Si può vedere il nome della CTE (gold_in_marathon) dopo la clausola WITH.

La seconda query è la query esterna. Fa riferimento alla CTE gold_in_marathon come a qualsiasi altra tabella. Al termine dell'esecuzione della query esterna, la CTE gold_in_marathon viene distrutta e non è più possibile farvi riferimento.

Si noti che la CTE ha solo le colonne city, year e country della tabella olympic_games. Dopo aver creato la CTE, il database esegue la query esterna, che a sua volta legge la CTE gold_in_marathon, raggruppando le righe per country e utilizzando la funzione COUNT() per ottenere il numero di medaglie d'oro di ciascun Paese. Di seguito sono riportati i risultati della query:

Esempio 2: Utilizzo di CTE con colonne rinominate (alias) in T-SQL

Nella prossima query, rinomineremo esplicitamente una colonna nella CTE utilizzando un alias. Supponiamo di volere un report con la classifica nazionale dei risultati della maratona (donne e uomini). Ogni giocatore riceverà 3 punti per ogni medaglia d'oro, 2 punti per ogni medaglia d'argento e 1 punto per ogni medaglia di bronzo. Creeremo una CTE chiamata player_points per calcolare i punti di ciascun giocatore. Poiché utilizzeremo una colonna calcolata per i punti, dovremo assegnare un nome a questa colonna utilizzando un alias. Vediamo la query completa:

WITH player_points AS
(
  SELECT 
    country,
    winner_name, 
    SUM(
      CASE medal_type 
        WHEN 'Gold' THEN 3
	  WHEN 'Silver' THEN 2
	  WHEN 'Bronze' THEN 1
	END
	)  AS player_total
    FROM   olympic_games
    WHERE sport in ('Marathon Men', 'Marathon Women')
    GROUP BY country,winner_name
)
SELECT 
  country, 
  SUM(player_total) AS country_points 
FROM player_points
GROUP BY country
ORDER BY country_points DESC;

Nella CTE player_points, calcoliamo i punti di ogni giocatore utilizzando una colonna calcolata, che non ha un nome. Dobbiamo definire un nome per questa colonna, per potervi fare riferimento nella query esterna.

Un modo per definire un nome è usare un alias (più avanti vedremo un altro modo) usando la clausola AS. Si può notare che la colonna è stata chiamata player_total nella definizione della CTE. Nella query esterna, raggruppiamo le righe per paese al fine di calcolare i punti totali di ciascun paese. Si noti che si utilizza l'espressione SUM(player_total) e che si utilizza un alias per rinominare la colonna in country_points. I risultati della query sono riportati di seguito:

Prima di chiudere questa sezione, vorrei suggerire l'articolo What Is a CTE in SQL Server?, dove si possono trovare molti esempi di query che utilizzano le CTE in T-SQL. L'articolo What Is a Common Table Expression (CTE) in SQL? tratta l'argomento delle CTE in SQL standard; entrambi gli articoli sono un buon complemento a questo. Se volete un corso sulle CTE e sulle query ricorsive, vi suggerisco di nuovo Query ricorsive in MS SQL Server.

Esempio 3: Utilizzo di più CTE nella stessa query T-SQL

In questa sezione mostreremo due esempi di query che utilizzano più di una CTE. Nel primo esempio, utilizzeremo due CTE indipendenti e la query principale accederà a entrambe.

Supponiamo di voler creare un report con i nomi degli atleti olimpici che hanno vinto almeno una medaglia d'oro e una d'argento. La prima CTE si chiama gold. Dopo il nome della CTE, si possono vedere i nomi delle colonne (winner_name e gold_medals) definiti esplicitamente tra le parentesi. Questo è l'altro modo per rinominare una colonna nella CTE.

Il nome della seconda CTE è silver e ha due colonne: winner_name e silver_medals. Si noti che non è stata inserita la clausola WITH prima della seconda CTE. La clausola WITH viene utilizzata una sola volta prima della definizione della prima CTE. Se si devono definire altre CTE, basta una virgola prima di iniziare la definizione delle CTE successive.

WITH gold(winner_name,gold_medals) AS
(
  SELECT 
    winner_name, 
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Gold'
  GROUP BY winner_name
), 
silver(winner_name,silver_medals) AS
(
  SELECT 
    winner_name,
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Silver'
  GROUP BY winner_name
)
SELECT  
  g.winner_name, 
  g.gold_medals, 
  s.silver_medals
FROM gold g 
JOIN silver s 
ON g.winner_name = s.winner_name;

Nella query T-SQL precedente, abbiamo creato due CTE: gold e silver con l'indirizzo winner_name e la quantità di medaglie (oro o argento) vinte da ciascun giocatore. Poi, nella query principale, abbiamo unito entrambe le CTE come se fossero tabelle normali, utilizzando la clausola JOIN. Poiché JOIN senza parole chiave funziona come una INNER JOIN, solo i record per la stessa winner_name in entrambe le tabelle saranno mostrati nel risultato della query. L'output è mostrato di seguito:

Esempio 4: Una query SQL Server con una CTE basata su un'altra CTE

In seguito, creeremo due CTE in una query, ma la seconda CTE sarà basata sulla prima CTE. Supponiamo di volere una query per ottenere i 3 migliori Paesi per quantità di medaglie vinte in una partita olimpica. Non vogliamo ripetere i Paesi, quindi se la prima e la seconda posizione riguardano lo stesso Paese, vogliamo mostrarlo una sola volta. La query sarà la seguente:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS quantity_of_medals
  FROM olympic_games
  GROUP BY city, year, country
), 
country_best_game AS
(
  SELECT  
    country, 
    city, 
    year, 
    quantity_of_medals 
  FROM medals m1
  WHERE quantity_of_medals = ( 
    SELECT max(quantity_of_medals) 
    FROM medals m2
    WHERE  m1.country = m2.country
  )
)
SELECT TOP 3 country, 
  city, 
  year, 
  quantity_of_medals 
FROM country_best_game 
ORDER BY quantity_of_medals DESC;

In questa query, abbiamo creato una CTE chiamata medals con la quantità di medaglie ottenute da ciascun paese in ogni partita olimpica in cui il paese ha vinto almeno una medaglia. Successivamente, verrà calcolata una seconda CTE chiamata country_best_game (basata sulla prima CTE); essa contiene una riga per ogni paese con il numero massimo di medaglie ottenute in una singola partita. Infine, nella query principale, selezioniamo solo i tre Paesi con il maggior numero di medaglie vinte in una singola partita. I risultati della query sono riportati di seguito:

A questo punto, vorrei suggerirvi l'articolo Come imparare le espressioni di tabella comuni di SQL, dove potrete leggere i diversi approcci all'apprendimento delle CTE. Vedrete anche diversi esempi di query che utilizzano le CTE in SQL standard.

Esempio 5: Utilizzo di una CTE in una INSERZIONE T-SQL

In T-SQL, le CTE possono essere utilizzate anche nelle istruzioni UPDATE, INSERT e DELETE. Come regola generale, qualsiasi comando SQL che consenta un'istruzione SELECT incorporata (ad esempio un'istruzione CREATE VIEW ) può supportare una CTE. Vediamo un esempio di INSERT che utilizza un'espressione di tabella comune in T-SQL.

Supponiamo di avere una tabella chiamata country_medals_by_game con le colonne country, city, game, number_of_medals e delta_with_previous_game. Il contenuto di ogni colonna è chiaro, tranne che per la colonna delta_with_previous_game. Tuttavia, questa colonna non verrà ancora utilizzata, quindi la spiegheremo in seguito. Il metodo INSERT per popolare la tabella è il seguente:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
INSERT INTO country_medals_by_game(city,year,country,quantity_of_medals)
SELECT city, year, country, medals_won
FROM medals;

Nel precedente INSERT abbiamo usato una CTE chiamata medals. Si noti che INSERT inizia con la definizione della CTE (si possono definire più CTE, se necessario). Dopo la definizione della CTE, inizia la normale istruzione INSERT. In questo esempio, INSERT utilizza un'istruzione SELECT, che a sua volta accede alla CTE medals definita in precedenza.

Esempio 6: Utilizzo di una CTE in un UPDATE di SQL Server

La colonna delta_with_previous_game memorizza la differenza di medaglie vinte da un Paese in due Olimpiadi consecutive. Se il Paese ha aumentato il numero di medaglie vinte, questa colonna avrà come valore positivo la differenza del numero di medaglie. Se il Paese ha vinto meno medaglie rispetto all'Olimpiade precedente, la colonna avrà un valore negativo. L'UPDATE per popolare la colonna è:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
UPDATE country_medals_by_game 
SET delta_with_previous_game = (
 SELECT COALESCE(quantity_of_medals - COALESCE(prevgame.medals_won,0),0)
 FROM medals prevgame
 WHERE country_medals_by_game.year = prevgame.year + 4
  AND country_medals_by_game.country = prevgame.country
);

In questo UPDATE, abbiamo iniziato con una clausola WITH per definire la stessa CTE medals che abbiamo utilizzato. Quando la sezione di definizione della CTE termina, inizia l'istruzione UPDATE. Nella clausola SET si utilizza una subquery per calcolare la differenza di medaglie vinte in due Olimpiadi consecutive. Si noti che la sottoquery accede alla CTE medals e la condizione ...

country_medals_by_game.year = prevgame.year + 4

... è di abbinare una riga di country_medals_by_game con la riga delle medaglie della partita olimpica precedente (avvenuta quattro anni prima). Una cosa interessante da notare è questa: Per i paesi che non hanno partecipato a due giochi contigui, impostiamo la colonna delta_with_previous_game su NULL. Questo indica che non possiamo calcolare la differenza; usare uno zero per questa colonna non è corretto perché significherebbe che il paese ha partecipato al gioco precedente, cosa che non ha fatto.

Esempio 7: Utilizzo di CTE per Query ricorsive in T-SQL

In SQL Server è comune avere tabelle che rappresentano gerarchie di dati (come dipendenti-gestori, parti-sottoparti o genitori-figli). Per attraversare queste gerarchie in qualsiasi direzione, dall'alto verso il basso o dal basso verso l'alto, SQL Server utilizza un costrutto chiamato CTE ricorsivo.

Per avere una gerarchia di dati nel nostro database Olympic, aggiungeremo una coppia di colonne alla tabella olympic_games. Supponiamo di voler identificare le medaglie che rappresentano un record mondiale. Possiamo aggiungere una colonna di testo chiamata record e impostarla con true quando una medaglia è associata a un nuovo record mondiale.

Inoltre, sappiamo che ogni record infrange un record precedente, quindi aggiungeremo un'altra colonna chiamata previous_record_medal in cui inseriremo medal_id del record precedente. Ora abbiamo una gerarchia di dati da leggere con una query ricorsiva; mostriamo una vista parziale della tabella olympic_games con le nuove colonne:

Supponiamo di voler ottenere un report con l'elenco dei record mondiali nella maratona maschile. Possiamo iniziare mostrando il record mondiale attuale, poi quello immediatamente precedente e così via. Avremo bisogno di una CTE ricorsiva per attraversare la gerarchia dei record della maratona maschile. Il suo aspetto sarà il seguente:

WITH record_history (medal_id, year, winner, country, prev_record_medal_id) AS
(
  SELECT 
    medal_id, 
    year, 
    winner_name, 
    country, 
    previous_record_medal
  FROM olympic_games
  WHERE sport = 'Marathon Men' AND record = 'true'

  UNION ALL

  SELECT 
    og.medal_id,
    og.year,
    og.winner_name,
    og.country, 
    og.previous_record_medal
  FROM olympic_games og 
  JOIN record_history mrh 
  ON og.medal_id = mrh.prev_record_medal_id
)
SELECT * 
FROM record_history;

La CTE record_history è ottenuta come risultato di una UNION ALL. La prima query nella UNION è per ottenere il record mondiale attuale; si noti la condizione record = true. Dopo UNION ALL, abbiamo un'altra query che ottiene tutti i record precedenti nella maratona maschile. La chiave per collegare una riga di record di medaglie con la riga di record di medaglie precedente è la condizione:

og.medal_id = mrh.prev_record_medal_id

I risultati della query sono riportati di seguito:

Per evitare un ciclo infinito in una CTE ricorsiva, esiste un limite al numero di invocazioni consentite. In SQL Server, questo limite è definito per impostazione predefinita come 100. Tuttavia, è possibile modificare questo limite utilizzando il parametro MAXRECURSION alla fine della query ricorsiva.

Se volete approfondire il tema delle query ricorsive, vi suggerisco gli articoli Come scrivere una CTE ricorsiva in SQL Server e Fallo in SQL: L'attraversamento ricorsivo dell'albero in SQL. Troverete numerosi esempi e diversi approcci per spiegare le query ricorsive.

Le CTE sono una potente caratteristica del linguaggio T-SQL

In questo articolo abbiamo illustrato come utilizzare le CTE di T-SQL per semplificare le query complesse per i database di SQL Server. Prima di concludere, vorrei suggerire alcuni articoli relativi a SQL Server. Il primo è Top 5 SQL CTE Interview Questions, dove si possono trovare consigli su come gestire un colloquio per un lavoro in SQL. Un altro articolo interessante è Come installare Microsoft SQL Server 2019 e SQL Server Management Studio, dove potete trovare assistenza per l'installazione di SQL Server.

Infine, vorrei incoraggiarvi a dare un'occhiata al corso Query ricorsive in MS SQL Server. Potrete imparare come elaborare alberi e grafici in T-SQL e come organizzare efficacemente le vostre query. Sviluppate le vostre competenze e aumentate il vostro patrimonio!