Qual è la differenza tra le CTE e le viste di SQL?

Viste SQL e CTE: Cosa fanno? Come si differenziano? Quale utilizzare e quando? Trovate le risposte qui!

In SQL, sia le CTE (espressioni comuni di tabella) che le viste aiutano a organizzare le query, rendendo il codice più pulito e facile da seguire. Tuttavia, esistono alcune importanti differenze tra loro. Questo articolo illustra diversi esempi di CTE e di viste e spiega quando utilizzarle.

Cosa sono le espressioni di tabella comuni (CTE) in SQL?

Come si ricorderà, le espressioni di tabella comuni sono insiemi di risultati temporanei che vengono creati con semplici istruzioni SQL e a cui si fa riferimento all'interno delle istruzioni SELECT, INSERT, UPDATE o DELETE.

Ad esempio, supponiamo di avere una tabella chiamata top_apps con le informazioni sulle valutazioni delle migliori applicazioni in diverse categorie:

Abbiamo anche la tabella google_apps che contiene le stesse informazioni per diverse applicazioni di Google:

Vogliamo confrontare le prestazioni delle applicazioni Google con quelle delle applicazioni più performanti nelle categorie corrispondenti. In particolare, vogliamo che le informazioni sulla valutazione massima in ogni categoria siano mostrate accanto alla valutazione dell'applicazione Google della stessa categoria.

Ecco una query che realizza questo obiettivo utilizzando una CTE:

WITH top_apps_max AS (
	SELECT category, MAX(rating) AS max_rating
	FROM top_apps
	GROUP BY category)
SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

La query inizia creando un insieme temporaneo di risultati chiamato top_apps_max. Questo set di risultati è derivato dalla tabella top_apps e comprende l'elenco delle categorie e le corrispondenti valutazioni massime. Poi, nell'istruzione principale SELECT, si unisce questo insieme temporaneo di risultati con la tabella utilizzando la colonna comune . google_apps utilizzando la colonna comune category. Ecco il risultato:

Si potrebbe ottenere lo stesso risultato utilizzando una subquery invece di una CTE. Tuttavia, rispetto alle subquery, l'uso di una CTE SQL consente di ottenere un codice più pulito e facile da seguire, leggibile da cima a fondo: si crea innanzitutto un set di risultati temporaneo con un nome specifico, che verrà utilizzato in seguito nella query per fare riferimento a quel set di risultati.

Si noti che la CTE esiste in memoria solo mentre la query è in esecuzione. Dopo l'esecuzione della query, la CTE viene scartata e non può essere utilizzata per la prossima query SQL, a meno che non venga definita di nuovo. Tuttavia, la stessa CTE potrebbe essere citata più volte nella query principale e nelle eventuali sottoquery.

Per saperne di più sulle CTE SQL, consultare questa guida introduttiva completa alle espressioni di tabella comuni. E se siete interessati a fare pratica con le CTE con esempi reali, date un'occhiata al nostro corso interattivo sulle espressioni di tabella comuni.

Nel linguaggio comune, le CTE sono talvolta chiamate viste in linea. Ricordiamo quindi cos'è una vista e come si differenzia da una CTE.

Che cos'è una vista in SQL?

Una vista è una query SQL memorizzata che viene eseguita ogni volta che si fa riferimento ad essa in un'altra query. Si noti che una vista non memorizza l'output di una particolare query, ma la query stessa.

Vediamo come funziona. Utilizzeremo un esempio simile, ma questa volta useremo una vista invece di una CTE.

Cominceremo creando la vista top_apps_max con la parola chiave CREATE VIEW, seguita dall'istruzione SELECT:

CREATE VIEW top_apps_max AS
SELECT category, MAX(rating) AS max_rating, MAX(reviews) AS max_num_reviews
FROM top_apps
GROUP BY category;

Come si vede, l'istruzione SELECT è molto simile a quella utilizzata con la CTE. Potrebbe essere assolutamente identica, ma abbiamo cambiato lo scopo: Ora vogliamo vedere il numero massimo di recensioni oltre alla valutazione massima per ogni categoria (solo per avere più dati su cui lavorare).

Quindi, ora abbiamo una query SQL memorizzata chiamata top_apps_max. È ora di usarla!

Cominciamo a replicare il nostro primo esempio utilizzando una vista invece di una CTE. Tuttavia, questa volta:

• Non abbiamo bisogno di creare una CTE all'inizio della query, perché abbiamo già la vista memorizzata top_apps_max.
• È sufficiente unire la tabella google_apps con la vista top_apps_max sulla colonna categoria ed elencare le colonne che vogliamo vedere nell'output:

SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

Il risultato di questa query sarà lo stesso del primo esempio:

Qual è la differenza?

Innanzitutto, è possibile utilizzare la stessa vista in altre query senza doverla definire nuovamente. Ad esempio, la query SQL qui sotto fa riferimento alla stessa vista top_apps_max, ma questa volta viene utilizzata per confrontare il numero di recensioni piuttosto che la valutazione delle diverse applicazioni:

SELECT ga.name, ga.category, ga.reviews, tam.max_num_reviews
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

Inoltre, è possibile utilizzare una CTE quando si definisce una vista. Supponiamo di voler vedere anche il nome dell'applicazione migliore in ogni categoria (cioè quella con il maggior numero di recensioni). Un modo per farlo è creare una vista che fornisca il nome, la categoria e il numero di recensioni per l'applicazione migliore in ogni categoria:

CREATE VIEW top_app_per_category AS
WITH top_app_max_reviews AS (
    SELECT category, MAX(reviews) AS max_num_reviews
    FROM top_apps
    GROUP BY category)
SELECT ta.name, ta.category, ta.reviews
FROM top_apps ta
JOIN top_app_max_reviews tamr
ON ta.reviews = tamr.max_num_reviews;

Come si vede, la query memorizzata in questa vista include un'espressione di tabella comune che fornisce il numero massimo di recensioni per ogni categoria. Quindi, nella query principale della vista top_app_per_category, uniamo questa CTE con la tabella top_apps per ottenere il nome dell'applicazione con il maggior numero di recensioni in ogni categoria.

Ora possiamo fare riferimento a questa vista in un'altra query che restituisce le applicazioni di Google insieme al nome dell'applicazione migliore nella categoria corrispondente e al suo numero di recensioni:

SELECT ga.name, ga.category, ga.reviews, top.name AS top_app, top.reviews AS top_app_reviews
FROM google_apps ga
JOIN top_app_per_category top
ON ga.category = top.category;

La cosa fondamentale da ricordare sulle viste SQL è che, a differenza di una CTE, una vista è un oggetto fisico di un database e viene memorizzata su disco. Tuttavia, le viste memorizzano solo la query, non i dati restituiti dalla query. I dati vengono calcolati ogni volta che si fa riferimento alla vista nella query.

Volete saperne di più sulle viste SQL? Ecco un ottimo articolo che spiega le viste SQL con numerosi esempi e illustrazioni. Inoltre, ricordate di esercitarvi con le viste con il nostro corso interattivo Working with Views.

SQL CTE vs. View: Quando usare ciascuna di esse

Sebbene esistano alcune differenze, le espressioni comuni di tabella e le viste sembrano avere prestazioni molto simili. Quindi, quando è opportuno utilizzarle?

• Query ad hoc. Per le query a cui si fa riferimento occasionalmente (o solo una volta), di solito è meglio usare una CTE. Se la query serve di nuovo, è sufficiente copiare la CTE e modificarla, se necessario.
• Query usate frequentemente. Se si tende a fare spesso riferimento alla stessa query, creare una vista corrispondente è una buona idea. Tuttavia, per creare una vista è necessario disporre dei permessi di creazione nel database.
• Gestione degli accessi. Una vista può essere utilizzata per limitare l'accesso al database di determinati utenti, consentendo loro di ottenere le informazioni di cui hanno bisogno. Si può dare agli utenti l'accesso a viste specifiche che interrogano i dati che sono autorizzati a vedere senza esporre l'intero database. In questo caso, una vista fornisce un ulteriore livello di accesso.

Esercitiamoci con le CTE e le viste SQL!

Ora che avete una conoscenza di base delle CTE e delle viste SQL, siete pronti per iniziare a usarle nelle vostre query! Esercitarsi con le query SQL è il modo migliore per capire come funzionano le CTE e come le viste fanno risparmiare tempo nel riscrivere ed eseguire le query.

LearnSQL.it ha sviluppato diversi corsi che trattano questi argomenti in modo approfondito. Prima di tutto, date un'occhiata al corso Recursive Queries che vi guiderà dalle CTE semplici alle CTE annidate, fino alle CTE ricorsive più impegnative. 114 esercizi interattivi vi aiuteranno a padroneggiare le più comuni espressioni di tabella nel modo più efficiente.

Per coloro che sono interessati a saperne di più sulle viste SQL, abbiamo preparato il corso Lavorare con le viste. Si tratta di un corso avanzato che vi insegnerà a creare, modificare e rimuovere le viste in SQL Server, MySQL, Oracle e PostgreSQL.

Grazie per aver letto e buon apprendimento!