Le CTE e la clausola WITH in SQL

Nelle lezioni precedenti abbiamo visto come utilizzare le subquery per inserire una query all'interno di un'altra. Le subquery permettono di risolvere molti problemi, ma quando una query diventa particolarmente complessa possono essere difficili da leggere e da gestire.

Le CTE, acronimo di Common Table Expression, offrono un modo più ordinato per definire risultati intermedi e utilizzarli successivamente nella query principale.

Una CTE può essere vista, semplificando, come una tabella temporanea definita all'interno della query, alla quale possiamo assegnare un nome e fare riferimento nelle istruzioni successive.

Creare una CTE con WITH

Una CTE viene definita utilizzando la clausola WITH.

La sintassi è: WITH nome AS (query). La query contenuta tra parentesi viene eseguita per creare il risultato della CTE. Successivamente, la query principale può utilizzare la CTE come se fosse una tabella.

Ad esempio, in un database di un negozio possiamo calcolare il numero di prodotti presenti in ogni categoria:

WITH prodotti_categoria AS (
    SELECT categoria, COUNT(*) AS numero_prodotti
    FROM prodotti
    GROUP BY categoria
)
SELECT categoria, numero_prodotti
FROM prodotti_categoria;

La CTE si chiama prodotti_categoria e contiene il risultato della prima query di selezione con SELECT.

Perché utilizzare una CTE

Uno dei principali vantaggi delle CTE è la leggibilità. Consideriamo una query che utilizza una subquery in FROM:

SELECT categoria, numero_prodotti
FROM (
    SELECT categoria, COUNT(*) AS numero_prodotti
    FROM prodotti
    GROUP BY categoria
) AS prodotti_categoria
WHERE numero_prodotti >= 10;

Possiamo ottenere lo stesso risultato utilizzando una CTE:

WITH prodotti_categoria AS (
    SELECT categoria, COUNT(*) AS numero_prodotti
    FROM prodotti
    GROUP BY categoria
)
SELECT categoria, numero_prodotti
FROM prodotti_categoria
WHERE numero_prodotti >= 10;

La seconda query nell'esempio separa più chiaramente i due passaggi. Questo può rendere le query complesse più semplici da comprendere e modificare.

Più CTE nella stessa query

Possiamo definire più CTE all'interno dello stesso WITH, semplicemente separandole con la virgola.

Ad esempio:

WITH
clienti_attivi AS (
    SELECT *
    FROM clienti
    WHERE attivo = 1
),
ordini_recenti AS (
    SELECT *
    FROM ordini
    WHERE data_ordine >= '2026-01-01'
)
SELECT *
FROM clienti_attivi AS c
INNER JOIN ordini_recenti AS o
    ON c.id = o.cliente_id;

In questo esempio abbiamo creato due CTE: clienti_attivi e ordini_recenti. La query principale può poi utilizzare entrambe. 

Nota: puoi approfondire JOIN nella lezione sull'integrità referenziale.

Una CTE può inoltre fare riferimento a una CTE definita precedentemente nello stesso WITH. Ad esempio:

WITH
prodotti_categoria AS (
    SELECT categoria, COUNT(*) AS numero_prodotti
    FROM prodotti
    GROUP BY categoria
),
categorie_grandi AS (
    SELECT categoria
    FROM prodotti_categoria
    WHERE numero_prodotti >= 10
)
SELECT * FROM categorie_grandi;

In questo caso categorie_grandi utilizza il risultato di prodotti_categoria.

Le CTE ricorsive

Le CTE possono essere utilizzate anche per risolvere problemi che richiedono una ricorsione. Per questo scopo SQL mette a disposizione WITH RECURSIVE.

Una CTE ricorsiva è una CTE che fa riferimento a se stessa ed è composta generalmente da due parti:

Le due parti vengono generalmente unite con UNION ALL, che abbiamo visto tra gli operatori di insieme.

Un esempio semplice consiste nel generare una sequenza di numeri. Per esempio, possiamo generare i numeri da 1 a 5:

WITH RECURSIVE numeri AS (
    SELECT 1 AS numero

    UNION ALL

    SELECT numero + 1
    FROM numeri
    WHERE numero < 5
)
SELECT numero FROM numeri;

Nell'esempio, SELECT 1 AS numero genera il valore iniziale, mentre la parte ricorsiva della query aggiunge 1.