CTE in SQL: una guida completa con esempi

Come creare una CTE in SQL

Quando creiamo una CTE, usiamo la parola chiave WITH per iniziare la definizione della CTE. La sintassi generale di una CTE è la seguente:

WITH cte_name (column1, column2, ...) AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Dove:

• WITH: Avvia la definizione della CTE.
• cte_name: Il nome assegnato alla CTE (usato per farvi riferimento in seguito).
• Elenco di colonne opzionale: Specifica i nomi delle colonne per il set di risultati della CTE.
• Query principale: Fa riferimento alla CTE per nome, trattandola come una normale tabella.

Vediamo un esempio. Supponiamo di avere una tabella Employees e di voler selezionare i dipendenti che guadagnano uno stipendio superiore a 50.000 $.

Passaggio 1: Scrivi la query di base

Iniziamo scrivendo la query SELECT di base:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Passaggio 2: Incapsula la query usando la parola chiave WITH per creare una CTE

Usa la parola chiave WITH per dare un nome alla CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Passaggio 3: Usa la CTE nella query principale

Infine, fai riferimento alla CTE in un'istruzione SELECT richiamando il nome della CTE definito sopra.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Per riassumere i passaggi sopra, abbiamo usato la parola chiave WITH per definire la CTE chiamata HighEarningEmployees. La query interna è stata usata per generare il dataset temporaneo. La query principale fa riferimento a HighEarningEmployees per mostrare le colonne specificate EmployeeID, FirstName e LastName.

Perché le CTE in SQL sono utili

Dall'esempio sopra, potresti chiederti perché usare le CTE quando anche le query semplici restituiscono gli stessi risultati. Ecco i motivi:

Semplificano le query complesse

Le CTE suddividono istruzioni SQL complesse in parti più piccole e gestibili, rendendo il codice più facile da leggere, scrivere e mantenere. 

Supponiamo di avere tre tabelle: Orders, Customers e Products. Vogliamo trovare il fatturato totale generato da ciascun cliente che ha acquistato nel 2024. Se scriviamo la query senza usare una CTE, risulta confusa e difficile da leggere e capire.

-- Standard SQL: Hard to read nested logic
SELECT 
    c.CustomerName, 
    SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
JOIN Customers c 
    ON o.CustomerID = c.CustomerID
JOIN Products p 
    ON o.ProductID = p.ProductID
WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Usando una CTE, possiamo separare la logica in un formato più leggibile. Prima isoliamo il passaggio di "filtro e join", poi eseguiamo l'aggregazione.

-- Standard SQL: Cleaner with CTE
WITH OrderDetails AS (
    SELECT 
        o.OrderID, 
        c.CustomerName, 
        p.Price, 
        o.Quantity, 
        o.OrderDate
    FROM Orders o
    JOIN Customers c 
        ON o.CustomerID = c.CustomerID
    JOIN Products p 
        ON o.ProductID = p.ProductID
    WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Riutilizzo del codice

Le CTE aiutano a evitare duplicazioni permettendo di riutilizzare lo stesso set di risultati. Se devi calcolare un aggregato (come una somma) e poi filtrare in base a quell'aggregato, una CTE è perfetta.

Supponiamo di dover calcolare le vendite medie e totali per ogni categoria di prodotto. Definiamo il calcolo una volta in una CTE:

WITH CategorySales AS (
    SELECT 
        Category, 
        SUM(SalesAmount) AS TotalSales, 
        AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select from the CTE where the pre-calculated TotalSales is high
SELECT 
    Category, 
    TotalSales, 
    AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Altre applicazioni

Oltre a semplificare le query e favorire il riutilizzo del codice, le CTE hanno anche altri utilizzi. Non posso coprire nel dettaglio ogni possibile uso delle CTE. Il nostro corso Data Manipulation in SQL è un'ottima opzione se vuoi continuare a esercitarti. Tuttavia, qui riporto alcuni dei motivi principali:

• Organizzazione della query e leggibilità: le CTE migliorano la leggibilità del codice SQL dividendo le query in passaggi logici e sequenziali. Ogni fase del processo può essere rappresentata dalla sua CTE, rendendo l'intera query più facile da seguire.
• Attraversamento di dati gerarchici: le CTE aiutano a navigare nelle relazioni gerarchiche, come strutture organizzative, relazioni genitore-figlio o qualsiasi modello con livelli annidati. Le CTE ricorsive sono utili per interrogare dati gerarchici perché consentono di attraversare i livelli in modo iterativo.
• Aggregazioni multilivello: le CTE possono aiutare a eseguire aggregazioni a più livelli, ad esempio calcolare le vendite a diverse granularità (per mese, trimestre e anno). Usare le CTE per separare questi passaggi di aggregazione assicura che ogni livello sia calcolato in modo indipendente e logico.
• Combinare dati da più tabelle: più CTE possono essere usate per combinare dati da tabelle diverse, rendendo il passaggio finale di combinazione più strutturato. Questo approccio semplifica join complessi e garantisce che i dati di origine siano organizzati logicamente per una migliore leggibilità.

Tecniche avanzate per le CTE in SQL

Le CTE supportano tecniche SQL avanzate, rendendole versatili e utili per casi d'uso diversi. Di seguito alcune applicazioni avanzate delle CTE.

Più CTE in un'unica query

Puoi definire più CTE in una singola query, consentendo trasformazioni e calcoli complessi. Questo metodo è utile quando un problema richiede più fasi di elaborazione dei dati, dove ogni CTE rappresenta una fase distinta.

Supponiamo di avere i dati di vendita in una tabella chiamata Sales e di voler calcolare le vendite totali per ogni prodotto, identificare i prodotti con vendite totali superiori alla media e classificare questi prodotti in base alle vendite totali.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
), 
AverageSales AS (
    -- Step 2: Calculate the average of those totals
    -- Note: We can reference the previous CTE (ProductSales) here
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
), 
HighSalesProducts AS (
    -- Step 3: Filter products above the average
    SELECT ps.ProductID, ps.TotalSales
    FROM ProductSales ps
    CROSS JOIN AverageSales av
    WHERE ps.TotalSales > av.AverageTotalSales
)
-- Step 4: Rank the results
SELECT 
    ProductID, 
    TotalSales, 
    RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Nell'esempio sopra:

• La prima CTE (ProductSales) calcola le vendite totali per prodotto.
• La seconda CTE (AverageSales) calcola la media delle vendite totali su tutti i prodotti.
• La terza CTE (HighSalesProducts) filtra i prodotti le cui vendite totali superano la media.
• La query finale classifica questi prodotti in base alle vendite totali.

CTE nelle istruzioni UPDATE, DELETE e MERGE

Inserite nelle operazioni UPDATE, DELETE e MERGE, le CTE possono semplificare i compiti di manipolazione dei dati, soprattutto con filtri complessi o dati gerarchici.

Uso di CTE con un'istruzione UPDATE

Supponiamo di avere una tabella Employees con una colonna EmployeeSalary. Vogliamo dare un aumento del 10% a tutti i dipendenti che lavorano in azienda da oltre 5 anni.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    -- Standard SQL: Compare HireDate to 5 years before today
    WHERE HireDate <= CURRENT_DATE - INTERVAL '5' YEAR
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.10
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

La CTE LongTermEmployees identifica i dipendenti che lavorano da più di cinque anni. L'istruzione UPDATE usa questa CTE per aumentare selettivamente gli stipendi.

Uso di CTE con un'istruzione DELETE

Ora, supponiamo di avere una tabella chiamata Products e di voler eliminare tutti i prodotti che non sono stati venduti negli ultimi 2 anni. Possiamo usare una CTE per filtrare i prodotti:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Standard SQL: Filter for dates older than 2 years ago
    WHERE LastSoldDate < CURRENT_DATE - INTERVAL '2' YEAR
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

La CTE OldProducts identifica i prodotti che non sono stati venduti negli ultimi due anni e poi l'istruzione DELETE usa questa CTE per rimuoverli.

Uso di CTE con un'istruzione MERGE

L'istruzione MERGE in SQL consente aggiornamenti, inserimenti o eliminazioni condizionali in una tabella di destinazione in base ai dati in una tabella di origine. Nell'esempio seguente, la CTE MergedInventory combina dati di inventario nuovi ed esistenti. L'istruzione MERGE aggiorna quindi le quantità per i prodotti esistenti o inserisce nuovi prodotti in base ai dati della CTE.

-- CTE to prepare the source data for the merge
WITH MergedInventory AS (
    SELECT 
        ni.ProductID, 
        ni.Quantity AS NewQuantity
    FROM NewInventoryData ni
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS target
USING MergedInventory AS source
    ON target.ProductID = source.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) 
    VALUES (source.ProductID, source.NewQuantity);

Common Table Expression (CTE) ricorsive

Le CTE ricorsive sono un tipo speciale di CTE che fanno riferimento a se stesse nella propria definizione, permettendo alla query di eseguire operazioni ripetute. Questo le rende ideali per lavorare con dati gerarchici come gli organigrammi.

Introduzione alle CTE ricorsive

Le CTE ricorsive sono un tipo speciale di CTE che fa riferimento a se stessa nella definizione, consentendo alla query di eseguire operazioni ripetute. Questo le rende ideali per lavorare con dati gerarchici o a struttura ad albero, come organigrammi, strutture di directory o distinte base di prodotto. La CTE ricorsiva elabora i dati in modo iterativo, restituendo i risultati passo dopo passo finché il membro ricorsivo non restituisce nuove righe (condizione di terminazione).

Membri ancora e ricorsivi

Una CTE ricorsiva è composta da due parti principali:

• Anchor (ancora): la parte che definisce la query di base da cui parte la ricorsione.
• Membro ricorsivo: la parte che fa riferimento alla CTE stessa, permettendo di eseguire le operazioni "ricorsive".

Supponiamo di avere una tabella Employees, in cui ogni riga contiene un EmployeeID, un EmployeeName e un ManagerID. Se vogliamo trovare tutti i riporti diretti e indiretti per un manager specifico, partiamo dal membro ancora che identifica il manager di livello più alto.

Nota: nello Standard SQL (PostgreSQL, MySQL, SQLite) devi usare la parola chiave RECURSIVE.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    
    UNION ALL
    
    -- Recursive member: find employees who report to the current managers
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
-- Select the final result from the CTE
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Come funziona:

1. Ancora: la query esegue prima il membro ancora, trovando il dipendente con ID 1.
2. Ricorsione: il membro ricorsivo viene eseguito cercando i dipendenti il cui ManagerID corrisponde all'EmployeeID trovato nel passaggio precedente.
3. Ciclo: questo processo si ripete (il Livello 1 trova il Livello 2, il Livello 2 trova il Livello 3) finché non si trovano nuovi dipendenti.

Potenziali problemi o limitazioni delle CTE in SQL

Capire caratteristiche e limiti delle CTE è importante per scrivere query logiche e leggibili. Vediamo alcune limitazioni e potenziali problemi nell'uso delle CTE in diversi database.

Limitazioni di SQL Server e Azure

Esistono alcune limitazioni specifiche dell'ambiente per le CTE quando si lavora con SQL Server o Azure Synapse Analytics. Includono quanto segue:

• SQL Server: Il livello massimo di ricorsione predefinito per le CTE ricorsive è 100. Se questo limite viene superato senza modifica, si verifica un errore. Le definizioni di CTE non possono essere annidate direttamente all'interno di un'altra definizione di CTE (anche se puoi concatenare più CTE in sequenza).
• Azure Synapse Analytics: Il supporto varia in base al tipo di pool specifico. Le CTE ricorsive attualmente non sono supportate nei Dedicated SQL Pools (precedentemente SQL DW). Tuttavia, sono supportate nei Serverless SQL Pools. Inoltre, alcune operazioni DML (come UPDATE o DELETE con CTE) possono avere restrizioni sintattiche rispetto a SQL Server standard.

Se ti capita di lavorare con SQL Server, sappi che DataCamp offre molte risorse utili. Per iniziare, ti consiglio il corso Introduction to SQL Server di DataCamp per padroneggiare le basi di SQL Server per l'analisi dei dati. Puoi provare il nostro percorso professionale SQL Server Developer, che copre tutto, dalle transazioni e la gestione degli errori all'analisi delle serie temporali. Il nostro corso Hierarchical and Recursive Queries in SQL Server va dritto al cuore di come scrivere query avanzate in SQL Server, inclusi i metodi che coinvolgono le CTE.

Altri potenziali problemi

Sebbene le CTE siano utili per semplificare le query complesse, ci sono alcune insidie comuni di cui dovresti essere consapevole. Includono quanto segue:

• Loop infiniti nelle CTE ricorsive: se la condizione di terminazione per una CTE ricorsiva non è soddisfatta, si può generare un loop infinito, facendo sì che la query venga eseguita all'infinito. Per evitare che la CTE ricorsiva giri all'infinito, usa l'hint OPTION (MAXRECURSION N) per limitare il numero massimo di iterazioni ricorsive, dove N è un limite specificato.

  • Come risolvere: in SQL Server, usa l'hint OPTION (MAXRECURSION N) per limitare il numero massimo di iterazioni ricorsive. In PostgreSQL, puoi usare la clausola CYCLE per rilevare automaticamente i loop.

• Considerazioni sulle prestazioni: le CTE ricorsive possono diventare onerose in termini di risorse se la profondità della ricorsione è elevata o se si elaborano grandi dataset. Per ottimizzarne le prestazioni, limita i dati elaborati a ogni iterazione e applica filtri appropriati per evitare livelli di ricorsione eccessivi.

Prestazioni: CTE vs sottoquery

Un mito comune è che le CTE siano intrinsecamente più veloci delle sottoquery. In realtà, la maggior parte degli ottimizzatori di query moderni (come quelli di SQL Server e PostgreSQL) "inlinerizzano" le CTE standard, cioè le elaborano esattamente come sottoquery senza differenze di prestazioni.

Tuttavia, le CTE possono offrire un vantaggio prestazionale tramite la materializzazione, in cui il database calcola il risultato della CTE una volta e lo mette in cache per più riferimenti all'interno della query principale.

Ti consiglio di usare le CTE principalmente per la leggibilità. I miglioramenti prestazionali dipendono dal contesto e da come il tuo database gestisce la cache.

Quando usare le CTE rispetto ad altre tecniche

Sebbene le CTE siano indicate per semplificare le query che coinvolgono operazioni ripetute, anche tabelle derivate, viste e tabelle temporanee hanno scopi simili. La seguente tabella evidenzia vantaggi e svantaggi di ciascun metodo e quando usarli.

Conclusione

Padroneggiare le CTE richiede pratica, come tutto: ti consiglio di provare il percorso professionale Associate Data Analyst in SQL di DataCamp per diventare un data analyst competente. Il corso Reporting in SQL ti aiuterà anche a diventare abile nel costruire report e dashboard complessi per una presentazione efficace dei dati. Infine, dovresti ottenere la SQL Associate Certification per dimostrare la tua padronanza nell'uso di SQL per risolvere problemi di business e distinguerti dagli altri professionisti.