Le query annidate in SQL

Nelle lezioni precedenti abbiamo visto come utilizzare SELECT, WHERE, JOIN, GROUP BY e altre clausole per costruire query sempre più complete.

Tuttavia, talvolta può essere necessario eseguire una query utilizzando il risultato di un'altra query. In questi casi possiamo utilizzare le query annidate, chiamate anche subquery.

Una subquery è quindi una query inserita all'interno di un'altra query. Il risultato della query interna viene utilizzato dalla query esterna per recuperare o filtrare i dati.

Cosa sono le query annidate

Una query annidata è una SELECT (che abbiamo visto nella lezione sulla selezione dei dati) inserita all'interno di un'altra istruzione SQL.

Per esempio, supponiamo di voler trovare i prodotti che hanno un prezzo superiore al prezzo medio di tutti i prodotti. Potremmo prima calcolare il prezzo medio:

SELECT AVG(prezzo) FROM prodotti;

Poi, possiamo utilizzare il risultato per filtrare i prodotti:

SELECT nome, prezzo
FROM prodotti
WHERE prezzo > 100;

Ma il valore 100 potrebbe cambiare se, nel frattempo, cambiano i dati presenti nella tabella.

Possiamo invece inserire la prima query direttamente nella seconda:

SELECT nome, prezzo FROM prodotti
WHERE prezzo > (
    SELECT AVG(prezzo)
    FROM prodotti
);

La query interna SELECT AVG(prezzo) calcola il prezzo medio. La query esterna utilizza poi quel valore nella condizione in WHERE. In questo modo possiamo confrontare i dati con un valore calcolato dinamicamente dal database.

Subquery scalari

Una subquery scalare è una subquery che restituisce un solo valore, cioè una sola riga e una sola colonna.

Per esempio, questa query restituisce un solo valore:

SELECT AVG(prezzo) FROM prodotti;

Possiamo quindi utilizzarlo in una condizione della query esterna. Quando utilizziamo operatori di confronto come =, >, <, >=, <= o <>, la subquery deve restituire un singolo valore (scalare). Per subquery che restituiscono più valori, esistono operatori specifici come IN, ANY e ALL, che vedremo tra poco.

Ad esempio, supponiamo di voler trovare il prodotto con il prezzo massimo:

SELECT nome, prezzo FROM prodotti
WHERE prezzo = (
    SELECT MAX(prezzo)
    FROM prodotti
);

La subquery SELECT MAX(prezzo) restituisce un solo valore, cioè il prezzo più alto. La query esterna cerca quindi i prodotti il cui prezzo è uguale a quel valore. Se più prodotti hanno lo stesso prezzo massimo, verranno restituiti tutti.

Subquery in SELECT

Una subquery scalare può essere utilizzata anche direttamente in SELECT.

Ad esempio:

SELECT nome, prezzo,
    (SELECT AVG(prezzo) FROM prodotti) AS prezzo_medio
FROM prodotti;

La subquery viene utilizzata per ottenere il prezzo medio e questo valore viene mostrato accanto a ogni prodotto.

Query annidate con IN

Non tutte le subquery restituiscono un singolo valore. Possiamo avere una subquery che restituisce una colonna con più righe.

Per esempio, supponiamo di avere le seguenti tabelle:

Possiamo voler trovare tutti i clienti che hanno effettuato almeno un ordine.

Utilizziamo una subquery che recuperi gli id degli utenti presenti in ordini. Poi, possiamo usare questo insieme di valori con IN (che abbiamo visto tra le condizioni avanzate): 

SELECT nome, email FROM clienti
WHERE id IN (
    SELECT cliente_id
    FROM ordini
);

La query interna restituisce quindi un elenco di id e IN verifica se l'ID di ogni cliente è presente in quell'elenco.

Usare NOT IN

Possiamo utilizzare NOT IN per ottenere l'effetto opposto. Per esempio, per trovare i clienti che non hanno effettuato ordini:

SELECT nome, email FROM clienti
WHERE id NOT IN (
    SELECT cliente_id
    FROM ordini
);

In questo caso vengono mantenuti i clienti il cui id non compare tra quelli restituiti dalla subquery.

Nota: NOT IN può produrre risultati inattesi quando la subquery può restituire valori NULL. In questi casi, spesso è più sicuro utilizzare NOT EXISTS, che vedremo nella lezione successiva.

Operatori ANY e ALL

Le subquery che restituiscono più valori possono essere utilizzate anche con ANY e ALL. Questi operatori vengono utilizzati insieme a un operatore di confronto.

Operatore ANY

ANY verifica se il confronto è vero per almeno uno dei valori restituiti dalla subquery.

Prendiamo questo esempio:

SELECT nome, prezzo FROM prodotti
WHERE prezzo > ANY (
    SELECT prezzo
    FROM prodotti
    WHERE categoria = 'Accessori'
);

La condizione della query esterna è vera se il prezzo del prodotto è maggiore di almeno uno dei prezzi restituiti dalla subquery. In pratica, equivale a confrontare il valore con il più basso tra quelli restituiti quando si utilizza >.

Operatore ALL

ALL richiede che il confronto sia vero per tutti i valori restituiti dalla subquery.

In questo esempio, il prezzo deve essere maggiore di tutti i prezzi restituiti dalla subquery:

SELECT nome, prezzo FROM prodotti
WHERE prezzo > ALL (
    SELECT prezzo
    FROM prodotti
    WHERE categoria = 'Accessori'
);

Subquery in FROM

Una subquery può essere inserita anche nella clausola FROM. In questo caso il risultato della subquery viene trattato dalla query esterna come una tabella temporanea. Questo tipo di subquery, in gergo, viene chiamato derived table.

Per esempio, possiamo prima calcolare il numero di prodotti per categoria:

SELECT categoria, COUNT(*) AS numero_prodotti
FROM prodotti
GROUP BY categoria;

Possiamo poi utilizzare questo risultato come una tabella all'interno di un'altra query:

SELECT categoria, numero_prodotti
FROM (
    SELECT categoria, COUNT(*) AS numero_prodotti
    FROM prodotti
    GROUP BY categoria
) AS categorie;

La query interna crea una tabella chiamata categorie, e la query esterna tratta questo risultato come se fosse una normale tabella.