Le select nidificate in SQL
Una interrogazione SELECT nidificata è composta da una select esterna e una o più select interne nella clausola WHERE.
Per risolvere l'interrogazione, in genere, il linguaggio SQL calcola prima la select interna.
Poi esegue la select esterna.
Nota. Non è comunque una regola generale. A seconda dell'elaborazione può accadere anche l'inverso. In alcune query il DBMS esegue prima la select più esterna e poi la select interna.
Spesso le select interne restituiscono più di un risultato. In questi casi la select nidificata devo costruirla aggiungendo
- le clausole ANY e ALL nelle condizioni di confronto.
- le clausole IN e NOT IN nelle condizioni di appartenenza.
- le clausole EXISTS e NOT EXISTS nelle condizioni di esistenza.
E' possibile usare anche gli operatori aggregati nella select interna (es. MIN, MAX, ecc. ).
Un esempio pratico
Ho la tabella Persone
Devo trovare le persone che hanno l'età superiore a quella dei residenti a Roma.
Posso farlo usando una select nidificata.
SELECT * FROM PERSONE
WHERE Età > ( SELECT Max(Età) FROM Persone WHERE Residenza = 'Roma' )
Il DBMS esegue la select interna trovando il valore massimo di Età delle persone residenti a Roma ossia 21.
SELECT * FROM PERSONE
WHERE Età > ( SELECT Max(Età) FROM Persone WHERE Residenza = 'Roma' )
Poi sostituisce il valore trovato ed esegue la select esterna
SELECT * FROM PERSONE
WHERE Età > ( 21 )
Il risultato è una tabella composta dalle righe con Età superiore a quelle dei residenti a Roma.
Questo risultato è stato ottenuto eseguendo due select a cascata sulla stessa tabella.
Le clausole ANY e ALL
Le clausole ANY e ALL sono usate nelle condizioni di confronto tra un attributo e il risultato di una select interna.
- ANY è soddisfatta se il confronto è vero almeno una volta con uno dei valori estratti dalla select interna.
- ALL è soddisfatta se il confronto è vero in tutti i valori estratti dalla select interna.
Esempio
Ho due tabelle Persone e Italia
Voglio trovare i nomi delle persone che hanno la residenza in Lombardia.
Utilizzo la clausola ANY dopo l'operatore di uguaglianza =.
SELECT Nome, Cognome, Residenza FROM PERSONE
WHERE Residenza = ANY ( SELECT Città FROM Italia WHERE Regione = 'Lombardia' )
Il DBMS esegue la query interna trovando le città "Bergamo" e "Milano", ossia le città della regione "Lombardia" nella tabella Italia.
Poi esegue la select esterna per trovare le righe della tabella Persone in cui la residenza è uguale a "Bergamo" o "Milano".
Il risultato della query è
Nota. Potrei ottenere lo stesso risultato con una condizione JOIN. La scelta tra una select nidificata e una join dipende soprattutto dalla leggibilità della query. In entrambi i casi il query optimizer del DBMS le trasforma per massimizzare l'efficienza.
Esempio 2
Ora voglio trovare le persone della tabella Persone che non risiedono in Lombardia.
In questo caso utilizzo la clausola ALL dopo l'operatore <> (diverso).
SELECT Nome, Cognome, Residenza FROM PERSONE
WHERE Residenza <> ALL ( SELECT Città FROM Italia WHERE Regione = 'Lombardia' )
Il DBMS esegue la query interna trovando le città "Bergamo" e "Milano".
Poi esegue la select esterna trovando le righe in cui la residenza è diversa da "Bergamo" e "Milano".
Il risultato della query è
Esempio 3
Ho la tabella Persone
Voglio trovare le persone della tabella che hanno l'età superiore a tutti quelli residenti a Roma.
Posso realizzare questo obiettivo usando la clausola ALL con l'operatore di maggioranza >.
SELECT * FROM PERSONE
WHERE Età > ALL ( SELECT Età FROM Persone WHERE Residenza = 'Roma' )
Il DBMS esegue la query interna trovando le età delle persone residenti a Roma (21 e 19).
Poi esegue la select esterna trovando tutte le righe con età superiore a 21 e 19.
In questo caso la select nidificata ha lavorato sulla stessa tabella.
Nota. Questa query non è scritta nel modo migliore possibile. La uso soltanto per spiegare meglio il funzionamento della clausola ALL. E' utile anche per evidenziare come una select nidificata possa ottenere gli stessi risultati degli operatori aggregati MIN e MAX senza usarli.
Le clausole IN e NOT IN
Le clausole IN e NOT IN sono usate nelle condizioni di appartenenza tra un attributo e il risultato di una select interna.
- IN è soddisfatta se il valore dell'attributo è presente nel risultato della select interna.
- NOT IN è soddisfatta se il valore dell'attributo non è presente nel risultato della select interna.
Esempio
Ho due tabelle Persone e Italia
Per trovare i nomi delle persone che hanno la residenza in Lombardia posso usare anche la clausola IN.
SELECT Nome, Cognome, Residenza FROM PERSONE
WHERE Residenza IN ( SELECT Città FROM Italia WHERE Regione = 'Lombardia' )
Il DBMS esegue la query interna trovando le città "Bergamo" e "Milano".
Poi esegue la select esterna per trovare le righe della tabella Persone in cui la Residenza è un valore appartenente al risultato della select interna.
Il risultato della query è
Nota. Anche in questo caso è possibile ottenere lo stesso risultato con una condizione JOIN. La scelta dipende dalla leggibilità della query. In entrambi i casi l'efficienza è migliorata dal query optimizer del DBMS.
Esempio 2
Per trovare le persone della tabella Persone che non risiedono in Lombardia posso usare la clausola NOT IN.
SELECT Nome, Cognome, Residenza FROM PERSONE
WHERE Residenza NOT IN ( SELECT Città FROM Italia WHERE Regione = 'Lombardia' )
Il DBMS esegue la select interna trovando le città "Bergamo" e "Milano".
Poi esegue la select esterna trovando le righe in cui la Residenza non è un valore appartenente al risultato della select interna.
Il risultato della query è
In questo caso il risultato è lo stesso ottenuto con le clausole ANY e ALL.
La clausola Exists e Not Exists
Nelle select nidificate la clausola EXISTS verifica se un valore esiste nella select interna.
La sua negazione è NOT EXISTS.
Esempio
Ho la tabella Persone
Voglio trovare le persone che hanno lo stesso cognome ma nome diverso.
Scrivo una query nidificata con la clausola EXISTS.
SELECT * FROM `persone` T1
WHERE EXISTS ( SELECT * FROM Persone T2
WHERE T2.cognome = T1.cognome AND T1.Nome <> T2.Nome )
Il DBMS legge una riga nella select esterna.
Poi verifica se esiste un'altra persona con lo stesso cognome ma nome diverso tramite la select interna.
Il risultato è il seguente
Nota. In questo caso viene eseguita prima la select esterna e poi la select interna, perché l'ordine di elaborazione richiede che sia prima letto il nome dalla select esterna. A ogni lettura di riga nell'interrogazione esterna corrisponde un intero ciclo dell'interrogazione interna.
E così via.
