Come funzionano le CTE e le query ricorsive in PostgreSQL?

Question

Accepted Answer

Una **CTE** (Common Table Expression), definita con **`WITH`**, è un insieme di risultati temporaneo denominato che rende le query complesse più leggibili. PostgreSQL supporta completamente le CTE, incluse le CTE **ricorsive** (`WITH RECURSIVE`) per attraversare dati gerarchici/ad albero.

## Basic CTEs — costruzione di query leggibile

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

Le CTE suddividono una query complessa in passaggi denominati e leggibili (concatenati, ognuno riferendosi ai precedenti) — molto più chiaro rispetto alle subquery annidate.

## CTE ricorsive — attraversare gerarchie

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Una **CTE ricorsiva** ha un caso base (le righe iniziali) e un passaggio ricorsivo (`UNION ALL` che si ricollega alla CTE stessa), iterando finché non ci sono nuove righe — perfetto per **alberi e gerarchie** (organigrammi, alberi di categorie, distinte base, percorsi in grafi).

## Una nota sulla materializzazione delle CTE (specifico di Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Prima di Postgres 12, le CTE erano sempre una "optimization fence" (materializzate); ora possono essere inline per prestazioni migliori — `MATERIALIZED`/`NOT MATERIALIZED` controlla questo comportamento.

## Perché è importante

Le CTE e le query ricorsive sono funzionalità PostgreSQL preziose per **leggibilità e dati gerarchici**, quindi comprenderle è utile per scrivere query complesse manutenibili e gestire dati strutturati ad albero.

Il vantaggio quotidiano è la **leggibilità**: le query complesse con più livelli di subquery diventano difficili da mantenere, mentre le CTE ti permettono di scomporle in passaggi logici sequenziali denominati (leggibili da cima a fondo), migliorando drammaticamente la chiarezza — rendendo le CTE uno strumento importante per scrivere SQL manutenibile.

La capacità distintiva è rappresentata dalle **CTE ricorsive** (`WITH RECURSIVE`), che consentono di interrogare **dati gerarchici e strutturati ad albero** (organigrammi, alberi di categorie, distinte base, commenti annidati, attraversamento di grafi) — una classe di query altrimenti molto difficile in SQL, rendendo le CTE ricorsive la soluzione standard per l'attraversamento gerarchico.

Comprendere la struttura della CTE ricorsiva (caso base + passaggio ricorsivo che si ricollega a se stesso) è una conoscenza fondamentale per questa esigenza comune.

Un dettaglio utile specifico di Postgres è il comportamento di **materializzazione delle CTE** (Postgres 12+ può inline le CTE per prestazioni migliori, controllabile con `MATERIALIZED`/`NOT MATERIALIZED`) — rilevante per l'ottimizzazione.

Poiché le query complesse sono comuni (e la leggibilità è importante per la manutenzione), e poiché i dati gerarchici (che le CTE ricorsive gestiscono in modo unico) appaiono frequentemente (strutture organizzative, categorie annidate), comprendere le CTE e le query ricorsive è una conoscenza PostgreSQL preziosa e praticamente rilevante che migliora la manutenibilità delle query e sblocca l'interrogazione gerarchica — una capacità frequentemente rilevante per SQL pulito e sofisticato e un argomento comune, soprattutto la forma ricorsiva per l'esigenza ricorrente di interrogare dati strutturati ad albero.