Guida per principianti alla subquery SQL

Le subquery sono una potente risorsa SQL che ci permette di combinare i dati di più tabelle in un'unica query. In questo articolo vi insegneremo tutto ciò che serve per iniziare a usare le subquery.

La definizione più semplice di una subquery SQL è forse "Una query dentro una query". Le subquery sono così facili da capire che spesso compaiono nei capitoli iniziali dei corsi di SQL.

Tuttavia, esistono molte varianti di subquery che devono essere spiegate. Inoltre, sebbene le subquery siano generalmente utilizzate nella clausola WHERE, è possibile utilizzarle in altre clausole, come FROM, HAVING e SELECT.

In breve, c'è molto di più da sapere sulle subquery che non solo cosa sono e dove vanno. Cominciamo quindi con il primo esempio di subquery SQL per principianti.

Esempio di subquery di base

Prima di passare alle subquery, dobbiamo spiegare le tabelle del nostro database. Per rilassare le nostre menti in questo periodo di allontanamento sociale, utilizzerò esempi relativi a luoghi belli e rilassanti. Il nostro database di esempio avrà due tabelle. La prima tabella si chiama best_10_places e contiene i 10 luoghi migliori per diversi tipi di attività (come lo snorkeling, lo sci e il trekking). La tabella ha colonne per il nome del luogo, l'attività che vi si può svolgere, la classifica di questo luogo e la città più vicina. Date un'occhiata:

Tabella: best_10_places

Se si vuole viaggiare in uno di questi bellissimi luoghi, è necessario un biglietto; la tabella one_way_ticket ha un record per ogni coppia di città collegate da qualsiasi tipo di trasporto. Utilizzeremo questa tabella per determinare come arrivare da una città all'altra. Le colonne contengono informazioni sulla città di origine, sulla città di destinazione, sul prezzo del biglietto, sulla durata del viaggio e sul tipo di trasporto (ad esempio, treno, aereo, ecc.). Di seguito è riportato un sottoinsieme di questa tabella:

Tabella: biglietto_un_viaggio

Ora siamo pronti per il primo esempio. Supponiamo che una persona a Parigi voglia andare nel posto numero 1 al mondo per fare snorkeling. Che tipo di trasporto va da Parigi a questo posto?

Come probabilmente sapete, la query SQL più semplice è formata da una clausola SELECT, una FROM e (facoltativamente) una WHERE. Come abbiamo già detto, una sottoquery è una query all'interno di una query. Quindi, nel prossimo esempio vedremo due query: la query principale (detta anche query esterna) e la subquery(in blu):

SELECT city_destination, transportation, ticket_price, travel_time
FROM one_way_ticket
WHERE city_destination = (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'snorkeling'
      AND ranking_position = 1
  )
  AND city_origin = 'Paris'

La subquery viene eseguita per prima, restituendo il sito closest_city alla migliore destinazione per lo snorkeling (la città di Florianopolis in Brasile). Quindi viene eseguita la query principale, sostituendo la subquery con il suo risultato (Florianopolis). Il risultato finale è:

Quando si utilizzano le sottoquery:

• È necessario racchiudere sempre la sottoquery tra parentesi.
• Prestare attenzione all'operatore utilizzato per confrontare il risultato della subquery. Nell'esempio precedente abbiamo usato "="; tuttavia, questo operatore deve essere usato con le subquery che restituiscono una sola riga e una sola colonna (note anche come subquery "scalari").

Vi suggerisco di leggere l'articolo SQL Subqueries per vedere altri esempi di subquery per principianti spiegati in dettaglio. Le subquery fanno anche parte del nostro SQL Basics corso, un tutorial passo dopo passo che vi porta a conoscere l'SQL di base con esempi ed esercizi.

Subquery scalari o non scalari: Questa è la domanda

Una subquery scalare restituisce una sola colonna con una sola riga. Che cos'è una subquery non scalare? Una subquery che restituisce più righe.

Ci sono molti operatori che possiamo usare per confrontare una colonna con una subquery. Tuttavia, alcuni di essi possono essere utilizzati solo con subquery scalari: =, >, >=, < e <=. Se si utilizza uno di questi operatori, la subquery deve essere scalare.

Vediamo un esempio con una sottoquery scalare. Supponiamo di avere un cliente che vuole andare da Parigi a Bariloche. Prima di acquistare il biglietto, il cliente vuole vedere se ci sono posti con un biglietto più economico. La query seguente troverà queste città:

SELECT city_destination, ticket_price, travel_time, transportation
FROM one_way_ticket
WHERE ticket_price < (
    SELECT ticket_price
    FROM one_way_ticket
    WHERE city_destination = 'Bariloche'
      AND city_origin = 'Paris'
  )
  AND city_origin = 'Paris'

Anche in questo caso, la subquery viene eseguita per prima; il suo risultato (il prezzo di un biglietto Parigi-Bariloche, ovvero 970 dollari) viene confrontato con la colonna ticket_price della query esterna. In questo modo si ottengono tutti i record in one_way_ticket con un valore di ticket_price inferiore a 970 dollari. Il risultato della query è mostrato di seguito:

Altri operatori, come IN, EXISTS o NOT EXISTS, > ALL, = ANY, possono essere utilizzati con sottoquery scalari o non scalari.

Il prossimo esempio utilizza l'operatore IN. Supponiamo che la persona che ha chiesto il posto migliore per fare snorkeling voglia esplorare altre destinazioni; in effetti, vorrebbe vedere i tre posti migliori per fare snorkeling. Il cambiamento nella nostra subquery è chiaro: dobbiamo solo cambiare “ranking_position = 1” con “ranking_position <= 3”. Tuttavia, la nostra subquery restituirà tre record e non sarà più scalare. Utilizzeremo l'operatore IN, in questo modo:

SELECT city_destination, transportation, ticket_price, travel_time
FROM one_way_ticket
WHERE city_destination IN (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'snorkeling'
      AND ranking_position <= 3
  )
  AND city_origin = 'Paris'

Come nell'esempio precedente, il database esegue prima la subquery, che restituisce un elenco di tre città (le città più vicine alle 3 principali destinazioni per lo snorkeling: Florianopolis, Honolulu e Melbourne). Quindi viene eseguita la query esterna con queste condizioni:

city_destination IN ('Florianopolis', 'Honolulu', 'Melbourne')

L'operatore IN restituisce TRUE quando il valore di city_destination è una di queste tre città. La query principale restituisce quindi il seguente risultato:

Se volete approfondire le sfumature delle subquery SQL, consultate il capitolo sulle subquery del nostro corso interattivo. SQL Basics dove si possono trovare diversi esempi e molti esercizi pratici.

Subquery avanzate

Il concetto di subquery è facile da capire. Ma grazie alla flessibilità di SQL, le subquery possono essere utilizzate in molte forme diverse. La trattazione di tutti i possibili utilizzi va oltre lo scopo di questo articolo. Ci limiteremo invece a illustrare alcuni degli usi più importanti.

In quanti posti diversi si può inserire una subquery?

Le sottoquery possono essere utilizzate in diversi punti di una query SQL, tra cui le clausole WHERE, FROM, HAVING e SELECT; inoltre, una sottoquery può essere utilizzata anche come parte di un'istruzione UPDATE, DELETE o INSERT. Nel prossimo esempio, vedremo come utilizzare una sottoquery nella clausola FROM.

Supponiamo che il proprietario dell'agenzia di viaggi voglia mostrare ogni città con il costo del biglietto e il numero di "posti migliori" vicino a questa città. Per ottenere la quantità di "posti migliori" per ogni città, useremo una sottoquery (mostrata in blu) nella clausola FROM per creare una pseudo tabella. La query esterna sarà poi JOIN con one_way_ticket e la pseudo tabella.

SELECT city_destination, ticket_price, pseudo_table.quantity
FROM one_way_ticket
JOIN (
    SELECT closest_city AS city, count(*) AS quantity
    FROM best_10_places
    GROUP BY 1
  ) pseudo_table
  ON one_way_ticket.pseudo_table.city

Il risultato di questa query è:

Per ulteriori informazioni sull'uso delle subquery in altre istruzioni SQL, leggere Subquery nelle istruzioni UPDATE e DELETE. Questo articolo contiene diversi esempi con codice SQL pronto per essere copiato e incollato se si vuole provare.

EXISTS: Un operatore orientato alle subquery

Uno degli operatori più potenti che si possono usare con le subquery è l'operatore EXISTS. Come si può vedere nell'esempio seguente, l'operatore EXISTS deve precedere la subquery. L'operatore restituisce TRUE se la sottoquery restituisce almeno una riga, indipendentemente dal contenuto della riga. Se la sottoquery restituisce 0 righe, EXISTS restituirà FALSE.

Per il prossimo esempio, supponiamo che il nostro cliente di Parigi voglia recarsi in un luogo dove possa fare sia trekking che snorkeling. La query seguente può essere utilizzata per rispondere a questo cliente:

SELECT city_destination, transportation, ticket_price, travel_time
FROM one_way_ticket
WHERE EXISTS (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'snorkeling'
      AND closest_city = one_way_ticket.city_destination
  )
  AND EXISTS (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'trekking'
    AND closest_city = one_way_ticket.city_destination
  )
  AND city_origin = 'Paris'

Il risultato mostra i record relativi alle città con attività di trekking e snorkeling:

Un punto interessante della sottoquery precedente è il riferimento alla colonna one_way_ticket.city_destination nella query esterna. Le sottoquery che fanno riferimento a colonne della query esterna sono chiamate "sottoquery correlate" e hanno alcuni comportamenti specifici. Come nell'esempio precedente, le subquery correlate tendono a essere utilizzate con gli operatori di subquery EXISTS e NOT EXISTS.

Le subquery correlate sono una potente risorsa di SQL. In certi scenari, sono il modo più naturale per risolvere un problema. Se siete interessati a questo argomento, vi consiglio di leggere Correlated Subquery in SQL: A Beginner's Guide e Learn to Write a SQL Correlated Subquery in 5 Minutes.

Gli operatori ALL e ANY

Questa coppia di operatori lavora insieme agli operatori =, <>, >, >=, < e <=, aggiungendo maggiore espressività al linguaggio. Dato l'elevato numero di combinazioni possibili con ALL e ANY, ho incluso una tabella con gli usi più comuni di questi operatori:

Applichiamo questo operatore a un esempio reale. Supponiamo di voler promuovere tutti i "luoghi migliori del mondo" che si possono visitare con un biglietto inferiore a 1.000 dollari. Ogni "posto migliore" nella tabella best_10_places può avere molti biglietti possibili; a noi interessano solo i luoghi in cui almeno un biglietto costa meno di 1.000 dollari. La query è la seguente:

SELECT Place_name, Activity, Ranking_position
FROM best_10_places
WHERE 1000 > ANY (
    SELECT ticket_price
    FROM one_way_ticket
    WHERE city_destination = best_10_places.closest_city
  )

I risultati della precedente interrogazione sono mostrati di seguito. Potete andare nel posto migliore per qualsiasi attività (snorkeling, sci e trekking) per meno di 1.000 dollari!

I prossimi passi con le subquery

In questo articolo ho spiegato le subquery e mostrato diversi esempi di utilizzo. Tuttavia, questo argomento presenta numerose varianti, tra cui i diversi tipi di subquery e di operatori. Per avere una comprensione più completa delle subquery, suggerisco di seguire un corso online come quello di LearnSQL.it SQL Basics o di leggere gli altri articoli che ho citato.