5 motivi per cui dovreste usare le CTE invece delle subquery

Le espressioni di tabella comuni, o CTE, sono state introdotte in SQL:1999 per gestire i casi in cui il risultato di una query viene utilizzato all'interno di un'altra query. Ma non esistevano già le subquery per questo? In questo articolo dimostrerò con diversi esempi perché le CTE sono migliori delle subquery per la struttura e la leggibilità delle query SQL.

Iniziamo ricordando cosa sono le CTE e le subquery e come si differenziano.

Espressioni di tabella comuni e subquery

Una subquery è una query annidata all'interno di un'altra query. Può essere inserita in qualsiasi punto di una query, anche all'interno di un'altra subquery. La sintassi è molto semplice: basta mettere la subquery tra parentesi e inserirla nella query principale, ovunque sia necessaria. Si noti che le sottoquery vengono eseguite prima della query principale (detta anche query padre o query esterna), in modo che i loro risultati possano essere utilizzati dalla query principale. Spesso è piuttosto complicato leggere una query SQL con più subquery perché, anche se vengono eseguite per prime, sono definite da qualche parte nella query principale. Per saperne di più sulle subquery SQL, consultare questa guida introduttiva.

Una Common Table Expression (CTE), detta anche clausola WITH, è un insieme di risultati temporaneo con nome che può essere citato in qualsiasi punto della query. A differenza delle subquery, che vengono inserite esattamente nel punto in cui servono, tutte le CTE vengono definite prima della query principale e vengono poi citate nella query utilizzando il nome assegnato. Negli esempi che seguono, vedremo come questo migliora significativamente la struttura e la leggibilità di una query SQL. Si definiscono prima tutti gli insiemi di risultati temporanei di cui si ha bisogno e poi li si usa nella query principale quando è necessario.

Un'introduzione dettagliata alle espressioni di tabella comuni è disponibile qui. Per saperne di più sulla differenza tra le CTE e le subquery SQL, consultate questo articolo.

5 motivi per scegliere le CTE

Chi studia l'SQL si chiede spesso se sia meglio usare un'espressione di tabella comune o una subquery quando si scrive una query SQL complessa. Vediamo alcuni esempi per spiegare perché si dovrebbe preferire una CTE a una subquery. Per questi esempi, utilizzerò la seguente tabella che riassume le prestazioni dei dipendenti di diversi dipartimenti.

Per ogni dipendente, abbiamo l'ID, il nome completo, il reparto, gli anni di esperienza e un punteggio di completamento KPI su una scala da 0 a 100.

Vediamo come le CTE e le subquery possono aiutarci ad analizzare questi dati.

5 motivi per usare le CTE piuttosto che le subquery

#1. Le CTE usano nomi significativi

È possibile assegnare alle CTE nomi significativi che specificano l'intenzione e rendono la query più leggibile. Per esempio, supponiamo di voler confrontare le prestazioni di ciascun dipendente con la media dei KPI completati nei rispettivi reparti. Per aggiungere la colonna corrispondente alla nostra tabella, dobbiamo prima calcolare il KPI medio per ogni reparto. La nostra intera query con una sottoquery è la seguente:

SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN 
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department) AS avg
ON p.department = avg.department;

È possibile ottenere lo stesso risultato utilizzando un'espressione di tabella comune:

WITH avg_department_kpi AS
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department)
SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN avg_department_kpi avg
ON p.department = avg.department;

Le CTE e le subquery danno lo stesso risultato e le query sono più o meno della stessa lunghezza. Tuttavia, la leggibilità della query CTE è migliore. Il significato di una query complessa può essere ovvio per voi quando guardate il vostro codice, ma potrebbe non essere affatto chiaro per i vostri colleghi. Quando si legge la versione CTE:

• Si vede la query nell'ordine in cui verrà eseguita: prima la sottoquery e poi la query principale.
• È possibile determinare lo scopo della sottoquery in base al suo nome. Nel nostro caso, avg_department_kpi si riferisce alla CTE che produce la media dei KPI per ogni reparto.

Allo stesso tempo, si può notare che la query CTE è un po' più lunga della versione subquery. Non è sempre così, soprattutto quando abbiamo bisogno della stessa sottoquery più volte nella nostra query principale.

Se non conoscete la sintassi WITH, potete esercitarvi con le comuni espressioni di tabella nel nostro corso interattivo. Per ora, passiamo al secondo motivo per preferire le CTE alle subquery.

#2. Le CTE sono riutilizzabili all'interno di una query

A differenza delle subquery, non è necessario ripetere la definizione di una CTE ogni volta che se ne ha bisogno nella query. La si definisce una sola volta, all'inizio della query, e poi la si richiama quando è necessario.

Supponiamo di voler confrontare le prestazioni di diversi reparti. In particolare, vogliamo vedere il KPI medio di ogni reparto e il KPI medio minimo e massimo tra i reparti. Potremmo affrontare questo problema utilizzando una query SQL con tre sottoquery:

1. Trovare il KPI medio per ogni reparto.
2. Trovare il KPI medio minimo tra i reparti.
3. Trovare il KPI medio massimo tra i reparti.

Si noti che le ultime due sottoquery avranno entrambe bisogno del risultato della prima. Pertanto, se si utilizzano le subquery, la nostra soluzione includerà subquery definite all'interno di altre subquery. Il tutto appare piuttosto confuso:

SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS ad
CROSS JOIN
    (SELECT MIN (average_kpi) AS min_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS min
CROSS JOIN
    (SELECT MAX (average_kpi) AS max_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS max;

Al contrario, quando si utilizzano le espressioni di tabella comuni, si definiscono le tre CTE all'inizio della query, si fa riferimento ad esse nella query principale quando necessario e si evitano le subquery multiple annidate:

WITH avg_per_department AS (
    SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    min_kpi_department AS (
    SELECT MIN (average_kpi) AS min_avg_kpi_department
    FROM avg_per_department),

    max_kpi_department AS (
    SELECT MAX (average_kpi) AS max_avg_kpi_department
    FROM avg_per_department)
SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM avg_per_department ad
CROSS JOIN min_kpi_department min
CROSS JOIN max_kpi_department max;

In questo caso, si può notare che le query WITH rendono il codice non solo più leggibile, ma anche molto più breve.

#3. Le CTE dividono in parti i calcoli complessi

Le CTE rendono più chiaro il processo di calcolo. Quando si usano le subquery per i calcoli, la query si trasforma spesso in un groviglio di subquery. Le CTE, invece, mostrano il processo di calcolo in modo più chiaro.

Supponiamo di voler sapere quanti dipendenti junior ed esperti ci sono in ogni reparto. Considereremo junior i dipendenti con due o meno anni di esperienza e esperti quelli con più di due anni di esperienza.

In pratica, abbiamo bisogno di due sottoquery:

1. Calcolare il numero di dipendenti junior in ogni reparto.
2. Calcolare il numero di dipendenti esperti in ogni reparto.

Ecco come possiamo affrontare la questione con le sottoquery:

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department) AS e
JOIN (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department) AS j
ON e.department = j.department;

L'esempio non è molto complicato, quindi probabilmente sarete in grado di seguire la query di cui sopra. Tuttavia, si noti che:

• È necessario indovinare lo scopo di ogni sottoquery.
• Non è immediatamente chiaro quali tabelle siano unite.
• La clausola SELECT elenca i campi da visualizzare nell'output, ma questi campi vengono definiti solo successivamente all'interno delle sottoquery.

Quando i calcoli diventano più complicati, è davvero difficile seguire il processo di calcolo attraverso tutte le sottoquery. Al contrario, il processo di calcolo con le CTE è molto più leggibile:

WITH experienced AS
  (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department),

  junior AS
  (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department)

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM experienced e
JOIN junior j
ON e.department = j.department;

In questo caso, si inizia definendo tutti gli insiemi temporanei di risultati necessari per i calcoli. Poi si scrive una semplice query per ottenere l'output necessario. Tutto è ordinato e chiaro.

Credetemi, apprezzerete molto la struttura che le comuni espressioni di tabella forniscono quando lavorerete con calcoli più complessi. Per dimostrare il mio punto di vista, voglio proporvi un altro esempio con calcoli annidati.

#4. Le CTE annidate sono davvero interessanti

Considerando la riutilizzabilità delle CTE e la loro capacità di chiarire il processo di calcolo, non sorprende che le clausole WITH siano perfette per i calcoli annidati.

Supponiamo di voler calcolare quanti dipendenti in ogni reparto hanno un punteggio KPI (1) superiore alla media o (2) inferiore alla media nel rispettivo reparto. In particolare, per ogni reparto, vogliamo mostrare il punteggio KPI medio, il numero di dipendenti con KPI superiore alla media e il numero di dipendenti con KPI inferiore alla media.

In pratica, abbiamo bisogno di tre sottoquery:

1. Ottenere il KPI medio per ogni reparto.
2. Ottenere il numero di dipendenti che hanno un punteggio KPI superiore alla media.
3. Per ottenere il numero di dipendenti che hanno un punteggio KPI inferiore alla media.

Tuttavia, quando si utilizzano le subquery, è necessario aggiungere la prima subquery tre volte, di cui due volte quando sarà annidata all'interno delle altre due subquery. La query risultante appare piuttosto disordinata:

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS avg
JOIN
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department) AS aa
ON avg.department = aa.department
JOIN
    (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department) AS ba
ON avg.department = ba.department;

La query ha funzionato e abbiamo ottenuto l'output che volevamo, ma notate quanto sia difficile seguire le molteplici subquery annidate. Nel mondo reale, spesso le cose si complicano ulteriormente.

Per contro, vediamo come appare ordinata la stessa query quando si utilizzano le CTE anziché le subquery:

WITH avg_kpi_department AS
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    above_average AS
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department),

     below_average AS
     (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department)

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM avg_kpi_department avg
JOIN above_average aa
ON avg.department = aa.department
JOIN below_average ba
ON avg.department = ba.department;

È possibile seguire chiaramente come sono stati definiti i tre insiemi temporanei di risultati. Si può capire il loro scopo dai nomi assegnati. Non è necessario ripetere più volte la stessa sottoquery all'interno della stessa query principale: quando si arriva alla query principale, tutto il lavoro preparatorio è stato fatto e si può semplicemente unire le CTE già definite.

#5. Le CTE consentono la ricorsione

Infine, le CTE sono ottime per elaborare grafi, alberi e altre strutture gerarchiche. Questo perché la sintassi WITH è in grado di elaborare la ricorsione. Una query ricorsiva è una query che fa riferimento a se stessa.

Ad esempio, se abbiamo una struttura organizzativa tipica, in cui ogni dipendente ha un superiore e i superiori hanno più subordinati, le CTE ricorsive possono aiutarci ad analizzare questi dati in modo efficiente. Con una sola query SQL e dati a livello individuale, possiamo calcolare lo stipendio totale per ogni reparto e sottodipartimento o il numero totale di giorni di ferie non utilizzati in ogni unità organizzativa.

La sintassi delle CTE ricorsive è piuttosto complicata. Per una spiegazione dettagliata, vi consiglio di leggere questo articolo che spiega come le query ricorsive elaborano le strutture gerarchiche. Si consiglia di leggere anche questi esempi di applicazione delle query ricorsive alle strutture a grafo.

Esercitiamoci con le CTE!

Ora che vi siete resi conto dei vantaggi che le CTE apportano alle query SQL, è il momento di fare pratica! Vi consiglio di iniziare con il nostro Recursive Queries (114 sfide di codifica), che tratta tutti i tipi di espressioni di tabella comuni, comprese le CTE semplici, le CTE annidate e le CTE ricorsive. Maggiori dettagli in questo articolo riassuntivo.

Grazie per aver letto e buon apprendimento!