Una CTE (Common Table Expression), definita con WITH, è un insieme di risultati temporaneo denominato che puoi referenziare all'interno di una query. Le CTE rendono le query complesse più leggibili suddividendole in passaggi denominati e logici — e consentono query ricorsive (per gerarchie).
active_users (
id, name users active
)
active_users
name ;
Il WITH ... AS (...) definisce una CTE — una query denominata che referenzi successivamente. È come una subquery, ma denominata e leggibile, soprattutto per query complesse.
WITH
monthly_sales AS (
SELECT month, SUM(amount) AS total FROM orders GROUP BY month
),
avg_sales AS (
SELECT AVG(total) AS avg_total FROM monthly_sales -- a CTE can reference another
)
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total; -- months above the average
CTE multiple suddividono una query complessa in passaggi leggibili e sequenziali — ognuno costruito su quello precedente. Questo è molto più chiaro di subquery profondamente annidate.
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1 -- base case (the top)
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- recursive step
)
SELECT * FROM subordinates; -- the whole tree under employee 1
CTE ricorsive (WITH RECURSIVE) attraversano dati gerarchici/ad albero (organigrammi, alberi di categorie, distinte base, percorsi in grafi) — qualcosa altrimenti molto difficile in SQL.
Le CTE sono una funzionalità SQL importante per leggibilità e ricorsione, e comprenderle è prezioso per scrivere query complesse mantenibili e per gestire dati gerarchici.
Il vantaggio quotidiano primario è la leggibilità: le query complesse con più livelli di subquery diventano difficili da leggere e mantenere, mentre le CTE ti consentono di suddividere la query in passaggi logici sequenziali denominati (leggere da cima a fondo come una serie di operazioni), migliorando drammaticamente la chiarezza — per questo motivo, le CTE sono spesso preferite alle subquery profondamente annidate per query complesse, rendendole uno strumento importante per scrivere SQL mantenibile.
Equalmente importante è la capacità CTE ricorsiva (WITH RECURSIVE), che abilita l'interrogazione di dati gerarchici e strutturati ad albero (organigrammi, alberi di categorie, distinte base, attraversamento di grafi) — una classe di query che altrimenti è molto difficile o impossibile in SQL standard, rendendo le CTE ricorsive la soluzione standard per l'attraversamento di dati gerarchici.
Comprendere entrambi gli usi — CTE per scomporre query complesse in passaggi leggibili, e CTE ricorsive per l'attraversamento gerarchico — copre il valore pratico.
Poiché le query complesse sono comuni nelle applicazioni reali (e la leggibilità/mantenibilità contano), e poiché i dati gerarchici (che le CTE ricorsive gestiscono particolarmente bene) appaiono frequentemente (strutture organizzative, categorie annidate, dati a thread), comprendere le CTE è una conoscenza preziosa di livello senior che migliora la mantenibilità delle query e sblocca l'interrogazione gerarchica — uno strumento frequentemente rilevante per scrivere SQL pulito e sofisticato e un argomento comune che dimostra capacità SQL avanzata, in particolare la forma ricorsiva per dati strutturati ad albero.
Una raccolta di domande di colloquio IT con risposte dettagliate — da Junior a Senior.
Dona