Espressioni di tabella comuni (WITH)
Ristrutturi le query annidate in clausole WITH leggibili, concateni CTE e impari le regole di materializzazione di PostgreSQL 12+
Espressioni di tabella comuni (WITH) è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.
Che cos'è una CTE?
Una Common Table Expression (CTE) è una sottoquery con nome definita con WITH:
WITH paid_orders AS (
SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;Perché utilizzare le CTE?
Tre vantaggi principali:
- Leggibilità — suddividere una query di 200 righe in passaggi con nome
- Riutilizzo — fare riferimento più volte allo stesso risultato intermedio
- Ricorsione — solo le CTE supportano le query ricorsive (nella prossima lezione)
Concatenare le CTE
Definisca più CTE in un unico WITH e ne utilizzi una nella successiva:
WITH last_30 AS (
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;Riutilizzare una CTE
Se lo stesso risultato intermedio viene utilizzato due volte, una CTE rende evidente l'intento:
WITH recent_users AS (
SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders' AS metric, COUNT(*) FROM orders
WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
WHERE user_id IN (SELECT id FROM recent_users);Materializzazione delle CTE (PostgreSQL ≤ 11)
Le versioni precedenti di PG materializzavano sempre l'output delle CTE, creando una barriera per il planner. Da PG 12 il planner incorpora le CTE per impostazione predefinita, salvo diversa indicazione:
-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...
-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...CTE che modificano i dati
Le CTE possono utilizzare INSERT/UPDATE/DELETE: sono utili per spostare atomicamente le righe tra tabelle:
WITH moved AS (
DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;CTE nelle operazioni DML
RETURNING + WITH per «trovare e agire»:
WITH cancelled AS (
UPDATE orders SET status = 'cancelled'
WHERE created_at < NOW() - INTERVAL '14 days'
AND status = 'pending'
RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;Ordine di esecuzione
Le CTE che modificano i dati vengono eseguite indipendentemente nella stessa istantanea. Ogni istruzione vede lo stato PRIMA dell'inizio di tutte le modifiche: sorprendente, ma prevedibile.
CTE, sottoquery e viste a confronto
Confronto:
- Sottoquery — definita inline; utilizzata una sola volta
- CTE — con nome; utilizzata più volte nella query; scompare al termine della query
- Vista — con nome; persistente; riutilizzabile in più query
Non abusi delle CTE
Racchiudere tutto in una CTE rende le query più leggibili, ma può nasconderne il costo. Con risultati intermedi molto grandi, il planner potrebbe scegliere piani peggiori rispetto a quelli di un JOIN equivalente.
Riepilogo
Le CTE assegnano un nome alle query intermedie.
- Migliorano la leggibilità
- Consentono il riutilizzo nella stessa query
- Sono necessarie per la ricorsione
- Vengono incorporate per impostazione predefinita nelle versioni moderne di PG
Verifica rapida
Quale parola chiave avvia una Common Table Expression?
Domande Frequenti
La lezione «Espressioni di tabella comuni (WITH)» è gratuita?
Sì — il testo completo di «Espressioni di tabella comuni (WITH)» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.
Cosa imparerò in «Espressioni di tabella comuni (WITH)»?
Ristrutturi le query annidate in clausole WITH leggibili, concateni CTE e impari le regole di materializzazione di PostgreSQL 12+ Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Academy?
Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.
Quanto tempo richiede la lezione «Espressioni di tabella comuni (WITH)»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Academy?
Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Sottoquery scalari, di riga e di tabella
- Sottoquery correlate e non correlate
- Espressioni di tabella comuni (WITH)
- CTE ricorsive per le gerarchie