CTE vs. Subquery in SQL: Qual è la differenza?

Cosa sono le espressioni di tabella comuni (CTE)? Sono uguali alle subquery? Quando è necessario utilizzare le CTE? Questo articolo analizza le somiglianze e le differenze tra CTE e subquery.

Quando presento a uno studente le Espressioni di tabella comuni, la sua prima reazione è: "È solo una subquery! Perché devo impararla?". Rispondiamo a questa domanda esaminando cosa si può fare con una subquery SQL e quali sono i vantaggi aggiuntivi dell'uso di una CTE.

Se dopo aver letto l'articolo decidete di voler imparare a usare le CTE SQL, LearnSQL.it offre un corso completo sulle Common Table Expressions che fornisce una copertura approfondita dell'argomento. Partendo da esempi semplici, si arriva a concetti difficili come l'elaborazione di alberi e grafici. Ogni argomento è corredato da spiegazioni ed esempi passo-passo. Con 114 esercizi interattivi, potrete fare molta pratica. Per completare il corso sono necessarie circa 18 ore.

Che cos'è una subquery?

Una subquery è una query annidata all'interno di una query principale; per spiegarlo al meglio è sufficiente un esempio. Tutti gli esempi riportati in questo articolo utilizzano SQL Server; la sintassi potrebbe essere leggermente diversa in altri dialetti di SQL.

Supponiamo che la direzione voglia offrire uno sconto a tutti i clienti i cui acquisti annuali siano superiori alla media di tutti i clienti. La query per estrarre un elenco di questi clienti potrebbe essere simile a questa:

SELECT 
  account_no, 
  name 
FROM customers 
WHERE annual_purchases >
  (SELECT AVG(annual_purchases) FROM customers);

La query principale elenca i clienti e la sottoquery calcola la media degli acquisti annuali di tutti i clienti. La subquery non ha bisogno di un nome (tranne quando si lavora in PostgreSQL).

Se non avete familiarità con le subquery, potreste dare un'occhiata al corso LearnSQL.it' SQL per principianti, che ha una sezione dedicata alle subquery. Per saperne di più sulle subquery, potete consultare la nostra Guida per principianti alle subquery SQL. Come mettere in pratica le subquery SQL fornisce alcune idee su come mettere in pratica questi concetti.

Cosa sono le CTE?

Le Common Table Expressions sono insiemi di risultati che vengono definiti all'inizio di una query e a cui la query può accedere come se fossero tabelle. Supponiamo di voler confrontare gli stipendi dei dipendenti con lo stipendio medio del loro ruolo. La query potrebbe avere questo aspetto:

WITH avg_salary AS (
  SELECT 
    role, 
    avg(salary) AS average 
  FROM employee 
  GROUP BY role
)
SELECT 
  employee.role, 
  name, 
  salary, 
  avg_salary
FROM employee 
JOIN avg_salary ON avg_salary.role = employee.role
ORDER BY role, name

Le CTE sono introdotte dalla parola chiave WITH, e i risultati sono memorizzati in una tabella temporanea denominata. In questo esempio, i risultati della CTE sono memorizzati nella tabella avg_salary, che viene utilizzata dalla query principale per rilevare lo stipendio medio di ciascun ruolo.

In realtà, semplici CTE come questa possono essere scritte come subquery. L'articolo "Che cos'è una CTE?" spiega le CTE in modo più dettagliato.

Quali sono le differenze tra CTE e subquery?

Inizierò elencando brevemente le principali differenze, per poi analizzarne alcune in modo più dettagliato.

• Le CTE sono definite nella parte anteriore della query, mentre le subquery sono definite in linea.
• Le CTE devono sempre avere un nome. Solo PostgreSQL insiste sul fatto che le subquery devono avere un nome.
• Le CTE possono essere utilizzate in modo ricorsivo. Lo spiegherò più avanti nell'articolo.
• Le CTE sono molto più leggibili delle subquery quando si scrive un report complesso.
• Una CTE può essere utilizzata più volte all'interno di una query, mentre una subquery può essere utilizzata una sola volta. Questo può rendere la definizione della query molto più corta, ma non necessariamente migliorerà le prestazioni.
• Le sottoquery possono essere utilizzate in una clausola WHERE insieme alle parole chiave IN o EXISTS, ma non è possibile farlo con le CTE.
• Le subquery possono essere utilizzate per prelevare un singolo dato da una tabella per aggiornare un valore in un'altra tabella.

Alcune funzionalità sono limitate alle sole subquery. Riportiamo due esempi in cui le subquery non possono essere sostituite con le CTE. Nel primo esempio, la subquery fa parte della clausola WHERE. Nel secondo, viene utilizzata per prelevare un singolo valore da una tabella per aggiornarne un'altra. Come già detto, SQL non consente di utilizzare le CTE per questi compiti.

Solo subquery: Uso delle subquery in WHERE

Nel primo esempio, una banca conserva i dettagli di tutte le transazioni del giorno in una tabella chiamata daily_trans. I dati contenuti in questa tabella comprendono un numero di conto, un codice di transazione e un importo.

Il database ha anche una tabella chiamata transaction_typese le sue colonne includono il codice della transazione e un indicatore chiamato debit_credit, che è impostato su 1 per i tipi di transazione che accreditano il conto del cliente e su 2 per quelli che lo addebitano.

Se la banca vuole un elenco di tutte le transazioni di accredito del giorno, la query potrebbe avere questo aspetto:

SELECT 
  account_no, 
  tran_code, 
  amount
FROM daily_trans
WHERE tran_code IN
  (SELECT tran_code 
   FROM transaction_types
   WHERE debit_credit = 1);

Questo tipo di query può essere scritto solo utilizzando una sottoquery. Non è possibile sostituire la subquery con una CTE.

Solo subquery: Uso delle subquery in UPDATE

Nel secondo esempio, la stessa banca ha una tabella chiamata customerle cui colonne includono un numero di conto, un nome di cliente e il numero di dipendente della persona designata per l'assistenza ai clienti.

La banca ha effettuato un rimpasto di responsabilità e vuole riassegnare la persona di supporto per alcuni clienti. Per farlo, ha creato una tabella chiamata reassignments che contiene il numero identificativo del vecchio addetto all'assistenza e il numero dell'addetto all'assistenza che ne assumerà le responsabilità.

Per effettuare le riassegnazioni, si potrebbe scrivere una query come questa:

UPDATE customer 
SET support_person =
  (SELECT new_employee 
   FROM reassignments
   WHERE old_employee = customer.support_person);

Le CTE rendono più leggibile una query complessa

Per illustrare come le CTE possono rendere le cose più facili da capire, prendiamo una query che utilizza più sottoquery e ricodifichiamola con le CTE.

Supponiamo di avere un negozio che vende tre tipi di prodotti: libri, musica e video. Il manager vuole sapere quanto ogni cliente ha acquistato per ogni categoria.

Il report potrebbe essere simile a questo:

Ecco una query che utilizza le sottoquery per produrre il report:

SELECT 
	customer, 
	sum(purchases) AS Total, 
	total_books AS Books, 
	total_music AS Music, 
	total_videos AS Videos
FROM sales
JOIN 
	(SELECT account_no, sum(purchases) AS total_books FROM sales 
	WHERE product_type = 'Books'
	GROUP BY account_no) books
ON books.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_music FROM sales 
	WHERE product_type = 'Music'
	GROUP BY account_no) music
ON music.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_videos FROM sales 
	WHERE product_type = 'Videos'
	GROUP BY account_no) videos
ON videos.account_no = sales.account_no
GROUP BY customer
ORDER BY customer

Come si può vedere, è piuttosto complicata. Ed è difficile da seguire se qualcun altro deve modificarla in seguito.

Vediamo ora come potrebbe apparire questa query se la riscrivessimo usando le Espressioni di tabella comuni:

WITH books AS (
  SELECT 
    customer, 
    sum(purchases) AS total_books 
  FROM sales 
  WHERE product_type = 'Books'
  GROUP BY customer
),

music AS (
  SELECT 
    customer, 
    sum(purchases) AS total_music 
  FROM sales 
  WHERE product_type = 'Music'
  GROUP BY customer
),

videos as (
  SELECT 
    customer, 
    sum(purchases) AS total_videos 
  FROM sales 
  WHERE product_type = 'Videos'
  GROUP BY customer
)

SELECT 
  customer, 
  sum(purchases) AS Total, 
  total_books AS Books, 
  total_music AS Music, 
  total_videos AS Videos
FROM sales
JOIN books ON books.customer = sales.customer
JOIN music ON music.customer = sales.customer
JOIN videos ON videos.customer = sales.customer
GROUP BY customer
ORDER BY customer

La maggior parte delle persone troverà questa seconda versione molto più facile da capire, anche se entrambe le query producono esattamente gli stessi risultati.

Cosa sono le CTE ricorsive?

Prima ho detto che solo le CTE possono essere ricorsive. Che cos'è una query ricorsiva? Le query ricorsive consentono di navigare tra i dati gerarchici e di produrre report adatti a dati ad albero e a grafici. Esempi di dati gerarchici sono:

• In un'organizzazione, un dipendente può riferire a un sottodirettore; il sottodirettore riferisce a un manager, e il manager riferisce al capo.
• Nella produzione, un prodotto può essere costituito da diversi componenti. Ciascun componente può anche essere composto da molti sottocomponenti e i sottocomponenti possono essere realizzati con diverse materie prime.

Vediamo un esempio. Una gelateria ha diverse voci nel menu. Ogni voce del menu può essere composta da diversi ingredienti: una banana split è composta da banane, salsa al cioccolato e gelato. Ma anche la salsa al cioccolato ha diversi ingredienti. Questi possono includere cacao in polvere, zucchero e altri ingredienti.

Il proprietario vuole un elenco completo di ogni voce del menu, seguito da tutti i suoi ingredienti. Una parte dell'elenco potrebbe assomigliare a questo:

Nel database abbiamo due tabelle:

• La tabella Item contiene l'elenco di ogni voce del menu e di ogni ingrediente.
• La tabella Bill_of_materials contiene i collegamenti tra ogni voce e i suoi ingredienti.

La tabella Items contiene queste informazioni:

Ecco alcuni esempi di voci della Bill_of_materials tabella. La colonna item_id contiene un collegamento all'elemento principale della tabella, mentre contiene un collegamento a uno dei suoi ingredienti. Items mentre component_id contiene un collegamento a uno dei suoi ingredienti. La prima voce, quindi, mostra che l' elemento 10: Cacao è un ingrediente dell'elemento 9: Salsa al cioccolato.

Ecco la query ricorsiva utilizzata per analizzare queste informazioni. La query è stata scritta in SQL Server; altri dialetti sarebbero leggermente diversi.

WITH menu_ingredients (id, path, description, item_id)
AS (
  SELECT 
CAST (id AS bigint), 
CAST (description as varchar (255)), 
CAST ('' AS varchar(40)),
CAST (id AS bigint)
   FROM items 
   WHERE description = 'Menu'
   
   UNION ALL
   
   SELECT
CAST (bom.component_id AS bigint), 
CAST (m.path + ' > ' + i.description AS varchar(255)),
i.description,
CAST (bom.item_id AS bigint)
   FROM menu_ingredients m, bill_of_materials bom
   JOIN items i
   ON i.id = bom.component_id
   WHERE bom.item_id = m.id
)

SELECT 
  path, 
  description 
FROM menu_ingredients
ORDER BY path

Una spiegazione completa di ciò che accade qui esula dallo scopo di questo articolo, ma ne spiegherò rapidamente le basi. In breve, quando una riga viene aggiunta al set di risultati di una query ricorsiva, questa può "vedere" la riga precedente e utilizzarla per raccogliere un'informazione che può essere usata per trovare la riga successiva. Questa query inizia con il prelievo della voce di livello superiore della distinta base: il menu stesso. Da lì, può scorrere tutte le righe "figlie", cioè gli ingredienti che lo compongono. E ogni ingrediente può raccogliere le proprie righe figlie, se ne ha. Per una spiegazione più dettagliata della ricorsione, consultate Cos'è una CTE ricorsiva in SQL. E come ho già detto, le query ricorsive sono trattate in modo completo nel corso Common Table Expressions di LearnSQL.it.

CTE vs. subquery

In sintesi, si sceglie una CTE quando:

• Si vuole rendere più leggibile una query complessa.
• È necessario utilizzare una query ricorsiva.

Scegliere una subquery quando:

• Si utilizzano le parole chiave della clausola WHERE IN o EXISTS per prelevare i criteri di selezione da un'altra tabella.
• Si vuole selezionare un singolo dato da un'altra tabella come nuovo valore per un campo in un'istruzione UPDATE.

In tutte le altre circostanze, la scelta è vostra: potete usare quella con cui vi sentite più a vostro agio.

Se avete bisogno di fare un po' di pratica con le CTE o con le subquery, potreste provare il sito web Esercizi SQL che contiene centinaia di esercizi interattivi che vi aiuteranno a consolidare le vostre abilità.

Buon apprendimento!