Subquery e CTE in SQL: Query Complesse Leggibili con WITH • Meteora Web Agency

Hai mai aperto una query SQL scritta da qualcun altro — o da te sei mesi fa — e hai passato venti minuti a decifrare cosa faceva? Subquery annidate dentro altre subquery, tre livelli di parentesi, JOIN sparsi. Funziona, ma è illeggibile. E quando una query è illeggibile, diventa impossibile da manutenere, da ottimizzare, da spiegare al collega.

Noi, di Meteora Web, lavoriamo ogni giorno con database di clienti reali: e-commerce con centinaia di migliaia di ordini, piattaforme SaaS con decine di tabelle collegate. La leggibilità del codice SQL non è un vezzo estetico. È una questione di ore di sviluppo sprecate e di bug che potevano essere evitati.

Qui ti mostriamo come usare le CTE (Common Table Expressions) con la clausola WITH per trasformare query intricate in blocchi logici chiari. Imparerai anche quando una subquery tradizionale è la scelta giusta e quando invece la CTE fa la differenza.

Subquery: lo strumento base, ma con limiti

La subquery è una query annidata dentro un'altra query. Si usa nel WHERE, nel FROM, nel SELECT o nel HAVING. È potente, ma ha due difetti grossi: leggibilità e riutilizzo.

Subquery nel WHERE

Un caso classico: trovare i clienti che hanno fatto almeno un ordine sopra i 500 euro.

SELECT nome, email
FROM clienti
WHERE id IN (
    SELECT cliente_id
    FROM ordini
    WHERE totale > 500
);

Funziona, ma se la subquery diventa complessa (più JOIN, aggregazioni, filtri) il codice si appiattisce in una matrioska difficile da leggere.

Subquery nel FROM (derived table)

Quando devi pre-aggregare dati prima di un JOIN, usi una subquery nel FROM:

SELECT c.nome, rs.totale_ordini
FROM clienti c
JOIN (
    SELECT cliente_id, COUNT(*) AS totale_ordini
    FROM ordini
    WHERE data > '2026-01-01'
    GROUP BY cliente_id
) rs ON c.id = rs.cliente_id;

Ancora leggibile, ma se hai bisogno di usare la stessa aggregazione in più punti della query, la devi riscrivere. Ed è lì che le CTE brillano.

CTE con WITH: leggibilità e riutilizzo

Una Common Table Expression è una query temporanea definita all'inizio di una query più grande. Si scrive con WITH nome_cte AS (query) e poi la usi come una tabella virtuale.

Primo esempio: dalla subquery alla CTE

Riscriviamo l'esempio precedente con una CTE:

WITH ordini_riassunto AS (
    SELECT cliente_id, COUNT(*) AS totale_ordini
    FROM ordini
    WHERE data > '2026-01-01'
    GROUP BY cliente_id
)
SELECT c.nome, o.totale_ordini
FROM clienti c
JOIN ordini_riassunto o ON c.id = o.cliente_id;

Il vantaggio è subito evidente: la logica di aggregazione è isolata, ha un nome descrittivo e può essere riutilizzata più avanti.

Più CTE in cascata

Le CTE possono essere concatenate. Ogni CTE successiva può fare riferimento a quelle precedenti. Questo permette di scomporre una query complessa in passaggi lineari.

WITH
clienti_attivi AS (
    SELECT id, nome
    FROM clienti
    WHERE ultimo_acquisto > '2026-06-01'
),
ordini_cliente AS (
    SELECT c.id, COUNT(o.id) AS num_ordini, SUM(o.totale) AS fatturato
    FROM clienti_attivi c
    LEFT JOIN ordini o ON o.cliente_id = c.id
    GROUP BY c.id
)
SELECT c.nome, oc.num_ordini, oc.fatturato
FROM clienti_attivi c
JOIN ordini_cliente oc ON c.id = oc.id
WHERE oc.fatturato > 1000
ORDER BY oc.fatturato DESC;

Ogni blocco ha una responsabilità singola. Se c'è un errore, lo isoli in un passo. Se devi modificare la definizione di "cliente attivo", cambi una sola riga.

CTE ricorsive: quando hai dati gerarchici

Una CTE ricorsiva si richiama da sola. Serve per dati ad albero: categorie, organigrammi, commenti annidati.

Struttura di una CTE ricorsiva

Deve avere un ancoraggio (SELECT che prende il livello base) e un passo ricorsivo (che si unisce alla CTE stessa).

WITH RECURSIVE gerarchia AS (
    -- ancoraggio: radici
    SELECT id, nome, parent_id, 1 AS livello
    FROM categorie
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- passo ricorsivo: figli
    SELECT c.id, c.nome, c.parent_id, g.livello + 1
    FROM categorie c
    JOIN gerarchia g ON c.parent_id = g.id
)
SELECT * FROM gerarchia ORDER BY livello, nome;

Risultato: ogni riga sa a che livello di profondità si trova. Potente e pulito.

Performance: CTE non fa miracoli, ma aiuta

Le CTE non sono di per sé più veloci delle subquery. Il database le elabora come tabelle derivate temporanee. In alcuni DBMS (es. PostgreSQL) le CTE possono essere materializzate, in altri no. Ma il vero guadagno è umano: scrivere query manutenibili significa trovare i colli di bottiglia più in fretta.

Noi abbiamo visto team perdere giorni a decifrare subquery annidate per poi scoprire che bastava un indice. Con le CTE, la struttura della query è chiara e puoi concentrarti sul piano di esecuzione.

Quando preferire le subquery alle CTE

In una subquery scalare (che restituisce un singolo valore): va bene nel SELECT o nel WHERE, è semplice e non merita una CTE.
In query una tantum molto brevi: non serve introdurre una CTE per due righe.
Quando la subquery è usata una sola volta e non come parte di un calcolo intermedio complesso.

Errori comuni e come evitarli

Dimenticare il punto e virgola prima della CTE se ci sono statement precedenti. Le CTE devono essere il primo elemento della query.
Usare UNION invece di UNION ALL in CTE ricorsive: UNION rimuove i duplicati, rallenta e può causare loop infiniti in ricorsione. Usa sempre UNION ALL.
Omettere il limite di profondità in CTE ricorsive: se i dati sono ciclici, la ricorsione non termina. Imposta un limite esplicito con WHERE livello < 10 o usa la sintassi MAXRECURSION in SQL Server.
Non testare con dati reali: una CTE che funziona con 100 righe può esplodere con 100.000. Verifica il piano di esecuzione.

In sintesi — cosa fare adesso

Rivedi la query più complessa che hai scritto e prova a scomporla in CTE con nomi chiari.
Se hai dati gerarchici (categorie, albero di commenti), usa una CTE ricorsiva — salverai ore di codice procedurale.
Controlla il piano di esecuzione della tua nuova CTE: assicurati che gli indici siano sfruttati.
Condividi la tua query con un collega: se la capisce in un minuto, hai vinto.
Se lavori con PHP e backend, dai un'occhiata a come applichiamo lo stesso principio di leggibilità nel codice PHP con le proprietà readonly — leggi la guida.

Le CTE non sono una moda. Sono uno strumento che distingue chi scrive SQL di fortuna da chi costruisce query che durano. Noi le usiamo ogni giorno, e anche tu dovresti.