Subquery in MySQL

Le subquery, dette anche query di query o query subselects, sono, perdonate il gioco di parole, delle query che utilizzano il risultato di altre query; esse vengono impiegate in particolar modo per ottimizzare e rendere più semplici le interrogazioni verso il DBMS e in molti casi risultano un'ottima alternativa alla realizzazione di JOIN tra tabelle.

Nel corso di questa breve trattazione descriveremo le varie tipologie di subquery e vedremo come esse possano essere utilizzate all'interno delle nostre interrogazioni.

Tipologie di subquery

Possiamo isolare tre principali tipologie di subquery, all'interno di essere troveremo ulteriori diversificazioni ma per il momento ci limiteremo ad elencare le categorie principali.

• Predicate subqueries: svolgono la funzione di estendere i costrutti logici basati sull'utilizzo della clausola WHERE
• Scalar subqueries: sono query indipendenti in grado di restituire un unico valore e possono essere utilizzate in associazione ad un valore scalare.
• Table subqueries: con questa categorie si identificano interrogazioni annidate nella clausola FROM

Per alcuni esempi contenuti in questo articolo ci baseremo su due semplici tabelle:

 
mysql> SELECT * FROM Aziende;
+------+---------------+----------+
| ID_a | Azienda       | Sede     |
+------+---------------+----------+
| 1    | Pino Spa      | Rieti    |
| 2    | Lino Spa      | Napoli   |
| 3    | Gino Spa      | Cremona  |
| 4    | Zino Spa      | Nuoro    |
| 5    | Fino Spa      | Rieti    |
| 6    | Mino Spa      | Rieti    |
+------+---------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM Prodotti;
+---------+-------------+------+--------+
| ID_p    | Prodotto    | ID_a | Prezzo |
+---------+-------------+------+--------+
| 111     | Orologi     | 1    | 600,00 |
| 112     | Pantaloni   | 2    | 850,00 |
| 113     | Maglioni    | 2    | 130,00 |
| 114     | Camice      | 3    | 100,00 |
| 115     | Cravatte    | 5    | 900,00 |
+---------+-------------+------+--------+
5 rows in set (0.00 sec)

Sarà semplice osservare come esista una relazione tra le due tabelle: i prodotti elencati nella seconda fanno riferimento agli identificativi (ID_a) delle aziende elencate nella prima.

Utilizzare le Predicate Subqueries

Prima di procedere è bene fare un accenno alla sintassi delle subquery, queste devono essere infatti espresse sempre all'interno di parentesi tonde in modo da differenziarsi dalla query nativa e di essere utilizzate come parametro da passare alle clausole.

Come abbiamo sottolineato in precedenza le Predicate subqueries estendono le funzionalità della clausola WHERE, in pratica grazie ad esse possiamo passare un'intera interrogazione come argomento per una clausola.

Di particolare rilievo è l'utilizzo associato delle subquery con i costrutti IN ("IN Subquery") e NOT IN; vediamo subito un esempio:

mysql> SELECT * FROM Aziende WHERE ID_a IN
    -> (SELECT ID_a FROM Prodotti);
+------+---------------+----------+
| ID_a | Azienda       | Sede     |
+------+---------------+----------+
| 1    | Pino Spa      | Rieti    |
| 2    | Lino Spa      | Napoli   |
| 3    | Gino Spa      | Cremona  |
| 5    | Fino Spa      | Rieti    |
+------+---------------+----------+
4 rows in set (0.00 sec)

In pratica, con essa non abbiamo fatto altro che selezionare dalla prima tabella tutte le aziende e i relativi record che hanno la caratteristica di presentare il loro identificativo anche nella tabella dei prodotti.

Al contrario, utilizzando, NOT IN avremo dal DBMS una risposta simile alla seguente:

mysql> SELECT * FROM Aziende WHERE ID_a NOT IN
    -> (SELECT ID_a FROM Prodotti);
+------+---------------+----------+
| ID_a | Azienda       | Sede     |
+------+---------------+----------+
| 4    | Zino Spa      | Nuoro    |
| 6    | Mino Spa      | Rieti    |
+------+---------------+----------+
2 rows in set (0.00 sec)

In questo caso infatti abbiamo richiesto di visualizzare soltanto i record relativi a quelle aziende il cui identificativo non è riportato nella tabella Prodotti.

Lo stesso risultato ottenuto nel primo esempio potrebbe essere conseguito associando una subquery all'operatore di confronto ANY; le subquery e gli operatori di confronto possono essere utilizzati insieme ("Quantified subqueries") con una sintassi come la seguente:

Valore {=|>|<|>=|<=|<>} {ANY | ALL | SOME} (subquery)

Quindi, tornando al primo esempio, avremmo potuto ottenere lo stesso risultato con un'interrogazione simile alla seguente:

mysql> SELECT * FROM Aziende WHERE ID_a = ANY(SELECT ID_a FROM Prodotti);

Stesso discorso per quanto riguarda l'utilizzo associato di subquery e operatore EXISTS ("Exists Subqueries"), infatti, anche in questo caso avremmo potuto ottenere lo stesso risultato del primo esempio grazie ad una subquery del tipo:

mysql> SELECT * FROM Aziende
WHERE EXISTS
(SELECT * FROM Prodotti WHERE Aziende.ID_a=Prodotti.ID_a);

Subquery che restituiscono un singolo valore

Le Scalar subqueries sono interrogazioni che restituiscono un singolo valore (detto appunto valore scalare), questo tipo di query possono essere utilizzate in qualsiasi momento sia disponibile un valore di colonna.

Si tratta della tipologia di subquery più semplice in quanto, come possiamo notare dal seguente esempio, consiste in un singolo operando:

mysql> CREATE TABLE tbl (prezzo INT, codice VARCHAR(4) NOT NULL);
mysql> INSERT INTO tbl VALUES(20, 'a123');
mysql> SELECT (SELECT codice FROM tbl);

L'output conseguente sarà semplicemente "a123", nel caso in cui invece la tabella fosse stata vuota, l'esito della subquery sarebbe stato NULL.

Per comprendere il significato del concetto di "restituire un solo valore", possiamo operare la seguente interrogazione a carico delle nostra tabelle Aziende e Prodotti:

mysql> SELECT (SELECT ID_a FROM Prodotti WHERE ID_a=2) FROM aziende;

Il risultato sarà una notifica di errore:

mysql> ERROR: Subquery returns more than 1 row

Inutile quindi tentare di estrarre dalla tabella Prodotti i due valori relativi all'identificativo ID_a=2 perché in questo caso i record coinvolti sarebbero più di uno e una Scalar subquery non è in grado di accettare un risultato del genere.

Vi sono alcuni casi in cui le Scalar subqueries non possono essere utilizzate, si tratta delle istruzioni o delle espressioni in cui è richiesto un cosiddetto "literal value", cioè un valore che và utilizzato esattamente "così com'è", sia esso stringa o numerico; è il caso per esempio della clausola LIMIT che richiede un valore numerico intero letterale o del costrutto LOAD DATA INFILE che richiede invece un parametro letterale di tipo stringa.

Un accenno và fatto anche riguardo alla gestione delle priorità per le subquery scalari, partiamo con un esempio:

mysql> CREATE TABLE tbl_a (prezzo INT);
mysql> INSERT INTO tb_a VALUES (10);
mysql> CREATE TABLE tbl_b (prezzo INT);
mysql> INSERT INTO tbl_b VALUES (20);

Abbiamo creato due tabelle provviste di una struttura identica, entrambe infatti contengono un unico campo chiamato prezzo che accetta unicamente valori numerici interi; ora proviamo ad eseguire la seguente istruzione:

mysql> SELECT (SELECT prezzo FROM tbl_b) FROM tbl_a;

il risultato sarà "20", cioè pari al valore numerico contenuto nell'unico record presente all'interno della tabella "tbl_b" per il campo "prezzo", ed è questo il valore che il DBMS considera valido per la soddisfazione della richiesta.

Table Subqueries e clausola FROM

La sintassi delle Table subqueries è la seguente:

mysql> SELECT valori FROM (subquery) [AS] nome_tabella

L'utilizzo di AS non è casuale, infatti all'interno della clausola FROM ogni tabella dovrà avere un nome e ogni colonna all'interno della subquery SELECT dovrà avere un nome univoco; facciamo un esempio in cui innanzitutto procediamo con la creazione di una tabella:

mysql> CREATE TABLE tbl (prezzo INT, codice VARCHAR(2), sconto FLOAT);
mysql> INSERT INTO tbl VALUES (10,'a',1.0);
mysql> INSERT INTO tbl VALUES (30,'b',2.5);

In secondo luogo eseguiamo una subquery di questo tipo:

SELECT sub1,sub2,sub3
 FROM (SELECT prezzo AS sub1, codice AS sub2, sconto*2 AS sub3 FROM tb1) AS sub
WHERE sub1 > 10;

l'output sarà "30, 'b', 5.0", i valori del secondo record inserito in tabella vanno quindi a popolare un alias di tabella i cui campi vengono definiti all'interno di un parametro subquery passato come argomento alla clausola FROM.

Subquery ed errori

Purtroppo con le subquery non è possibile fare tutto ed utilizzare la stessa sintassi che può essere sfruttata per le query principali. Riportiamo di seguito alcune possibili notifiche dovute ad errori nell'esecuzione delle interrogazioni e le relative cause:

Message = "This version of MySQL does not yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"

in questo caso la versione di MySQL non prevede l'utilizzo delle clausole elencate all'interno delle subquery, alcune sintassi non vengono supportate e non sarà quindi possibile eseguire interrogazioni come le seguenti:

mysql> SELECT * FROM tbl_a WHERE prezzo IN (SELECT codice FROM tbl_b ORDER BY prezzo LIMIT 1);

Vi è poi il caso in cui il numero di colonne proposte in subquery non si accettabile, l'esecuzione di un'interrogazione del genere

mysql> SELECT (SELECT col_a, col_b FROM tbl_b) FROM tbl_a;

produrrà la seguente notifica di errore:

Message = "Operand should contain 1 column(s)" 

Si tratta di un caso molto simile a quello indicato in precedenza per le subquery scalari dove la notifica riguarda invece il coinvolgimento nell'elaborazione di più record all'interno di una stessa interrogazione.

Infine, citiamo tra i possibili errori quello relativo all'utilizzo scorretto di un nome di tabella; eseguendo per esempio una query come la seguente:

mysql> UPDATE tbl_a SET col_b = (SELECT MAX(col_a) FROM tbl_a);

il DBMS ci invierà questa notifica:

Message = "You can't specify target table 'x' for update in FROM clause"

questo accade perché se da una parte è possibile utilizzare una subquery all'interno di un'istruzione di UPDATE (così come per DELETE), dall'altra non è possibile coinvolgere la stessa tabella in una subquery FROM e in un'azione di UPDATE.