Introduzione alle "Join" e relazioni uno a uno
In un precedente articolo avevamo offerto il modello teorico per eseguire relazioni tra tabelle in un database MySQL. In queste pagine affronteremo invece la realizzazione pratica delle relazioni che illustrate la volta scorsa. Innanzitutto è bene avere presente il concetto di JOIN ('unione') tra tabelle. Attraverso una query di tipo JOIN (vedremo che ne esistono varie tipologie) è possibile infatti ottenere come risultato di un'unica query un insieme di dati provenienti da tabelle differenti che presentino però una relazione tra di loro.
Facciamo l'esempio pratico di avere a disposizione due tabelle, utenti e indirizzi, legate tra di loro da una relazione uno a uno che, come abbiamo visto nel precedente articolo, ci permette di espanderea i record della tabella concettualmente principale aggiungendo dati facoltativi senza doverci ritrovare con un gran numero di campi vuoti.
Tabella utenti ID | Nome | Cognome ------------------------ 1 | Mario | Rossi 2 | Rosa | Verdi 3 | Adelaide | Palone 4 | Alfonso | Gambaro Tabella indirizzi Utente | Via | Cap | Citta ------------------------------------------------ 1 | via Goldoni, 45 | 20129 | Milano 2 | p.zza XXIV Maggio, 3 | 10111 | Genova 4 | via Garibaldi, 29 | 56100 | Pisa
Senza l'utilizzo della query di tipo JOIN avremmo dovuto eseguire una prima query sulla tabella utenti per estrarre i dati di nostro interesse dalla stessa, quindi per ogni record eseguire nuovamente una query sulla tabella indirizzi per trovare i dati presenti in essa: il risultato in questo caso sarebbe stato di far eseguire a MySQL ben cinque query. Un po' di utenti in contemporanea ed ecco che un errore macroscopico di progettazione può rallentare un server fino al punto di bloccarlo.
Proviamo quindi ad eseguire la query seguente:
SELECT utenti.nome, utenti.cognome, indirizzi.via, indirizzi.cap,
indirizzi.citta FROM utenti, indirizzi
Ci aspetteremmo 4 risultati, invece ne otteniamo 12; com'è possibile?
Le query di tipo JOIN (in questo caso l'unione è implicita) creano un'unione cartesiana tra le tabelle collegate secondo le specifiche inserite per il collegamento. In pratica MySQL crea una nuova tabella virtuale in cui sono presenti tutti i campi di entrambe le tabelle originarie e per ogni record della prima vengono inseriti tanti record quanti sono quelli della seconda per ottenere tutte le combinazioni rese possibili dalle condizioni della tabella. In tal modo in questo caso si ritroverà l'utente "Mario Rossi" una volta con l'indirizzo di Milano, una volta con quello di Genova e una volta con quello di Pisa e la stessa cosa avverrà per tutti gli altri utenti.
Per evitare brutte sorprese è quindi bene specificare tutte le condizioni necessarie, facendo molta attenzione quando ad essere unite non sono solo due tabelle ma un numero maggiore: una sola query di questo tipo progettata male è in grado di occupare tutte le risorse del sistema data la mole di calcoli necessari per eseguirla.
SELECT utenti.nome, utenti.cognome, indirizzi.via, indirizzi.cap,
indirizzi.citta FROM utenti, indirizzi WHERE utenti.ID = via.utente
Classificazione delle JOIN
Sebbene esistano diversi tipi di JOIN le più comuni (che quindi vi troverete ad usare) sono essenzialmente due: le INNER JOIN e le LEFT JOIN.
Inner Join
Le query di tipo INNER JOIN ('unione interna') sono le più comuni nonché il tipo predefinito. Corrispondono, come spiegato precedentemente, all'unione cartesiana di due tabelle e all'eliminazione di tutti i record risultanti che non trovino riscontro nelle condizioni della query.
È possibile eseguire query di questo tipo sia in forma esplicita che in forma implicita: la differenza principale è rappresentata dal punto in cui si inseriscono le condizioni dell'unione.
Le due forme seguenti, rispettivamente la forma implicita e quella esplicita, sono pertanto equivalenti.
#prima forma
SELECT utenti.nome, utenti.cognome, indirizzi.via, indirizzi.cap,
indirizzi.citta FROM utenti, indirizzi WHERE utenti.ID = via.utente
#seconda forma
SELECT utenti.nome, utenti.cognome, indirizzi.via, indirizzi.cap,
indirizzi.citta FROM utenti INNER JOIN indirizzi ON utenti.ID = via.utente
Il termine INNER si può omettere, quindi è probabile che si ritrovi una query in cui l'unione sia ottenuta solo per mezzo del termine JOIN. La mancanza di clausole di unione o la loro scorretta segnalazione può portare infine ad ottenere una query di tipo CROSS JOIN che nella quasi totalità dei casi risulta essere indesiderata e frutto solamente di un errore.
Left Join
Le query di questo tipo permettono di estrarre tutti i record dalla tabella principale (quella specificata appunto a sinistra) associandoli ai valori collegati provenienti dalla seconda tabella qualora essi siano presenti.
È bene notare infatti come le query di tipo INNER JOIN illustrate sopra permettano di estrarre solamente tre record visto che l'utente numero 3 ("Adelaide Palone") non era collegata ad alcun indirizzo.
L'utilizzo di una query LEFT JOIN permette invece di andare a lavorare in quelle condizioni in cui i dati presenti in una delle due tabelle siano soltanto facoltativi. Questa caratteristica tornerà utile più avanti anche nel caso delle unioni "uno a molti".
SELECT utenti.nome, utenti.cognome, indirizzi.via, indirizzi.cap,
indirizzi.citta FROM utenti LEFT JOIN indirizzi ON utenti.ID = via.utente
Grazie a questa query otterremo quattro record corrispondenti ai quattro utenti presenti nel database. Per l'utente privo dei dati della tabella facoltativa i valori dei campi di quella tabella verranno impostati a NULL.
Da notare infine come curiosità l'esistenza della sintassi RIGHT JOIN che genera gli stessi risultati prendendo però come tabella principale quella di destra. È consigliabile non utilizzare questa sintassi bensì invertire l'ordine delle tabelle per avere una maggior compatibilità a livello di codice SQL.
Le relazioni uno a molti
Per le relazioni uno a molti ci troviamo spesso nella situazione di dover aggiungere i dati presenti nella tabella legata in posizione 1 a quelli della tabella in posizione molti. Questo tipo di unione è in fondo anche il significato stesso della normalizzazione di un database: inserire i dati ingombranti (come sono le stringhe rispetto ai valori numerici) una sola volta e collegarli a tutti i record necessari con un dato più corto come un id numerico.
Prendiamo il caso concreto del database libri - editori
Tabella editori ID | Nome --------------------- 1 | Mondadori 2 | Feltrinelli 3 | Apogeo 4 | Tecniche nuove 5 | Piemme Tabella libri ID | Titolo | Editore ----------------------------------------- 1 | Di noi tre | 1 2 | Due di due | 1 3 | Il diario di Bolivia | 2 4 | Il tropico del cancro | 2 5 | HTML 4 tutto e oltre | 3 6 | MySQL guida completa | 3 7 | Imparare PHP in 24 ore | 4 8 | Comunità sul web | 4
Per selezionare i libri con il nome dell'editore associato è sufficiente eseguire una query JOIN di tipo INNER:
SELECT libri.titolo, editori.nome FROM libri JOIN editori ON libri.editore = editori.id
Più interessante risulta invece essere il caso in cui si debbano estrarre gli editori con il numero di libri editi disponibili nel database: in questo caso è necessario tenere conto del fatto che dato il tipo di collegamento non è affatto scontato che un editore sia collegato a qualche libro, mentre un libro è sicuramente collegato ad un editore. Per questo dobbiamo far uso di una query LEFT JOIN unita alla funzione COUNT() di MySQL.
SELECT editori.nome, COUNT(libri.id) AS libri FROM editori
LEFT JOIN libri ON libri.editore = editori.id GROUP BY editori.id
Questa query restituirà come risultato il seguente recordset:
Mondadori | 2 Feltrinelli | 2 Apogeo | 2 Tecniche nuove | 2 Piemme | 0
Le relazioni molti a molti
Le relazioni del tipo molti a molti sono discretamente complicate da gestire per via della mancanza di linearità e dell'utilizzo di tre tabelle anziché due. Per restare all'esempio precedente immaginiamo di avere a disposizione nel database dei libri anche una tabella categoria ed una di collegamento libro -> categoria per fare in modo che lo stesso libro possa essere presente in più categorie (relazione molti a molti).
Tabella categoria ID | Padre | Nome ------------------------ 1 | 0 | Romanzo 2 | 0 | Programmazione 3 | 0 | Web 4 | 0 | Sociologia 5 | 2 | PHP 6 | 0 | Diario Tabella libro_in_categoria Libro | Categoria ------------------------------ 1 | 1 2 | 1 3 | 6 4 | 1 5 | 3 6 | 3 7 | 3 7 | 5 8 | 3 8 | 4
A questo punto eseguiamo una query per estrapolare i libri con le rispettive categorie
SELECT libro.titolo, categoria.nome FROM libro JOIN libro_in_categoria
ON (libro.id = libro_in_categoria.libro) JOIN categoria
ON (categoria.id = libro_in_categoria.categoria) ORDER BY titolo ASC, nome DESC
Ci accorgiamo però in fretta che questa soluzione estrae uno stesso libro tante volte quante sono le categorie a cui è associato, situazione inaccettabile.
Per risolvere questo problema abbiamo due soluzioni:
- ricorrere ad un po' di lavoro da parte del linguaggio di programmazione interfacciato avendo così una maggior personalizzazione del risultato in output
- utilizzare la funzione
GROUP_CONCAT()
La prima soluzione consiste nell'utilizzare una variabile per salvare uno dei dati relativi al libro estrapolati e controllarlo ad ogni ciclo sul recordset in modo da poter individuare il momento in cui terminano le ripetizioni di un libro e iniziano quelle del successivo. In questo modo si possono ad esempio gestire le visualizzazioni delle categorie trasformandole in link.
La seconda soluzione invece è meno flessibile per quanto riguarda la forma del risultato ma è più snella a livello di codice e il lavoro grosso lo svolge tutto MySQL:
SELECT libro.titolo, GROUP_CONCAT(DISTINCT categoria.nome
ORDER BY categoria.nome ASC SEPARATOR ', ') AS categorie
FROM libro JOIN libro_in_categoria ON (libro.id = libro_in_categoria.libro)
JOIN categoria ON (categoria.id = libro_in_categoria.categoria)
GROUP BY libro.id ORDER BY titolo ASC
Le relazioni self
Come era stato anticipato nello scorso articolo inoltre è possibile instaurare una relazione molti a molti anche all'interno della stessa tabella, ad esempio per creare una gerarchia di cartelle l'una dentro l'altra. In questi casi è possibile creare una query JOIN sulla stessa tabella grazie all'uso degli alias:
SELECT cat.nome, padre.nome AS padre FROM categoria AS cat
JOIN categoria AS padre ON cat.padre = padre.id
Grazie a questo tipo di query è possibile elencare tutte le categorie con il relativo elemento padre.
