Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 11 / 40

Subquery vs CTE: quando usare cosa

Subquery inline, derived table, sottoquery correlate e CTE. La regola della leggibilità, il mito del 'è lo stesso piano?' e come rifattorizzare una query mostro.

Inizi a scrivere una query semplice in Runehold: “i 10 clienti migliori per lifetime value”. Poi il marketing chiede “solo i clienti in Germania”. Poi “e solo quelli con LTV sopra la media del loro paese”. Poi “mostrami sia il trimestre scorso sia quello attuale, fianco a fianco”. All’improvviso la tua query semplice è di 60 righe, annidata su tre livelli, e nessuno riesce a leggerla.

Questa lezione parla di come tenere quelle query leggibili. Vedremo le subquery (query dentro query), le derived table (subquery nella FROM), le sottoquery correlate (subquery che fanno riferimento alla riga esterna) e le CTE (Common Table Expression, dette anche clausole WITH) — lo strumento che ti permette di scomporre una query grande in passi nominati e leggibili.

I quattro tipi di subquery

1. Subquery scalare: restituisce un singolo valore

SELECT o.OrderId,
       o.Total,
       (SELECT AVG(Total) FROM Sales.Orders)  AS overall_average
FROM Sales.Orders AS o
WHERE o.Total > (SELECT AVG(Total) FROM Sales.Orders);

La query interna restituisce un singolo valore (una riga, una colonna). Puoi usarla ovunque andrebbe un valore letterale: in SELECT, in WHERE, in ORDER BY.

Trappola: se la subquery scalare restituisce più di una riga a runtime, ottieni un errore. SQL Server non sa quale valore usare. Scrivi le subquery scalari in modo che restituiscano dimostrabilmente una sola riga.

2. Subquery multi-riga con IN o EXISTS

-- Tutti gli ordini dei clienti in Italia
SELECT OrderId, Total
FROM Sales.Orders
WHERE CustomerId IN (SELECT CustomerId FROM Sales.Customer WHERE Country = 'IT');

-- Equivalente con EXISTS (spesso più chiaro per condizioni complesse)
SELECT o.OrderId, o.Total
FROM Sales.Orders AS o
WHERE EXISTS (
    SELECT 1 FROM Sales.Customer c
    WHERE c.CustomerId = o.CustomerId AND c.Country = 'IT'
);

IN va bene con una colonna semplice. EXISTS è meglio quando la subquery ha più condizioni o deve fare riferimento alla riga esterna. Entrambi producono piani quasi identici nel SQL Server moderno; scegli in base alla leggibilità.

3. Derived table: subquery nella FROM

-- AOV per paese, filtrato per i paesi con almeno 10 ordini
SELECT country, orders, aov
FROM (
    SELECT CountryCode AS country,
           COUNT(*)    AS orders,
           AVG(Total)  AS aov
    FROM Sales.Orders
    GROUP BY CountryCode
) AS country_stats
WHERE orders >= 10
ORDER BY aov DESC;

La subquery (SELECT ... GROUP BY CountryCode) è una derived table. Deve avere un alias (qui, country_stats), e la query esterna la tratta come qualsiasi altra tabella. Utile quando devi aggregare una volta e poi filtrare/fare join sul risultato aggregato.

4. Sottoquery correlata: fa riferimento alla riga esterna

-- Per ogni cliente, mostra la data dell'ultimo ordine
SELECT c.CustomerId,
       c.Name,
       (SELECT MAX(o.OrderDate)
        FROM Sales.Orders o
        WHERE o.CustomerId = c.CustomerId) AS last_order
FROM Sales.Customer AS c;

La query interna fa riferimento a c.CustomerId, che viene dalla query esterna. Concettualmente viene eseguita una volta per ogni riga esterna. Si chiama sottoquery correlata.

Le sottoquery correlate sembrano lente, ma di solito l’optimizer le riscrive in una join o in una semi-join. Comunque, preferisci le join esplicite quando possibile — esprimono l’intento più chiaramente.

Le CTE: la clausola WITH

Una CTE (Common Table Expression) è una subquery nominata che definisci all’inizio dello statement e a cui fai riferimento dopo:

WITH country_stats AS (
    SELECT CountryCode AS country,
           COUNT(*)    AS orders,
           AVG(Total)  AS aov,
           SUM(Total)  AS revenue
    FROM Sales.Orders
    GROUP BY CountryCode
),
top_customers AS (
    SELECT TOP (10) c.Country, c.Name, SUM(o.Total) AS ltv
    FROM Sales.Customer AS c
    JOIN Sales.Orders   AS o ON o.CustomerId = c.CustomerId
    GROUP BY c.Country, c.Name
    ORDER BY ltv DESC
)
SELECT cs.country, cs.revenue, cs.aov, tc.Name AS top_customer, tc.ltv
FROM country_stats AS cs
LEFT JOIN top_customers AS tc ON tc.Country = cs.country
ORDER BY cs.revenue DESC;

Confronta questa con la stessa logica scritta come subquery annidate — sarebbero 30 righe di annidamento, illeggibili dopo una settimana. Le CTE trasformano le query grandi in passi nominati, ognuno un pensiero autoconsistente.

Regole delle CTE:

  • Più CTE si separano con virgole.
  • Ogni CTE può fare riferimento alle CTE precedenti nello stesso WITH.
  • La CTE esiste solo per questo statement. Non è una view. Non è memorizzata.
  • Le CTE di solito vengono inlinedall’optimizer — nessuna penalità di performance rispetto a una derived table.

Quando usare una CTE:

  • Qualsiasi query che annidasse subquery a due o più livelli.
  • Qualsiasi query che vorresti commentare con “passo 1”, “passo 2”.
  • Le query ricorsive (vedi la prossima sezione).

Il mito “è lo stesso piano”

Sentirai dire “le CTE sono uguali alle derived table, stesso piano, stessa performance”. È quasi sempre vero in SQL Server: l’optimizer di solito inline una CTE non ricorsiva e la tratta come una derived table.

Non sempre. Una CTE referenziata più volte non viene materializzata; l’optimizer inline la definizione in ogni riferimento. Per CTE costose usate più volte, questo significa che l’operazione costosa viene eseguita ripetutamente. Se vedi quel pattern e la CTE è davvero costosa, la soluzione è scaricare i risultati della CTE in una tabella temporanea e fare join contro quella.

-- Se country_stats è molto costosa e vi facciamo riferimento due volte:
DROP TABLE IF EXISTS #country_stats;
SELECT CountryCode AS country,
       COUNT(*) AS orders,
       AVG(Total) AS aov
INTO #country_stats
FROM Sales.Orders
GROUP BY CountryCode;

-- Poi le query esterne usano #country_stats, calcolata una sola volta.
SELECT * FROM #country_stats WHERE orders > 100;
SELECT * FROM #country_stats WHERE aov < 50;

Le tabelle temporanee (il prefisso #) vivono per la durata della tua sessione. Si materializzano; l’optimizer ne vede le statistiche; possono persino essere indicizzate. Per calcoli intermedi davvero costosi, vincono sulle CTE. Per tutto il resto, le CTE sono più pulite.

CTE ricorsive: meno spaventose di quanto sembrino

Una CTE ricorsiva fa riferimento a sé stessa. Uso classico: percorrere una gerarchia, come l’organigramma di Runehold (dipendenti → manager → direttori → VP).

WITH org_tree AS (
    -- Anchor: il CEO, la cima dell'albero
    SELECT EmployeeId, FullName, ManagerId, 0 AS level
    FROM HR.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    -- Parte ricorsiva: aggiunge righe un livello sotto a quelle che abbiamo già
    SELECT e.EmployeeId, e.FullName, e.ManagerId, t.level + 1
    FROM HR.Employee AS e
    JOIN org_tree    AS t ON t.EmployeeId = e.ManagerId
)
SELECT level, REPLICATE('  ', level) + FullName AS indented_name
FROM org_tree
ORDER BY level, FullName;

Struttura:

  1. Anchor — il punto di partenza. “Le righe in cui ManagerId è NULL” = il CEO.
  2. UNION ALL — obbligatorio; non UNION.
  3. Parte ricorsiva — una query che fa join della CTE con sé stessa, aggiungendo un livello a ogni iterazione.

Il motore esegue prima l’anchor, poi la parte ricorsiva ripetutamente, aggiungendo righe finché la parte ricorsiva non restituisce zero nuove righe. Fallisce in modo sicuro con un limite di default di 100 livelli di ricorsione; sovrascrivi con OPTION (MAXRECURSION 500) se hai un albero davvero profondo.

Usi delle CTE ricorsive:

  • Organigrammi, alberi di cartelle, thread di commenti, annidamento di categorie.
  • Distinta base (prodotto → parti → sotto-parti).
  • Generare intervalli di date: “dammi una riga per ogni giorno tra X e Y”.
  • Visita di grafi.

Per qualsiasi cosa raggiungibile da un singolo “anchor”, una CTE ricorsiva è lo strumento giusto. Non è magia; è solo self-join ripetuta con una condizione di terminazione.

Rifattorizzare una query mostro

Ecco un prima-dopo. Il “prima” è basato su una vera query di produzione che ho riscritto l’anno scorso.

Prima — annidata, 55 righe, impossibile da seguire:

SELECT
    c.Name,
    c.Country,
    (SELECT COUNT(*) FROM Sales.Orders o
     WHERE o.CustomerId = c.CustomerId) AS order_count,
    (SELECT SUM(o.Total) FROM Sales.Orders o
     WHERE o.CustomerId = c.CustomerId) AS lifetime_value,
    (SELECT MAX(o.OrderDate) FROM Sales.Orders o
     WHERE o.CustomerId = c.CustomerId) AS last_order_date,
    CASE
        WHEN (SELECT COUNT(*) FROM Sales.Orders o
              WHERE o.CustomerId = c.CustomerId) = 0 THEN 'Never ordered'
        WHEN (SELECT MAX(o.OrderDate) FROM Sales.Orders o
              WHERE o.CustomerId = c.CustomerId) < DATEADD(MONTH, -6, GETDATE()) THEN 'Dormant'
        ELSE 'Active'
    END AS status
FROM Sales.Customer c
WHERE c.Country IN ('IT', 'NL', 'DE');

Quattro sottoquery correlate, ognuna eseguita per ogni cliente. Il CASE ne ripete due. Lenta, illeggibile, fragile.

Dopo — usando una CTE:

WITH customer_orders AS (
    SELECT o.CustomerId,
           COUNT(*)       AS order_count,
           SUM(o.Total)   AS lifetime_value,
           MAX(o.OrderDate) AS last_order_date
    FROM Sales.Orders AS o
    GROUP BY o.CustomerId
)
SELECT
    c.Name,
    c.Country,
    COALESCE(co.order_count, 0)     AS order_count,
    COALESCE(co.lifetime_value, 0)  AS lifetime_value,
    co.last_order_date,
    CASE
        WHEN co.order_count IS NULL OR co.order_count = 0 THEN 'Never ordered'
        WHEN co.last_order_date < DATEADD(MONTH, -6, GETDATE()) THEN 'Dormant'
        ELSE 'Active'
    END AS status
FROM Sales.Customer AS c
LEFT JOIN customer_orders AS co ON co.CustomerId = c.CustomerId
WHERE c.Country IN ('IT', 'NL', 'DE');

Un’aggregazione, fatta una volta sola, usata quattro volte. Stesso risultato. Più veloce. Leggibile. Più facile aggiungere una nuova colonna.

Questo refactor è il singolo refactor più comune che faccio nel codice reale. Se vedi una query con la stessa subquery scalare cinque volte, prendi subito una CTE.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Subquery scalare: ordini sopra la media generale
SELECT o.OrderId, o.Total,
       (SELECT AVG(Total) FROM Sales.Orders) AS overall_aov
FROM Sales.Orders AS o
WHERE o.Total > (SELECT AVG(Total) FROM Sales.Orders)
ORDER BY o.Total DESC;

-- 2. Derived table: paesi con almeno 2 ordini, con il loro AOV
SELECT country, orders, aov
FROM (
    SELECT CountryCode AS country, COUNT(*) AS orders, AVG(Total) AS aov
    FROM Sales.Orders
    GROUP BY CountryCode
) AS s
WHERE orders >= 2
ORDER BY aov DESC;

-- 3. Versione CTE della stessa query
WITH country_stats AS (
    SELECT CountryCode, COUNT(*) AS orders, AVG(Total) AS aov
    FROM Sales.Orders
    GROUP BY CountryCode
)
SELECT CountryCode, orders, aov
FROM country_stats
WHERE orders >= 2
ORDER BY aov DESC;

-- 4. Multi-CTE: classificazione clienti
WITH customer_orders AS (
    SELECT CustomerId,
           COUNT(*)     AS order_count,
           SUM(Total)   AS ltv,
           MAX(OrderDate) AS last_order
    FROM Sales.Orders
    GROUP BY CustomerId
),
classified AS (
    SELECT c.CustomerId, c.Name, c.Country,
           COALESCE(co.ltv, 0) AS ltv,
           CASE
              WHEN co.order_count IS NULL THEN 'Never ordered'
              WHEN co.last_order  < DATEADD(MONTH, -6, GETDATE()) THEN 'Dormant'
              ELSE 'Active'
           END AS status
    FROM Sales.Customer    AS c
    LEFT JOIN customer_orders AS co ON co.CustomerId = c.CustomerId
)
SELECT * FROM classified ORDER BY ltv DESC;

-- 5. CTE ricorsiva: genera ogni giorno tra due date
WITH days AS (
    SELECT CAST('2026-04-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d)
    FROM days
    WHERE d < '2026-04-10'
)
SELECT d, DATENAME(WEEKDAY, d) AS day_of_week
FROM days
OPTION (MAXRECURSION 1000);

Quella ricorsiva è particolarmente potente: ti dà una riga per data senza una tabella di dimensione date. Utile per riempire i buchi in un report di serie temporali.

Prossima lezione: le funzioni di finestra. ROW_NUMBER, LAG, LEAD, totali progressivi. Il motivo per cui “top N per gruppo” diventa banale e che sostituisce metà dei casi in cui la gente userebbe una self-join.

Cerca