12 select annidate con: any in all exist , self join

Dato il codice della precedente lezione

select nomenazione from nazioni 
where idnazione = any (select idnazione from nazioni, atleti where idnazione=nazione and eta>14);

= ANY che stava a significare uguale ad ogni , si può anche sostituire con IN che vuol dire è presente in

select nomenazione from nazioni 
where idnazione in (select idnazione from nazioni, atleti where idnazione=nazione and eta>14);

è possibile avere anche la negazione, quindi: voglio il codice nazione che non appare in (not in)

select nomenazione from nazioni 
where idnazione not in (select idnazione from nazioni, atleti where idnazione=nazione and eta>14);

quest’ultima richiesta la posso anche scrivere con è diverso da tutti quelli (!= all)

select nomenazione from nazioni 
where idnazione != all (select idnazione from nazioni, atleti where idnazione=nazione and eta>14);

Ora invece vogliamo: elenco degli atleti della stessa nazione di ‘bianchi carlo’ 

select nomenazione from nazioni 
where idnazione = any (select nazione from atleti, nazioni where idnazione=nazione and cognome='bianchi' and nome='carlo');

Così anche lo stesso bianchi carlo verrebbe incluso nei risultati , per escluderlo basta aggiungere un

and cognome != 'bianchi' or nome != 'carlo';

altra domanda: elenco delle nazioni che hanno almeno un atleta

Per soddisfare questa domanda, ci possono essere varie strade, useremo quella con exist (), operatore condizionale che serve a verificare se esiste almeno un valore, altrimenti il risultato della condizione è falso.

select nomenazione from nazioni 
where exists (select * from nazioni, atleti where idnazione=nazione and nomenazione=nomenazione);

la subselect vorrebbe selezionare l’elenco delle righe della nazione che stiamo considerando, supponendo che nomenazione della prima select sia l’Italia, vorremmo  selezionare l’Italia se ha almeno un’atleta estratto dalla subselect (l’ultimo nomenazione della subselect deve essere uguale al primo della prima select).

nomenazione=nomenazione crea ambiguità in SQL per risolvere dobbiamo utilizzare qualche alias, quindi la prima riga diventa

select naz.nomenazione from nazioni as naz

selezionare dalla tabella chiamata naz nomenazione e rinominare nazioni in naz mentre la subselect diventa

select * from nazioni, atleti where idnazione=nazione and nazioni.nomenazione=naz.nomenazione

nell’uguaglianza il primo nomenazione è estratto dalla tabella nazione (quella del where), mentre il secondo dalla tabella naz rinominata poco prima

select naz.nomenazione from nazioni as naz
where exists (select * from nazioni, atleti where idnazione=nazione and nazioni.nomenazione=naz.nomenazione);

Se invece chiedessimo l’elenco di nazioni che non hanno nemmeno un atleta, basterebbe aggiungere a exists la negazione not, quindi not exists.

Ricordiamoci quindi che per risolvere le ambiguità come quelle viste prima, bisogna creare gli alias cioè tutte quelle volte che dobbiamo fare riferimento agli stessi campi delle stesse tabelle sia nella select principale che in quella secondaria.

SELF JOIN

Concludiamo questa lezione  con un esempio di tabella congiunta con se stessa, cioè con una relazione 1 a M oppure N a M con se stessa.

Facciamo l’esempio pratico, abbiamo un nuovo database anagrafe, con una tabella cittadini, con i campi: idCittadino (PK) , nome , cognome, ksconiuge (che è l’id del codice del coniuge). Abbiamo idCittadino: 1 , nome: Mario , cognome: Rossi, ksconiuge: 3 . All’id 3 troviamo la sig.ra Maria Larossa con ksconiuge: 1 .

La tabella è parzialmente (perché non tutti i cittadino sono sposati) in associazione con se stessa, perché abbiamo una chiave esterna (ksconiuge) in relazione con la chiave primaria (idCittadino).

Vogliamo ottenere un elenco delle coppie sposate.

select nome, cognome, nome, cognome 
from cittadini, cittadini
where idcittadino=ksconiuge;

Altra ambiguità la prima coppia nome e cognome,  sarebbe riferita al marito (Mario Rossi ) e la seconda alla moglie (Maria Larossa), ma SQL non può distinguere, dobbiamo quindi creare un alias della tabella cittadini, in pratica una copia di cittadini che chiameremo cittadini2

select nome, cognome, nome, cognome 
from cittadini, cittadini as cittadini2
where idcittadino=ksconiuge;

e poi selezionare i campi specificando tramite l’operatore punto le tabelle appropriate

select cittadini.nome, cittadini.cognome, cittadini2.nome, cittadini2.cognome 
from cittadini, cittadini as cittadini2
where idcittadino=ksconiuge;

e metterò in join le chiavi delle 2 tabelle

select cittadini.nome, cittadini.cognome, cittadini2.nome, cittadini2.cognome 
from cittadini, cittadini as cittadini2
where cittadini.idcittadino=cittadini2.ksconiuge;

In questo modo abbiamo risolto il problema, otterremo ovviamente una simmetria dei dati, che risulteranno inseriti 2 volte con ordine invertito, in quanto il marito è sposato con la moglie e viceversa.