Le Common Table Expressions (CTE) sono una funzionalità potente e versatile disponibile in molti sistemi di gestione di database relazionali, come SQL Server, PostgreSQL, Oracle e MySQL a partire dalla versione 8.0. Queste strutture consentono di definire una query temporanea che può essere referenziata all'interno di altre query, rendendo il codice SQL più leggibile e manutenibile. L'uso delle CTE è particolarmente utile per semplificare query complesse, migliorare la chiarezza del codice e gestire meglio le gerarchie e le ricorsioni.

Le CTE vengono definite utilizzando la parola chiave WITH, seguita dal nome della CTE e dalla query che la compone. La sintassi di base per la definizione di una CTE è la seguente:

```
WITH NomeCTE AS (
SELECT colonne
FROM tabella
WHERE condizioni
)
SELECT colonne
FROM NomeCTE
WHERE altre_condizioni;
```

Le CTE possono essere utilizzate per varie finalità, come la suddivisione di query complesse in parti più gestibili, l’implementazione di operazioni ricorsive e la semplificazione di operazioni di aggregazione. Inoltre, le CTE possono essere combinate tra loro, consentendo di creare query che possono utilizzare più CTE in una sola volta.

Una delle principali applicazioni delle CTE è nella gestione di query ricorsive. In questo contesto, una CTE può fare riferimento a se stessa, consentendo di navigare attraverso strutture gerarchiche. Ad esempio, consideriamo un database di un'organizzazione in cui abbiamo una tabella Dipendenti con una struttura gerarchica che rappresenta i manager e i loro sottoposti. Utilizzando una CTE ricorsiva, possiamo recuperare tutti i dipendenti sotto un determinato manager. La seguente query mostra come farlo:

```
WITH RECURSIVE SottoDipendenti AS (
SELECT ID, Nome, ManagerID
FROM Dipendenti
WHERE ManagerID = @ManagerID
UNION ALL
SELECT d.ID, d.Nome, d.ManagerID
FROM Dipendenti d
INNER JOIN SottoDipendenti sd ON d.ManagerID = sd.ID
)
SELECT *
FROM SottoDipendenti;
```

In questo esempio, la CTE SottoDipendenti inizia selezionando i dipendenti che riportano direttamente al manager specificato. Poi, la parte ricorsiva della CTE recupera ulteriormente i dipendenti che riportano ai dipendenti già selezionati, continuando fino a quando non vengono trovati più dipendenti. Questo consente di ottenere un elenco completo di tutti i dipendenti sotto un manager, indipendentemente dal livello gerarchico.

Un'altra applicazione delle CTE è nella semplificazione delle query con aggregazioni complesse. Ad esempio, supponiamo di avere una tabella Vendite e vogliamo calcolare il totale delle vendite per ogni rappresentante vendite, quindi selezionare solo i rappresentanti che hanno raggiunto un certo obiettivo di vendita. Possiamo usare una CTE per separare la logica della somma delle vendite dalla logica di filtraggio. La query seguente illustra questo approccio:

```
WITH TotaleVendite AS (
SELECT RappresentanteID, SUM(Importo) AS Totale
FROM Vendite
GROUP BY RappresentanteID
)
SELECT RappresentanteID
FROM TotaleVendite
WHERE Totale > @Obiettivo;
```

In questo caso, la CTE TotaleVendite calcola il totale delle vendite per ciascun rappresentante, e successivamente la query principale filtra solo i rappresentanti che hanno superato un determinato obiettivo di vendita.

Le CTE possono anche essere utilizzate per semplificare le query che richiedono join complessi. Ad esempio, in una situazione in cui abbiamo più tabelle e vogliamo ottenere un report dettagliato, possiamo utilizzare le CTE per rendere le query più leggibili. Supponiamo di avere tre tabelle: Clienti, Ordini e Prodotti. Possiamo definire CTE per ciascuna tabella e unirle successivamente in una query finale. Ecco un esempio:

```
WITH ClientiCTE AS (
SELECT ID, Nome
FROM Clienti
),
OrdiniCTE AS (
SELECT ClienteID, DataOrdine
FROM Ordini
),
ProdottiCTE AS (
SELECT OrdineID, NomeProdotto
FROM Prodotti
)
SELECT c.Nome, o.DataOrdine, p.NomeProdotto
FROM ClientiCTE c
JOIN OrdiniCTE o ON c.ID = o.ClienteID
JOIN ProdottiCTE p ON o.ID = p.OrdineID;
```

Questa strutturazione rende la query molto più chiara rispetto a una singola query complessa con più join annidati, migliorando la manutenibilità e la comprensione del codice.

Le CTE non hanno formule specifiche, ma la loro potenza risiede nella loro capacità di semplificare e strutturare le query SQL in modo più logico e facilmente interpretabile. L’uso delle CTE può ridurre la necessità di sottoselezioni complesse e migliorare la leggibilità del codice, il che è fondamentale in ambienti di lavoro collaborativi dove più sviluppatori possono lavorare sugli stessi database.

La funzionalità delle CTE è stata sviluppata nel contesto dell’evoluzione dei linguaggi di programmazione SQL e dei sistemi di gestione dei database. I principali sviluppatori e le comunità open source hanno collaborato per integrare queste funzionalità nei vari sistemi di gestione di database. In particolare, Microsoft ha implementato le CTE in SQL Server a partire dalla versione 2005, mentre PostgreSQL le ha introdotte nella versione 8.4. Queste implementazioni hanno reso le CTE un elemento standard nel linguaggio SQL, contribuendo alla loro adozione diffusa e al miglioramento delle pratiche di scrittura delle query.

In sintesi, le Common Table Expressions rappresentano un significativo passo avanti nella scrittura di query SQL più chiare e gestibili. La loro capacità di semplificare query complesse, gestire gerarchie e migliorare la leggibilità del codice le rende uno strumento prezioso per sviluppatori e analisti di dati. Con l'aumento della complessità dei dati e delle strutture organizzative, l’utilizzo delle CTE continuerà a guadagnare importanza nel panorama della programmazione SQL.