Informatica - select nidificata

Utilizzo della nidificazione delle query

Escluso il ricorso a query che elencano l'attributo nome insieme ad una funzione di gruppo max(lit), escluso qualunque raggruppamento di tuple, saremmo portati ad eseguire due query distinte; con la prima calcoliamo il prezzo più alto:

SELECT max(lit) FROM modello; [44.1]

Il risultato della query, cioè 654321, viene impiegato per scrivere:

SELECT nome FROM modello WHERE lit = 654321; [44.2]

Appare immediato il difetto di questo approccio. In particolare la seconda query, non è generalizzabile in quanto una variazione dei dati della relazione MODELLO comporta anche una sua modifica.

La nidificazione delle query evita questo inconveniente per la modifica della condizione stabilita nella [44.2]. Sostanzialmente si tratta delle due query precedenti con qualche modifica: ne riportiamo il testo:

SELECT nome FROM modello WHERE lit = (SELECT max(lit) FROM modello); [45]

Vediamo qui un esempio di come sia possibile esprimere, attraverso la nidificazione, espressioni condizionali in cui si...

confronta uno scalare con una funzione colonna (grazie al calcolo che avviene in tempi diversi!). Una notevole proprietà delle select nidificate è quella di essere equivalenti, in molti casi, alle join di una tabella con se stessa.

Si consideri il problema risolto con la 25. Allo stesso risultato si può arrivare con la select nidificata, calcolando per prima la relazione che restituisce il consumo a ciclo urbano della "126", quindi selezionando dalla relazione modello le tuple che rispettano la condizione richesta.

11.3 Le parole ANY/ALL

Il prossimo argomento riguarda l'uso di ANY oppure ALL. Consideriamo il seguente problema: vogliamo conoscere i nomi delle autovetture della casa 3 che hanno lo stesso uso delle autovetture della casa 0. Il risultato si ottiene calcolando, con una nidificazione, le tipologie di uso delle auto della casa 0, quindi selezionando le auto della casa 3 che sono della stessa tipologia. Purtroppo la query:

SELECT nome FROM

Il modello formattato con i tag HTML è il seguente:

<p>modello</p>
<p>WHERE nocasa = '3' AND uso = (SELECT uso FROM modello WHERE nocasa = '0'); [46]</p>
<p>restituisce un errore se, come accade con i dati del nostro esempio, la relazione interna ha cardinalità maggiore di uno. Cio' avviene poiché la condizione posta nella terza riga risulta ambigua: questa, infatti, deve valere per uno o per tutti gli scalari calcolati dalla select interna? La specificazione di una delle due particelle ANY/ALL risolve l'ambiguità specificando al programma cosa fare. In particolare la parola ANY significa che la condizione deve essere valida "per una qualsiasi tupla", mentre la parola ALL sta per "tutte le tuple" della relazione più interna. Detto questo, riportiamo il testo della query che risolve il problema proposto in precedenza insieme al risultato ottenuto.</p>
<p>SELECT nome FROM modello WHERE nocasa = '3' AND uso = ANY(SELECT uso FROM modello WHERE nocasa = '0'); [47]</p>
<p>NOME</p>
<p>----------</p>
<p>croma td</p>

sotto:

<p>Le considerazioni appena svolte, unitamente alla possibilità di usare la parola IN nella WHERE così come avveniva nella query [10], spiegano come la [47] possa essere sostituita da:</p>

<pre>
<code>
<p>SELECT nome FROM modello<br>
WHERE nocasa = '3'<br>
AND uso IN<br>
(SELECT uso FROM modello<br>
WHERE nocasa = '0'); [47.1]</p>
</code>
</pre>

<p>per ottenere un risultato identico. In questa forma, si capisce meglio come la richiesta di specificazione di ANY/ALL sia giustificata. Se infatti si sviluppa la [47.1], analogamente a quanto fatto nella [11], si comprende che le condizioni multiple definite in questo modo, hanno bisogno di conoscere se devono essere legate con AND oppure OR.</p>

<p>La prossima query, mostra con un esempio, quando ricorrere alla particella ALL. Si vuole conoscere il nome delle auto della casa 0 che hanno una cilindrata maggiore di tutte le auto della casa 3. In questo caso, è la stessa espressione "maggiore di tutte", ad aiutarci nella comprensione della query che riportiamo di sotto:</p>

seguito insieme alla relazione risultato. SELECT nome FROM modello WHERE nocasa = '0' AND cc > ALL(SELECT cc FROM modello WHERE nocasa = '3'); [48] NOME ---------------- cx 25 limousine cx25 trd turbo dscx 25 gti turbo 11.4 Una condizione equivalente Qualora le condizioni per la nidificazione siano di questo tipo, ovvero condizioni "maggiore di ALL", si possono evitare ricorsi alla ALL con una query equivalente, costituita nel nostro caso da: SELECT .. FROM .. WHERE .. AND .. > (SELECT max(..) FROM .. WHERE ..); [48.1] Una osservazione relativa alle due specifiche ANY/ALL. Intuitivamente si capisce che la particella ANY è notevolmente meno restrittiva rispetto alla ALL. Questo ci introduce al discorso che segue. Supponiamo di voler risolvere il quesito precedente con una variante: la cilindrata deve essere maggiore di una qualunque etc. In questo caso si perviene con facilità alla scrittura del testo della query, mentre non altrettanto semplice risulta la

comprensione del suo risultato.

SELECT nome FROM modello WHERE nocasa = '0' AND cc > ANY(SELECT cc FROM modello WHERE nocasa = '3');

[49]NOME

-----------------

bx 19 trd diesel

cx 25 limousine

cx25 trd turbo

dscx 25 gti turbo

bx 19 trd diesel

cx 25 limousine

cx25 trd turbo

dscx 25 gti turbo

...

...

...

cx 25 limousine

cx25 trd turbo

cx 25 gti turbo

Cosa e' accaduto? La ragione di questo sta nella diversa restrittivita' di ANY e ALL unitamente al meccanismo di funzionamento della SELECT nidificata. Se infatti la relazione interna ha cardinalita' maggiore di uno, la stessa tupla della SELECT esterna risulta "vincente" per diverse volte, portando al risultato visto. Questo inconveniente viene facilmente evitato aggiungendo alla lista dei valori la parola DISTINCT.

11.5 (ancora) Una condizione equivalente

Analogamente a quanto detto in precedenza in occasione della [48.1], problemi derivanti dall'uso di query simili alla [49], a maggior ragione, possono

SELECT nome FROM modello WHERE nocasa = '0' AND cc > (SELECT min(cc) FROM modello WHERE nocasa = '3'); Una interessante osservazione merita la condizione espressa per la query [47.1]. Si capisce immediatamente che l'inversione del risultato e percio' della WHERE, si ottiene facendo precedere IN da NOT. Riportiamo il testo della query corrispondente, assieme al risultato per confermarne la correttezza. SELECT nome FROM modello WHERE nocasa = '3' AND uso NOT IN (SELECT uso FROM modello WHERE nocasa = '0'); NOME -------------- 126panda 750 l panda 1000 s panda d diesel Si tenga presente, e questo e' quanto ci premeva notare, che la stessa inversione, se ci riferiamo alla query equivalente [47], si ottiene cambiando ANY con ALL assieme alla negazione NOT. Nel caso presentato di seguito la negazione viene espressa ricorrendo al simbolo equivalente "!". SELECT nome FROM modello WHERE nocasa =

'3'AND uso != ALL(SELECT uso FROM modelloWHERE nocasa = '0'); [51]11.6 Una particolare struttura di query nidificataUna interessante possibilita' della SELECT nidificata, viene evidenziata nella query che stiamo percommentare: supponiamo di voler ottenere il nome della casa che produce il maggior numero diautovetture. Allo scopo di facilitare la comprensione della query, sono necessarie alcuneconsiderazioni. Problemi di ricerca dell'attributo, la casa costruttrice nel nostro caso, associato ad unvalore calcolato con una funzione colonna, si risolvono sempre con una nidificazione, analogamentea quanto esemplificato nella query [45]. In aggiunta a questo, dovremo considerare che i datinecessari ad una delle due SELECT si trovano su tabelle diverse, quindi dovremo ricorrere anche aduna join. Il problema maggiore pero' riguarda la ricerca del massimo non sui dati di una colonna,ma sui valori ottenuti da una funzione colonna (la count che calcola le tuple

di ciascuna casa costruttrice). Per risolverlo consideriamo i risultati delle prossime tre query. La prima potrà apparire scontata, ma è funzionale al nostro ragionamento. Si tratta della query che calcola quante sono le tuple della tabella MODELLO. La riportiamo con il risultato. ```html

SELECT count(*) FROM modello;

COUNT(*)

--------

12

``` Se aggiungiamo, alla query precedente la clausola GROUP BY in relazione all'attributo nocasa, otteniamo una lista con il numero di modelli prodotti da ogni costruttore. Ne riportiamo il testo insieme al risultato. ```html

SELECT count(*) FROM modello GROUP BY nocasa;

COUNT(*)

--------

4251

``` Risulta semplice ora ottenere il massimo tra i valori appena elencati: basterà chiedere il calcolo del massimo sui risultati della query precedente. Anche in questo caso riportiamo la query ed il suo risultato. ```html

SELECT max(count(*)) FROM modello GROUP BY nocasa;

MAX(COUNT(*))

-------------

5

``` Le considerazioni svolte precedentemente alle tre query ed il risultato ottenuto con la

1. possonoriassumersi nei punti che riepiloghiamo di seguito come risolutivi del nostro problema e diproblemi analoghi:
2. select nidificata come richiesto per il calcolo di attributi associati ad un valore massimo (vederequery [45]);
3. select interna come la [52]
4. la query esterna deve eseguire una join ed un raggruppamento sulla casa costruttrice.
5. La fasatura tra le due select, quella interna e quella esterna, si otterra' imponendo una condizionenella query esterna che usa una funzione colonna; attenzione a non confondere questo con la listache segue SELECT nella query interna. Ancora aggiungiamo che il raggruppamento della selectesterna puo' essere su nocasa oppure su casa. Il testo della query ed il suo risultato sono riportati diseguito.

SELECT casa, count(*)
FROM marche, modello
WHERE marche.nocasa = modello.nocasa
GROUP BY casa
HAVING count(*) =
(SELECT max(count(*))
FROM modello
GROUP BY nocasa); [53]

CASA COUNT(*)
-------- ----------
fiat 511.7

"tutto" ed una sua parte

La select nidificata risulta decisiva nei casi in cui il software Sql che utilizziamo non ammette l'uso della opzione "(+)" come era stato fatto per la query [23]. Se questo avviene la query che elenca i nomi dei costruttori che non producono alcun modello è ottenuta con una nidificazione in cui la query interna elenca i codici dei costruttori che producono almeno un modello, mentre la esterna impone la condizione che il codice deve essere diverso da tutti quelli contenuti nella select interna.