5 esempi di subquery SQL

Le subquery SQL sono strumenti fondamentali per comunicare efficacemente con i database relazionali. In questo articolo fornisco cinque esempi di subquery che dimostrano come utilizzare le subquery scalari, multiriga e correlate nelle clausole WHERE, FROM/JOIN e SELECT.

Una subquery, o query annidata, è una query inserita all'interno di un'altra query SQL. Quando si richiedono informazioni da un database, può essere necessario includere una subquery nelle clausole SELECT, FROM, JOIN, o WHERE. Tuttavia, è possibile utilizzare le subquery anche quando si aggiorna il database (ad esempio nelle istruzioni INSERT, UPDATE e DELETE ).

Esistono diversi tipi di subquery SQL:

• Lesubquery scalari restituiscono un singolo valore, o esattamente una riga e una colonna.
• Le subquery a più righe restituiscono o:
  • Una colonna con più righe (cioè un elenco di valori), oppure
  • più colonne con più righe (ad esempio, tabelle).
• Subquery correlate, in cui la query interna si basa su informazioni ottenute dalla query esterna.

Per saperne di più sui diversi tipi di subquery SQL, si consiglia di concentrarsi sugli esempi. Come tutti sappiamo, è sempre più facile afferrare nuovi concetti con casi d'uso reali. Quindi iniziamo.

5 esempi di subquery in SQL

Supponiamo di gestire una galleria d'arte. Abbiamo un database con quattro tabelle: paintings, artists, collectors, e sales. Di seguito è possibile vedere i dati memorizzati in ciascuna tabella.

Esploriamo ora questi dati utilizzando query SQL con diversi tipi di subquery.

Esempio 1 - Subquery scalare

Cominciamo con un esempio semplice: Vogliamo elencare i dipinti che hanno un prezzo superiore alla media. In pratica, vogliamo ottenere i nomi dei dipinti insieme ai prezzi elencati, ma solo per quelli che costano più della media. Ciò significa che dobbiamo prima trovare il prezzo medio; è qui che entra in gioco la subquery scalare:

SELECT name, listed_price
FROM paintings
WHERE listed_price > (
    SELECT AVG(listed_price)
    FROM paintings
);

La nostra subquery si trova nella clausola WHERE, dove filtra l'insieme dei risultati in base al prezzo di listino. Questa subquery restituisce un singolo valore: il prezzo medio per quadro della nostra galleria. Ogni prezzo quotato viene confrontato con questo valore e solo i quadri con un prezzo superiore alla media vengono inseriti nell'output finale:

Se tutto questo vi sembra un po' complicato, vi consigliamo di consultare il nostro corso interattivo SQL Basics e rispolverare le vostre conoscenze essenziali di SQL.

Esempi 2 - Subquery a più righe

Esaminiamo ora le subquery che restituiscono una colonna con più righe. Queste sottoquery sono spesso incluse nella clausola WHERE per filtrare i risultati della query principale.

Supponiamo di voler elencare tutti i collezionisti che hanno acquistato quadri dalla nostra galleria. Possiamo ottenere il risultato necessario utilizzando una subquery a più righe. In particolare, possiamo utilizzare una query interna per elencare tutti gli ID dei collezionisti presenti nella tabella sales che corrispondono ai collezionisti che hanno effettuato almeno un acquisto presso la nostra galleria. Poi, nella query esterna, chiediamo il nome e il cognome di tutti i collezionisti il cui ID è presente nell'output della query interna. Ecco il codice:

SELECT first_name, last_name
FROM collectors
WHERE id IN (
    SELECT collector_id
    FROM sales
);

Ecco l'output:

È interessante notare che si può ottenere lo stesso risultato senza una subquery utilizzando una INNER JOIN (o semplicemente JOIN). Questo tipo di join restituisce solo i record che si trovano in entrambe le tabelle. Quindi, se uniamo le tabelle collectors e la tabella sales otterremo un elenco di collezionisti con i record corrispondenti nella tabella sales tabella. Nota: ho usato anche la parola chiave DISTINCT per rimuovere i duplicati dall'output.

Ecco la query:

SELECT DISTINCT collectors.first_name, collectors.last_name
FROM collectors
JOIN sales
  ON collectors.id = sales.collector_id;

Per ulteriori informazioni sulla scelta di una subquery rispetto a una JOIN, consultare il nostro blog.

Esempio 3 - Subquery a più righe con più colonne

Quando una subquery restituisce una tabella con più righe e più colonne, di solito la subquery si trova nella clausola FROM o JOIN. Ciò consente di ottenere una tabella con dati non disponibili nel database (ad esempio, dati raggruppati) e di unire questa tabella con un'altra del database, se necessario.

Supponiamo di voler vedere l'importo totale delle vendite per ogni artista che ha venduto almeno un quadro nella nostra galleria. Si può iniziare con una sottoquery che attinge alla tabella sales e calcola l'importo totale delle vendite per ogni ID artista. Poi, nella query esterna, combiniamo queste informazioni con i nomi e i cognomi degli artisti per ottenere l'output richiesto:

SELECT
  artists.first_name, 
  artists.last_name, 
  artist_sales.sales
FROM artists
JOIN (
    SELECT artist_id, SUM(sales_price) AS sales
    FROM sales
    GROUP BY artist_id
  ) AS artist_sales
  ON artists.id = artist_sales.artist_id;

Assegniamo un alias significativo all'output della nostra subquery (artist_sales). In questo modo, possiamo facilmente fare riferimento ad esso nella query esterna, quando selezioniamo la colonna da questa tabella e quando definiamo la condizione di join nella clausola ON. Nota: i database emettono un errore se non si fornisce un alias per l'output della subquery.

Ecco il risultato della query:

In questo modo, con una breve query SQL, siamo stati in grado di calcolare le vendite totali di ogni artista sulla base dei dati grezzi di una tabella (sales) e poi unire questo risultato con i dati di un'altra tabella (artists).

Le subquery possono essere molto potenti quando si devono combinare informazioni provenienti da più tabelle. Vediamo cos'altro si può fare con le subquery.

Esempio 4 - Subquery correlata

L'esempio seguente dimostra come le subquery:

• possono essere utilizzate nella clausola SELECT e
• possono essere correlate (cioè la query principale o esterna si basa sulle informazioni ottenute dalla query interna).

Per ogni collezionista, vogliamo calcolare il numero di dipinti acquistati nella nostra galleria. Per rispondere a questa domanda, possiamo utilizzare una sottoquery che conta il numero di dipinti acquistati da ciascun collezionista. Ecco l'intera query:

SELECT
  first_name, 
  last_name,
  (
    SELECT count(*) AS paintings
    FROM sales
    WHERE collectors.id = sales.collector_id
  )
FROM collectors;

Si noti come la query interna in questo esempio venga effettivamente eseguita per ogni riga della tabella collectors:

• La sottoquery è inserita nella clausola SELECT perché vogliamo avere una colonna aggiuntiva con il numero di dipinti acquistati dal collezionista corrispondente.
• Per ogni record della tabella collectors la subquery interna calcola il numero totale di dipinti acquistati da un collezionista con l'ID corrispondente.

Ecco l'output:

Come si vede, l'output della sottoquery (cioè il numero di dipinti) è diverso per ogni record e dipende dall'output della query esterna (cioè il collezionista corrispondente). Si tratta quindi di una sottoquery correlata.

Consultate questa guida se volete imparare a scrivere subquery correlate in SQL. Per ora, vediamo un altro esempio di subquery correlata.

Esempio 5 - Subquery correlata

Questa volta vogliamo mostrare i nomi e i cognomi degli artisti che hanno registrato zero vendite nella nostra galleria. Proviamo a realizzare questo compito utilizzando una sottoquery correlata nella clausola WHERE:

SELECT first_name, last_name
FROM artists
WHERE NOT EXISTS (
  SELECT *
  FROM sales
  WHERE sales.artist_id = artists.id
);

Ecco cosa succede in questa query:

• La query esterna elenca le informazioni di base sugli artisti, verificando innanzitutto se ci sono record corrispondenti nella tabella dei dipinti. sales
• La query interna cerca i record che corrispondono all'ID dell'artista attualmente controllato dalla query esterna.
• Se non ci sono record corrispondenti, il nome e il cognome dell'artista corrispondente vengono aggiunti all'output:

Nel nostro esempio, abbiamo un solo artista che non ha ancora venduto nulla. Speriamo che ne ottenga presto una.

È ora di esercitarsi con le subquery SQL!

In questo articolo ho illustrato diversi esempi di subquery SQL per fornire una comprensione generale di come le subquery possono essere sfruttate in SQL. Tuttavia, spesso le espressioni di tabella comuni (CTE) possono fare meglio delle subquery.

Se volete esercitarvi con le subquery SQL e con altri argomenti fondamentali dell'SQL, provate il nostro SQL Basics corso interattivo. Include 129 sfide di codifica per interrogare più tabelle, aggregare e raggruppare dati, unire tabelle, scrivere subquery e molto altro ancora.

Volete diventare maestri di SQL? Date un'occhiata alla nostra traccia di apprendimento SQL from A to Z. Questo percorso va oltre le nozioni di base e comprende 7 corsi interattivi che coprono le funzioni SQL standard, il linguaggio di manipolazione dei dati SQL (DML), i report SQL di base, le funzioni finestra, le espressioni di tabella comuni (CTE) e le estensioni GROUP BY.

Grazie per la lettura e buon apprendimento!