Clausola SQL WITH: Common Table Expressions (CTE) e query ricorsive
Published: 2026-02-04
La clausola WITH (chiamata anche Common Table Expression o CTE) ti permette di definire insiemi di risultati temporanei e nominati che puoi referenziare in una query. Questo rende le query complesse più leggibili e riutilizzabili. È disponibile nella maggior parte dei database SQL moderni, inclusi SQLite, PostgreSQL, MySQL 8.0+, SQL Server e Oracle.
Dati di esempio
Usiamo due tabelle in tutti gli esempi:
Tabella employees:
| id | name | manager_id | salary |
|---|---|---|---|
| 1 | Alice | NULL | 120000 |
| 2 | Bob | 1 | 80000 |
| 3 | Carol | 1 | 85000 |
| 4 | Dave | 2 | 60000 |
| 5 | Eve | 2 | 62000 |
| 6 | Frank | 3 | 58000 |
Tabella sales:
| id | employee_id | amount | month |
|---|---|---|---|
| 1 | 2 | 5000 | January |
| 2 | 2 | 6000 | February |
| 3 | 3 | 7000 | January |
| 4 | 3 | 8000 | February |
| 5 | 4 | 3000 | January |
| 6 | 5 | 4000 | January |
| 7 | 6 | 2500 | February |
Clausola WITH di base (non ricorsiva)
Una clausola WITH definisce una o più tabelle temporanee nominate che esistono solo per la durata della query.
Esempio: Vendite totali per dipendente
La CTE employee_sales calcola le vendite totali per dipendente, poi la uniamo alla tabella employees per mostrare i nomi.
CTE multiple
Puoi definire più CTE separate da virgole. Le CTE successive possono fare riferimento a quelle precedenti.
Esempio: Analisi delle vendite con metriche multiple
WITH RECURSIVE: attraversare gerarchie
WITH RECURSIVE permette a una CTE di fare riferimento a se stessa, consentendoti di attraversare dati gerarchici o ad albero. Questo è utile per organigrammi, gerarchie di categorie o qualsiasi relazione padre-figlio.
Una CTE ricorsiva ha due parti:
- Query base: L’insieme iniziale di righe
- Query ricorsiva: Fa riferimento alla CTE stessa per trovare righe correlate
Esempio: Organigramma (tutti i dipendenti sotto un manager)
Trova tutti i dipendenti sotto Alice (la CEO):
Esempio: Catena di comando (trovare la gerarchia dei manager)
Trova l’intera catena di comando per Dave (che riporta a Bob, che riporta ad Alice):
Sintassi WITH RECURSIVE nei vari database
WITH RECURSIVE è supportato in:
- SQLite — 3.8.3+ (2013)
- PostgreSQL — 8.4+ (2009)
- SQL Server — 2005+ (chiamato “Recursive CTE”)
- Oracle — 11g+ (chiamato “Recursive Subquery Factoring”)
- MySQL — 8.0+ (2018)
Nota: Le versioni più vecchie di MySQL e alcuni altri database non supportano le CTE ricorsive: potrebbero richiedere approcci diversi come stored procedure o ricorsione a livello applicativo.
Casi d’uso comuni per le clausole WITH
- Semplificare query complesse — Suddividere una query grande in passaggi logici
- Riutilizzare sottoquery — Definire un calcolo una volta e usarlo più volte
- Attraversamento gerarchico — Navigare alberi (organigrammi, categorie, commenti)
- Generazione di sequenze — Creare intervalli di numeri o intervalli di date
- Aggregazioni progressive — Costruire i risultati a passi successivi
Riepilogo
WITH(CTE) crea un insieme di risultati temporaneo e nominato da usare in una query- Più CTE possono essere concatenate
WITH RECURSIVEpermette a una CTE di fare riferimento a se stessa per query gerarchiche- Le CTE ricorsive richiedono un caso base e un caso ricorsivo separati da
UNION ALL - Supportato in tutti i database SQL moderni (anche se la sintassi può variare leggermente)
Prova a modificare gli esempi sopra per esplorare gerarchie o calcoli diversi!