Subquery vs. CTE: un primo SQL

Vi siete mai chiesti quali siano le differenze tra una subquery e una common table expression (CTE) in SQL? I concetti sembrano molto simili, ma conoscerne le differenze e sapere quando usarle vi aiuterà a scrivere query efficienti e leggibili.

Per prima cosa spiegheremo i concetti alla base delle subquery e delle CTE. Poi vedremo alcuni esempi e infine analizzeremo i pro e i contro di ciascuna tecnica.

Che cos'è una subquery?

Una subquery è una query all'interno di una query. Può essere utilizzata in diversi modi: nella clausola FROM, per filtrare o anche come colonna. Per usare una subquery, basta aggiungere delle parentesi e inserire la query al suo interno.

Nei primi esempi, lavoreremo con i dati relativi ai risultati di una gara di salto in lungo. Abbiamo a disposizione due tabelle:

participant - Memorizza informazioni sui partecipanti al concorso:

• id - Un ID univoco per ogni partecipante.
• first_name - Il nome del partecipante.
• last_name - Il cognome del partecipante.

jump - Memorizza informazioni sui salti effettuati dai partecipanti:

• id - L'ID di ogni salto.
• participant_id - L'ID del partecipante che ha effettuato il salto.
• contest_id - L'ID della gara in cui è stato effettuato il salto.
• length - Lunghezza del salto, in centimetri.

participant

jump

Dal momento che si conoscono i dati utilizzati, è possibile dare un'occhiata ai seguenti esempi di sottoquery:

SELECT
  first_name,
  last_name,
  length
FROM participant
JOIN jump
  ON jump.participant_id = participant.id
WHERE length > (
  SELECT
    AVG(length)
  FROM jump
);

Questa query mostra i partecipanti con salti più lunghi della lunghezza media del salto. Nella condizione WHERE, abbiamo utilizzato una sottoquery per ottenere la lunghezza media del salto. Poiché la subquery restituisce un solo valore, è possibile confrontare facilmente i dati delle colonne.

Un altro esempio:

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

In questa query, mostriamo la lunghezza media del salto più grande di ogni partecipante. Per ottenere il risultato, dobbiamo innanzitutto calcolare la lunghezza media del salto per ogni partecipante. Per farlo, utilizziamo una sottoquery all'interno della clausola FROM. Quindi, utilizziamo semplicemente MAX() per restituire la lunghezza media maggiore.

Questi sono solo due esempi di subquery. L'argomento è vasto, anche se i casi d'uso sono piuttosto semplici, e i concetti sono troppi per essere descritti in questo articolo. Una breve panoramica delle subquery è disponibile nell'articolo SQL Subqueries sul blog LearnSQL.it . Se siete interessati a lavorare con le subquery, consultate la parte 6 del corso SQL Basics (si chiama addirittura Subqueries).

Potete anche guardare gli episodi della nostra serie We Learn SQL su Youtube. Molti di essi sono stati dedicati alle subquery SQL. Ricordatevi di iscrivervi al nostro canale.

Che cos'è la CTE?

Una Common Table Expression (in breve CTE ) è una query che viene creata prima di scrivere la query principale. Poi, possiamo semplicemente usarla come una tabella regolare all'interno del nostro codice.

Guardate l'esempio seguente. Ancora una volta, stiamo utilizzando i dati della gara di salto in lungo:

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Il risultato è esattamente lo stesso dell'esempio precedente con la subquery: la lunghezza media del salto più grande per ogni partecipante. Tuttavia, invece di scrivere la nostra query all'interno della clausola FROM, l'abbiamo inserita nella clausola WITH (che precede la query principale). Abbiamo chiamato la nostra CTE average_length e l'abbiamo utilizzata nella clausola FROM della query principale.

Naturalmente, le CTE possono essere molto più complicate di questo esempio. Ma non ne parleremo qui. Se si desidera approfondire la conoscenza delle CTE, si consiglia di consultare il corso Recursive Queries all'indirizzo LearnSQL.it.

Nessuna differenza...?

In un primo momento, si potrebbe pensare che non ci sia quasi nessuna differenza tra le subquery e le CTE. Abbiamo usato sia una subquery che una CTE nella clausola FROM e la sintassi era solo leggermente diversa. Tuttavia, non dimenticate il primo esempio: nella clausola WHERE abbiamo usato una subquery. Non si poteva usare una CTE, e questa non è l'unica differenza!

Subquery vs CTE: qual è la differenza?

Naturalmente, questo non significa che le CTE siano inferiori alle subquery. Esaminiamo le differenze tra le due, iniziando dalle CTE.

Differenza n. 1: le CTE possono essere ricorsive

Vediamo il primo vantaggio delle CTE. Le CTE consentono di utilizzare un concetto potente: la ricorsione. Grazie alla ricorsione, SQL è ora Turing completo: ogni programma che può essere scritto in qualsiasi linguaggio di programmazione può essere scritto anche in SQL. (Se avete dubbi sul fatto che SQL sia un linguaggio di programmazione, date un'occhiata a Is SQL a programming language? sul blog LearnSQL.it ).

Come funziona la ricorsione in SQL? Consente alla CTE di richiamare se stessa fino a quando non viene soddisfatta una determinata condizione finale. A ogni passaggio, la query si espande e modifica i dati in suo possesso. Vediamo un esempio.

Lavoreremo con i seguenti dati relativi ad alcuni dipendenti di un'azienda fittizia. Sono memorizzati nella tabella employee che contiene le seguenti colonne:

• id - Un ID univoco per ogni dipendente.
• first_name - Il nome del dipendente.
• last_name - Il cognome del dipendente.
• manager_id - L'ID del manager del dipendente.

employee

Ora, vorremmo mostrare la catena di gestione dall'amministratore delegato (una persona senza valore nella colonna manager_id ) a ciascun dipendente. La query seguente risolverà questo problema. Guardate:

WITH RECURSIVE employee_chain AS (
  SELECT
    id,
    first_name,
    last_name,
    first_name || ' ' || last_name AS chain
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    chain || '->' || employee.first_name || ' ' || employee.last_name
  FROM employee_chain
  JOIN employee
    ON employee.manager_id = employee_chain.id
)

SELECT
  first_name,
  last_name,
  chain
FROM employee_chain;

Il risultato sarà simile a questo:

Abbiamo scritto una query che può creare facilmente un'intera catena di relazioni. Si potrebbe pensare che ciò possa essere ottenuto con delle sottoquery, ma man mano che la catena di gestione diventa sempre più profonda, si dovrà scrivere sempre più codice. La quantità di codice da scrivere dipende dalla profondità della catena, che può essere controllata solo con una CTE ricorsiva.

Come funziona questa query? Inizia eseguendo la prima parte (prima di UNION ALL) e seleziona un dipendente senza manager (cioè Maisy Bloom). Poi, la parte sottostante la UNION ALL seleziona i dipendenti gestiti direttamente da Maisy (Caine Farrow). Poiché la query chiama se stessa, esegue nuovamente la stessa parte e seleziona tutti i dipendenti gestiti da Caine (Waqar Jarvis e Lacey-Mai Rahman). Ripete questa operazione finché ha righe da unire. Dopo aver attraversato l'intera catena di gestione, la query si ferma.

Se questo è il primo incontro con la ricorsione in SQL, potrebbe essere un po' difficile da capire. È del tutto normale. Consultate la sezione Fallo in SQL: L'attraversamento ricorsivo di alberi in SQL per una spiegazione più dettagliata.

Differenza n. 2: le CTE sono riutilizzabili

Un enorme vantaggio delle CTE è che possono essere utilizzate più volte in una query. Non è necessario copiare l'intero codice CTE: è sufficiente inserire il nome della CTE.

Utilizzando i dati della sezione precedente, vorremmo 1) filtrare i dipendenti che non hanno un manager e poi 2) mostrare ogni dipendente con il suo manager, ma solo se ha un manager. Il risultato sarà simile a questo:

Vediamo come una CTE potrebbe risolvere questo compito:

WITH not_null_manager AS (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
)

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM not_null_manager AS nnm1
JOIN not_null_manager AS nnm2
  ON nnm1.manager_id = nnm2.id;

Vediamo ora come una subquery otterrebbe lo stesso risultato:

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm1
JOIN (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm2
  ON nnm1.manager_id = nnm2.id;

Come si può notare, la query CTE contiene meno codice. È anche più leggibile: si ripete semplicemente il nome della CTE (not_null_manager) invece di un intero pezzo di codice.

Non c'è molta differenza nell'efficienza delle prestazioni tra queste due query. Anche se la CTE viene dichiarata una sola volta, il tempo di esecuzione è quasi lo stesso.

Differenza n. 3: le CTE possono essere più leggibili

Quindi, sappiamo che è possibile scrivere meno codice utilizzando le CTE. Ma che dire dell'organizzazione del codice? Ecco un altro esempio che si concentra sulla clausola FROM.

Ricordate ancora i primi esempi? Quelli in cui si restituiva la lunghezza media del salto più grande? Se no, ecco un rapido ripasso.

Questo utilizza una sottoquery:

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

E questo utilizza una CTE:

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Quale pensate sia più leggibile? Direi che la seconda (CTE) è un po' meglio. Il codice è meno annidato e c'è un piccolo livello di astrazione. Si capisce subito che questa query seleziona la lunghezza media massima.

La leggibilità delle CTE è ancora più evidente negli esempi della sezione precedente. Quando le query diventano sempre più grandi, l'uso delle CTE aumenta notevolmente la leggibilità del codice. E la leggibilità è fondamentale per uno sviluppo efficiente del codice.

Se volete saperne di più sull'uso delle CTE per migliorare la leggibilità del codice, date un'occhiata all'articolo Come organizzare le query SQL con le CTE.

Ma le subquery sono talvolta insostituibili

Finora avete appreso alcune differenze tra le subquery e le CTE. A dire il vero, stavo cercando di convincervi che le CTE sono molto meglio delle subquery. Ma in questa sezione imparerete perché le subquery possono essere preziose.

Filtrare con una subquery

Il primo esempio di questo articolo ha utilizzato una subquery nella clausola WHERE. Non ho mostrato un esempio simile nella sezione CTE. Questo perché solo le subquery possono essere utilizzate nella clausola WHERE!

Inoltre, ci sono molte parole chiave che si possono usare nella condizione WHERE, ad esempio ALL, ANY, EXISTS, e altre ancora! Purtroppo non posso spiegarle in questa sede, perché richiederebbe troppo tempo. Si consiglia invece di consultare la sezione Subqueries del nostro corso SQL Basics del nostro corso. Non solo imparerete a conoscere queste parole chiave, ma risolverete anche alcuni problemi che le utilizzano! Oppure, se volete una breve spiegazione, consultate l'articolo SQL Subqueries nel nostro blog.

Le subquery possono agire come colonne

È possibile utilizzare le subquery come se fossero colonne. L'unico vincolo è che la subquery deve restituire un solo valore. Guardate:

SELECT DISTINCT
  contest_id,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length > 600
  ) AS longer_jumps,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length <= 600
  ) AS shorter_jumps
FROM jump AS outside_jump;

Per ogni gara, questa query restituisce il numero di salti superiori a 600 cm (calcolati nella prima sottoquery) e il numero di salti inferiori o uguali a 600 cm (calcolati nella seconda sottoquery). Il risultato sarà simile a questo:

Subquery correlate

Guardate di nuovo l'esempio precedente. Avete notato che ho usato un riferimento a una tabella nella query esterna all'interno della subquery? Ho persino fatto riferimento al valore della riga corrente di quella tabella. Questa si chiama "subquery correlata". Consente di utilizzare i valori della query esterna all'interno della subquery.

È una tecnica molto utile, ma anche piuttosto complicata; non la spiegheremo in questo articolo. Tuttavia, vi invitiamo a consultare Correlated Subquery in SQL: A Beginner's Guide nel nostro blog per una spiegazione.

Un'altra differenza: Le CTE devono essere nominate

L'ultima differenza tra le CTE e le subquery sta nella denominazione. Le CTE devono sempre avere un nome. Nella maggior parte dei motori di database, invece, le subquery non richiedono alcun nome (l'unica eccezione è la clausola FROM nel mio motore di database preferito, PostgreSQL).

È buona norma assegnare un nome alle subquery inserite nelle clausole FROM o SELECT, ma non è un obbligo. E, per essere precisi, non è possibile dare un nome alle subquery utilizzate nella clausola WHERE.

Si potrebbe pensare che la denominazione non sia una grande differenza e che non influisca più di tanto. Tuttavia, può capitare di dover controllare rapidamente qualcosa nel database. In questo caso, la scelta potrebbe ricadere sulla sintassi più semplice. Anche se la query è meno leggibile, si tenga presente che tali query vengono raramente lette dopo essere state utilizzate.

Subquery e CTE: cosa è meglio?

Abbiamo imparato molto sulle differenze tra CTE e subquery. Allora, qual è la migliore? La risposta è: nessuna, nessuna delle due, oppure dipende: sia le subquery che le CTE hanno pro e contro. Ogni query dovrebbe essere analizzata e la scelta tra queste due dovrebbe essere fatta caso per caso. Ma per farlo, è necessario imparare a fondo entrambi i concetti.

Per saperne di più sulle subquery, vi suggerisco la sezione Subqueries del corso LearnSQL. SQL Basics di LearnSQL. Se desiderate saperne di più sulle CTE, il corso Recursive Queries è la scelta migliore. Questi corsi vi aiuteranno ad apprendere rapidamente questi concetti. In questo modo, sarete in grado di decidere quali query beneficiano delle CTE e quali richiedono le subquery.

Tuttavia, se avete già una certa dimestichezza con le subquery correlate e non volete imparare di nuovo le stesse cose, potreste anche voler affinare le vostre competenze con il nostro corso SQL Practice Set corso.