Subquery SQL

L'articolo descrive cos'è una subquery e come si presentano queste utili istruzioni. Verranno illustrati esempi di base con gli operatori IN, EXISTS, ANY e ALL, si analizzeranno le subquery nelle clausole FROM e WHERE e si analizzerà la differenza tra subquery correlate e nidificate.

Innanzitutto, iniziamo con un database di esempio. Per presentare alcune di queste affermazioni è necessario disporre di una tabella di esempio e riempirla con alcuni dati.

Che cos'è una subquery?

Una subquery è un'istruzione SELECT con un'altra istruzione SQL, come nell'esempio seguente.

SELECT *
FROM product
WHERE id IN (
  SELECT product_id
  FROM provider_offer
  WHERE provider_id = 156
);

Le subquery sono ulteriormente classificate come subquery correlate o subquery annidate. Di solito sono costruite in modo da restituire:

1. una tabella

   SELECT MAX(average.average_price)
   FROM (
     SELECT
       product_category,
       AVG(price) AS average_price
     FROM product
     GROUP BY product_category
   ) average;
   

2. o un valore

   SELECT id
   FROM purchase
   WHERE value > (
     SELECT AVG(value)
     FROM purchase
   );
   

Volete saperne di più sulle subquery SQL? Date un'occhiata alla nostra serie We Learn SQL su Youtube. Ricordatevi di iscrivervi al nostro canale.

Subquery annidate

Lesubquery annidate sono subquery che non dipendono da una query esterna. In altre parole, entrambe le query di una subquery annidata possono essere eseguite come query separate.

Questo tipo di subquery può essere usato quasi ovunque, ma di solito assume uno dei seguenti formati:

SELECT
FROM
WHERE [NOT] IN (subquery)

SELECT *
FROM client
WHERE city IN (
  SELECT city
  FROM provider
);

La subquery di esempio restituisce tutti i clienti che provengono dalla stessa città dei fornitori di prodotti.
L'operatore IN controlla se il valore è presente nella tabella e recupera le righe corrispondenti.

SELECT
FROM
WHERE expression comparison_operator [ANY| ALL] (subquery)

Subquery con l'operatore ALL

L'operatore ALL confronta un valore con tutti i valori della tabella dei risultati.

Ad esempio, la seguente query restituisce tutti i modelli e i produttori di biciclette che hanno un prezzo superiore a quello delle cuffie più costose.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ALL(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Subquery simile ma con l'operatore ANY:

Subquery con operatore ANY

L'operatore ANY confronta un valore con ogni valore di una tabella e valuta se il risultato di una query interna contiene o meno almeno una riga.

La seguente query restituisce tutti i modelli e i produttori di biciclette che hanno un prezzo superiore ad almeno una delle cuffie.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ANY(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

È anche possibile annidare una sottoquery in un'altra aubquery. Ad esempio:

Subquery annidata in un'altra subquery utilizzando l'operatore IN

Questa query restituisce i produttori e i modelli di biciclette presenti nelle offerte dei fornitori DAGLI USA.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND id IN (
    SELECT distinct product_id
    FROM provider_offer
    WHERE provider_id IN (
      SELECT id
      FROM provider
      WHERE country = 'USA'
    )
  );

Lo stesso si può fare utilizzando i join.

SELECT product.producer, product.model
FROM product, provider_offer, provider
WHERE provider_offer.product_id = product.id
  AND provider_offer.provider_id = provider.id
  AND product_category = 'bike'
  AND provider.country = 'USA';

Subquery correlate

Lesottoquery sono correlate quando la query interna e quella esterna sono interdipendenti, cioè quando la query esterna è una query che contiene una sottoquery e la stessa sottoquery è una query interna. Gli utenti che conoscono i concetti di programmazione possono paragonarla a una struttura ad anello annidata.

Cominciamo con un semplice esempio.

La query interna calcola il valore medio e lo restituisce. Nella clausola WHERE della query esterna, filtriamo solo gli acquisti che hanno un valore superiore al valore restituito dalla query interna.

Subquery correlate nella clausola WHERE

SELECT id
FROM purchase p1
WHERE date > '2013-07-15'
  AND value > (
    SELECT AVG(value)
    FROM purchase p2
    WHERE p1.date = p2.date
  );

La query restituisce gli acquisti successivi al 15/07/2014 con un prezzo totale superiore al valore medio Dallo stesso giorno.

L'esempio equivalente, ma con tabelle unite.

SELECT  p1.id
FROM purchase p1, purchase p2
WHERE p1.date = p2.date
  AND p1.date > '2013-07-15'
GROUP BY p1.id
HAVING p1.value > AVG(p2.value);

Questo esempio può anche essere scritto come un'istruzione SELECT con una subquery correlata in una clausola FROM.

La sottoquery restituisce la tabella che contiene il valore medio di ogni acquisto per ogni giorno. Uniamo questo risultato alla tabella Acquisti sulla colonna 'data' per verificare la condizione data > '15/07/2014′.

SELECT id
FROM
  purchase,
  (
    SELECT date, AVG(value) AS average_value
    FROM purchase
    WHERE date > '2013-07-15'
    GROUP BY date
  ) average
WHERE purchase.date  = average.date
  AND purchase.date  > '2013-07-15'
  AND purchase.value > average.average_value;

In genere, questo tipo di subquery dovrebbe essere evitato perché gli indici non possono essere utilizzati su una tabella temporanea in memoria.

Subquery con EXISTS

SELECT
FROM
WHERE [NOT] EXISTS (subquery)

L'operatore EXISTS controlla se la riga dalla subquery corrisponde a una riga della query esterna. Se non ci sono dati corrispondenti, l'operatore EXISTS restituisce FALSE.

Questa query restituisce tutti i clienti che hanno ordinato dopo il 10/07/2013.

SELECT id, company_name
FROM client
WHERE EXISTS(
  SELECT *
  FROM purchase
  WHERE client.id = purchase.client_id
  WHERE date > '2013-07-10'
);

Quando si utilizza una sottoquery, l'ottimizzatore di query esegue ulteriori passaggi prima di utilizzare i risultati dalla sottoquery. Se una query che contiene una sottoquery può essere scritta utilizzando una join, è meglio farlo in questo modo. I join di solito consentono all'ottimizzatore di query di recuperare i dati in modo più efficiente.

Limitazioni delle subquery

L'uso delle subquery presenta alcune limitazioni:

• In Oracle è possibile annidare fino a 255 livelli di subquery in una clausola WHERE.
• In SQL Server è possibile annidare fino a 32 livelli.