CTE e viste: scrivere query SQL lunghe che restano leggibili

Le query SQL tendono a crescere. Si parte da un semplice SELECT, poi si aggiunge una JOIN, una sottoquery per escludere qualche caso, un'altra per calcolare una media di confronto. Dopo qualche mese la query occupa due schermate, ha sottoquery annidate su quattro livelli e nessuno, compreso chi l'ha scritta, la modifica volentieri. SQL offre strumenti precisi per evitare questa deriva: le espressioni di tabella comuni e le viste.

Le espressioni di tabella comuni (CTE)

Una CTE è una query a cui si dà un nome all'inizio dell'istruzione, con la clausola WITH, e che poi si usa come se fosse una tabella. Esiste solo per la durata di quella singola query.

Prendiamo una domanda concreta: quali clienti hanno speso nel 2025 più del doppio della spesa media dei clienti della stessa regione?

Scritta con sottoquery annidate:

SELECT x.id_cliente, x.speso
FROM (SELECT o.id_cliente, c.regione, SUM(o.totale) AS speso
      FROM ordini o JOIN clienti c ON c.id = o.id_cliente
      WHERE o.data_ordine >= '2025-01-01' AND o.data_ordine < '2026-01-01'
      GROUP BY o.id_cliente, c.regione) x
JOIN (SELECT regione, AVG(speso) AS media
      FROM (SELECT o.id_cliente, c.regione, SUM(o.totale) AS speso
            FROM ordini o JOIN clienti c ON c.id = o.id_cliente
            WHERE o.data_ordine >= '2025-01-01' AND o.data_ordine < '2026-01-01'
            GROUP BY o.id_cliente, c.regione) y
      GROUP BY regione) m ON m.regione = x.regione
WHERE x.speso > 2 * m.media;

La stessa logica di calcolo della spesa compare due volte, e per capirla bisogna leggere dall'interno verso l'esterno. Con le CTE:

WITH spesa_2025 AS (
  SELECT o.id_cliente, c.regione, SUM(o.totale) AS speso
  FROM ordini o
  JOIN clienti c ON c.id = o.id_cliente
  WHERE o.data_ordine >= '2025-01-01' AND o.data_ordine < '2026-01-01'
  GROUP BY o.id_cliente, c.regione
),
media_regione AS (
  SELECT regione, AVG(speso) AS media
  FROM spesa_2025
  GROUP BY regione
)
SELECT s.id_cliente, s.regione, s.speso, m.media
FROM spesa_2025 s
JOIN media_regione m ON m.regione = s.regione
WHERE s.speso > 2 * m.media;

La query si legge dall'alto verso il basso, come una sequenza di passi con un nome. La spesa per cliente è calcolata una sola volta e riutilizzata. Se in futuro la definizione di «spesa» cambia, per esempio per escludere gli ordini annullati, si modifica in un solo punto.

Verificare passo per passo

Un vantaggio pratico enorme delle CTE è la possibilità di controllare i passaggi intermedi. Per verificare che spesa_2025 sia corretta basta sostituire temporaneamente la query finale con SELECT * FROM spesa_2025 e guardare il risultato, magari confrontando il totale con un valore noto. Con le sottoquery annidate, isolare un passaggio richiede di smontare la query.

Prestazioni

Per molto tempo, in alcuni database, le CTE erano sempre calcolate per intero e salvate in un risultato temporaneo, il che impediva certe ottimizzazioni. PostgreSQL, per esempio, ha cambiato questo comportamento nella versione 12: oggi una CTE usata una sola volta viene di norma integrata nella query principale e ottimizzata insieme a essa. In caso di dubbio, il piano di esecuzione mostra che cosa succede davvero.

Le CTE ricorsive

Una variante speciale, WITH RECURSIVE, permette a una CTE di fare riferimento a se stessa. È lo strumento per percorrere gerarchie di profondità sconosciuta, come un organigramma:

WITH RECURSIVE sottoposti AS (
  SELECT id, nome, id_responsabile, 1 AS livello
  FROM dipendenti
  WHERE id = 10
  UNION ALL
  SELECT d.id, d.nome, d.id_responsabile, s.livello + 1
  FROM dipendenti d
  JOIN sottoposti s ON d.id_responsabile = s.id
)
SELECT * FROM sottoposti;

La prima parte è il punto di partenza; la seconda aggiunge a ogni giro i dipendenti che riportano a quelli già trovati, finché non ce ne sono più. Se i dati contengono un ciclo per errore, la ricorsione non termina: conviene aggiungere un limite sul livello.

Le viste

Una vista è una query salvata nel database con un nome. Si interroga come una tabella, ma ogni volta esegue la query sottostante sui dati attuali:

CREATE VIEW ordini_validi AS
SELECT *
FROM ordini
WHERE stato <> 'annullato' AND test = false;

Le viste servono soprattutto a condividere definizioni. Se tutti i report devono escludere gli ordini annullati e quelli di prova, scrivere la regola in una vista evita che ogni analista la reinterpreti a modo suo. Sono anche uno strumento di sicurezza: si può dare a un utente il permesso di leggere una vista che mostra solo alcune colonne, senza dargli accesso alla tabella completa.

Le viste materializzate

Una vista materializzata salva fisicamente il risultato della query, come una tabella. Leggerla è veloce, ma i dati sono fermi all'ultimo aggiornamento, che va richiesto esplicitamente o pianificato:

CREATE MATERIALIZED VIEW fatturato_mensile AS
SELECT date_trunc('month', data_ordine) AS mese, SUM(totale) AS fatturato
FROM ordini_validi
GROUP BY 1;

REFRESH MATERIALIZED VIEW fatturato_mensile;

L'esempio usa la sintassi di PostgreSQL; altri database offrono funzionalità simili con nomi diversi. Sono ideali per cruscotti consultati spesso su dati che possono essere aggiornati ogni ora o ogni notte.

Quale strumento usare

In sintesi

• Le CTE danno un nome ai passaggi di una query e la rendono leggibile dall'alto in basso.
• Ogni passaggio di una CTE si può verificare isolatamente.
• WITH RECURSIVE percorre gerarchie di profondità sconosciuta.
• Le viste condividono definizioni comuni e limitano l'accesso ai dati.
• Le viste materializzate velocizzano i riepiloghi pesanti, al prezzo di dati non in tempo reale.