SQL - Sottointerrogazioni
una delle ragioni che rendono SQL un linguaggio
potente è la possibilità di esprimere interrogazioni più
complesse in termini di interrogazioni più semplici,
tramite il meccanismo delle subqueries
(sottointerrogazioni)
la clausola WHERE di una query (detta query esterna)
può infatti contenere un'altra query (detta subquery)
la subquery viene usata per determinare uno o più
valori da usare come valori di confronto in un predicato
della query esterna
121 Linguaggio SQL
SQL - Sottointerrogazioni
esempio: si vuole elencare tutti gli impiegati che
hanno la stessa mansione dell'impiegato di nome
Gianni
SELECT Nome, Mansione FROM Impiegati
WHERE Mansione = (SELECT Mansione FROM Impiegati
WHERE Nome = 'Gianni');
– HP: Nome deve essere chiave
– la subquery restituisce come valore 'ingegnere’
– la query esterna determina quindi tutti gli impiegati che sono
ingegneri
– il risultato è
122 {Rossi, Neri, Dare, Adami, Gianni, Milli}
Linguaggio SQL
SQL - Sottointerrogazioni
tramite il meccanismo delle subqueries è possibile
esprimere queries anche più complesse
esempio: si vuole elencare tutti gli impiegati che hanno
uno stipendio superiore alla media degli stipendi di tutti
gli impiegati
SELECT Nome, Stipendio FROM Impiegati
WHERE Stipendio > (SELECT AVG(Stipendio)
FROM Impiegati);
123 Linguaggio SQL
SQL - Sottointerrogazioni
risultato Nome Stipendio
Rosi 2975,00
Blacchi 2850,00
Neri 2450,00
Dare 2000,00
Gianni 1950,00
Verdi 3000,00
è possibile per una subquery avere al suo interno
un'altra subquery, predicati di join, e tutti i predicati visti
le subqueries possono essere usate anche all'interno
dei comandi di manipolazione dei dati (Insert, Delete,
Update)
li vedremo più avanti
124 Linguaggio SQL
SQL - Sottointerrogazioni
negli esempi visti, le subqueries restituiscono un solo
valore
tali subqueries sono note come subqueries scalari
se una subquery scalare restituisce più di una tupla si
genera un errore a run-time
una particolarità delle subqueries scalari è che se
nessuna tupla verifica la subquery, viene restituito il
valore NULL
se si vuole invece utilizzare una subquery che
restituisce più valori (table subquery) è necessario
specificare come i valori restituiti devono essere usati
nella clausola WHERE
125 Linguaggio SQL
SQL - Sottointerrogazioni
a tale scopo vengono usati i quantificatori ANY ed ALL,
che sono inseriti tra l'operatore di confronto e la
subquery (esiste anche SOME, sinonimo di ANY)
esempio: determinare lo stipendio, la mansione, il
nome e il numero di dipartimento degli impiegati che
guadagnano più di almeno un impiegato del
dipartimento 30
SELECT Stipendio, Mansione, Nome, Dip#
FROM Impiegati WHERE Stipendio > ANY (SELECT Stipendio
FROM Impiegati
WHERE Dip#=30);
126 Linguaggio SQL
SQL - Sottointerrogazioni
– risultato
Stipendio Mansione Nome Dip#
3000,00 dirigente Verdi 10
2975,00 dirigente Rosi 20
2850,00 dirigente Blacchi 30
2450,00 ingegnere Neri 10
2000,00 ingegnere Dare 10
1950,00 ingegnere Gianni 30
1600,00 ingegnere Rossi 20
1500,00 tecnico Turni 30
1300,00 ingegnere Milli 10
1100,00 ingegnere Adami 10
se si usa il quantificatore ALL, la query restituisce gli
impiegati il cui stipendio è maggiore di tutti i valori
restituiti dalla subquery
127 Linguaggio SQL
SQL - Sottointerrogazioni
esempio: determinare lo stipendio, la mansione, il
nome e il numero di dipartimento degli impiegati che
guadagnano più di tutti gli impiegati del dipartimento
30
SELECT Stipendio, Mansione, Nome, Dip#
FROM Impiegati WHERE Stipendio > ALL (SELECT Stipendio
FROM Impiegati
WHERE Dip#=30);
– risultato Stipendio Mansione Nome Dip#
3000,00 dirigente Verdi 10
2975,00 dirigente Rosi 20
128 Linguaggio SQL
SQL - Sottointerrogazioni
sono definite le seguenti abbreviazioni per ANY ed ALL
– IN equivalente ad = ANY
– NOT IN equivalente a ≠ ALL
esempio: elencare il nome e la mansione degli
impiegati del dipartimento 10 che hanno la stessa
mansione di un qualche impiegato del dipartimento 30
SELECT Nome, Mansione FROM Impiegati
WHERE Dip#=10 AND Mansione IN
(SELECT Mansione FROM Impiegati
WHERE Dip#=30);
129 Linguaggio SQL
SQL - Sottointerrogazioni
esempio (2): elencare il nome e la mansione degli
impiegati del dipartimento 10 che hanno una qualche
mansione non presente nel dipartimento 30
SELECT Nome, Mansione FROM Impiegati
WHERE Dip#=10 AND Mansione NOT IN
(SELECT Mansione FROM Impiegati
WHERE Dip#=30);
– risultato risultato (2)
Mansione Nome Mansione Nome
dirigente Verdi tecnico Andrei
ingegnere Dare tecnico Bianchi
ingegnere Milli tecnico Turni
segretaria Martini
130 Linguaggio SQL
SQL - Sottointerrogazioni
è inoltre possibile selezionare più di una colonna
tramite una sottointerrogazione; in tal caso è
necessario apporre delle parentesi alla lista delle
colonne a sinistra dell'operatore di confronto
esempio: si vuole elencare gli impiegati con la stessa
mansione e stipendio di Martini
SELECT Nome FROM Impiegati
WHERE (Mansione,Stipendio) = (SELECT Mansione, Stipendio
FROM Impiegati
WHERE Nome = 'Martini');
131 Linguaggio SQL
SQL - Sottointerrogazioni
la clausola di WHERE di una query può contenere una
qualsiasi combinazione di condizioni normali e
condizioni con subqueries
esempio: si vuole elencare gli impiegati con la stessa mansione
di Gianni o con uno stipendio maggiore o uguale a quello di Fordi,
ordinando il risultato in base alla mansione e allo stipendio
SELECT Nome, Mansione, Stipendio FROM Impiegati
WHERE Mansione = (SELECT Mansione FROM Impiegati
WHERE Nome ='Gianni')
OR Stipendio > (SELECT Stipendio FROM Impiegati
WHERE Nome = 'Fordi')
ORDER BY Mansione, Stipendio;
132 Linguaggio SQL
SQL - Sottointerrogazioni
una subquery può contenere a sua volta un'altra
subquery
esempio: elencare il nome e la mansione degli
impiegati del dipartimento 10 con la stessa mansione
di un qualche impiegato del dipartimento Ricerche
SELECT Nome, Mansione FROM Impiegati
WHERE Dip#=10 AND Mansione IN
(SELECT Mansione FROM Impiegati
WHERE Dip# = (SELECT Dip# FROM Dipartimenti
WHERE Nome_Dip = 'Ricerche’));
133 Linguaggio SQL