CTE: query in più passi con WITH

Le query reali tendono a crescere: tre JOIN, un GROUP BY, una sotto-query, un'altra sotto-query… diventano illeggibili. Le CTE ("Common Table Expressions", clausola WITH) ti permettono di dare un nome a uno step intermedio e usarlo come se fosse una tabella nella query principale.

Sintassi base

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

Una CTE è valida solo per la query che la segue: non sopravvive all'esecuzione.

Esempio

Trovare i 3 clienti che hanno speso di più (somma su ordini consegnati):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Letta in italiano: "prima calcola la spesa per cliente; poi unisci con clienti e prendi i top 3". Senza CTE servirebbe una sotto-query inline nella FROM (legale, ma più rumorosa).

Più CTE in cascata

Puoi definire più CTE separate da virgole, e ognuna può riferire le precedenti:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average perché average ha sempre una sola riga: il prodotto cartesiano si limita ad aggiungere total_average a ogni riga del risultato.

CTE vs sotto-query: quando usare quali

Le CTE brillano quando:

• la stessa sotto-query è usata due o più volte nella query principale;
• vuoi rendere esplicita la struttura in passi nominati (autodocumentante);
• è il prerequisito per le CTE ricorsive (WITH RECURSIVE, prossima lezione del modulo).

Una sotto-query inline è più concisa per uno step usato una sola volta.

Prova tu

Esercizio di ripasso