cte e subquery

cte base

with totals as (
  select user_id, sum(amount) as total
  from orders
  group by user_id
)
select * from totals;

cte multipli

with
  orders_2026 as (
    select * from orders where year = 2026
  ),
  totals as (
    select user_id, sum(amount) total from orders_2026 group by user_id
  )
select * from totals;

cte ricorsive

with recursive tree as (
  select id, parent_id, name from nodes where parent_id is null
  union all
  select n.id, n.parent_id, n.name
  from nodes n
  join tree t on n.parent_id = t.id
)
select * from tree;

subquery correlate

select o.*
from orders o
where o.amount > (
  select avg(amount) from orders where user_id = o.user_id
);

anti join

select u.id
from users u
left join orders o on u.id = o.user_id
where o.user_id is null;

utile per trovare utenti senza ordini.

checklist finale

• usa cte per leggibilità e riuso.
• valuta l'impatto su performance con explain.
• limita cte ricorsive a dataset controllati.
• documenta query complesse nel codice.

panoramica

In questo capitolo su cte e subquery, scrivi query modulari e leggibili con cte e subquery. L'obiettivo è trasformare i concetti in micro-pattern riutilizzabili con esempi piccoli e verificabili.

Lavora in sequenza: leggi, prova, modifica gli snippet e annota i trade-off principali (performance, leggibilità, manutenzione).

obiettivi

• capire e applicare cte base in uno scenario reale
• capire e applicare ct e multipli in uno scenario reale
• capire e applicare cte ricorsive in uno scenario reale
• capire e applicare subquery correlate in uno scenario reale

scheda rapida

SELECT categoria, COUNT(*) AS tot
FROM prodotti
WHERE attivo = TRUE
GROUP BY categoria
ORDER BY tot DESC
LIMIT 5;

Adatta questo scheletro agli esempi della lezione e sostituisci i dati con il tuo dominio.

tips

• usa CTE per spezzare query lunghe
• nomina le CTE in modo esplicito
• valuta EXISTS al posto di IN su liste grandi
• attenzione alle subquery correlate: possono essere costose

mini progetto

Refactor di una query complessa usando CTE e subquery per un report clienti.

• definisci una CTE con il dataset base
• aggiungi una CTE con aggregati e KPI
• usa una subquery per filtrare dinamicamente
• verifica che i risultati combacino

output atteso: uno script o query ripetibile con risultati verificabili.