Nella lezione precedente abbiamo visto come utilizzare i JOIN esterni per recuperare anche le righe che non hanno una corrispondenza nell'altra tabella, con LEFT JOIN.
Esistono però altri tipi di JOIN che permettono di gestire situazioni particolari, tra cui CROSS JOIN, JOIN sulla stessa tabella e FULL JOIN.
Il CROSS JOIN permette di ottenere il prodotto cartesiano di due tabelle. A differenza degli altri JOIN che abbiamo visto, non richiede una condizione ON per stabilire quali righe collegare.
La sintassi è: SELECT colonne FROM tabellaA CROSS JOIN tabellaB;
Il risultato contiene ogni possibile combinazione tra le righe delle due tabelle.
Supponiamo di avere una tabella colori e una tabella taglie. Scriviamo la seguente query:
SELECT colori.colore, taglie.taglia
FROM colori
CROSS JOIN taglie;Nel risultato della query dell'esempio, ogni colore viene combinato con ogni taglia. Se la prima tabella contiene 3 righe e la seconda 3 righe, il risultato conterrà 9 righe.
Un JOIN sulla stessa tabella (detto anche self join) consiste nell'effettuare un JOIN tra una tabella e se stessa. Può sembrare strano, ma è molto utile quando una tabella contiene una relazione tra i propri dati.
Possiamo usare come esempio un database aziendale contenente la lista di chi lavora, in cui ogni dipendente può avere un manager che è a sua volta un dipendente.
Ad esempio, la tabella può contenere le colonne id, nome e manager_id:
In questo esempio:
La colonna manager_id contiene quindi l'ID di un altro dipendente della stessa tabella.
Per scrivere la query, possiamo utilizzare un LEFT JOIN:
SELECT dipendente.nome AS dipendente,
manager.nome AS manager
FROM dipendenti AS dipendente
LEFT JOIN dipendenti AS manager
ON dipendente.manager_id = manager.id;Nell'esempio, la stessa tabella dipendenti viene utilizzata due volte, ma con due ruoli differenti:
dipendente rappresenta il dipendente.manager rappresenta il suo manager.Un FULL OUTER JOIN, spesso abbreviato in FULL JOIN, combina il comportamento di LEFT JOIN e RIGHT JOIN.
FULL JOIN ha lo scopo di mantenere tutte le righe di entrambe le tabelle. Se esiste una corrispondenza, i dati delle due tabelle vengono combinati.
Se invece una riga non ha una corrispondenza, vengono mantenuti comunque i dati della tabella di sinistra oppure quelli della tabella di destra, mentre le colonne dell'altra tabella assumono valore NULL.
MySQL non supporta direttamente la sintassi FULL OUTER JOIN. Per ottenere un risultato equivalente possiamo combinare un LEFT JOIN e un RIGHT JOIN tramite UNION.
Nota: vedremo in dettaglio UNION nella prossima lezione sugli operatori di insieme.
Ad esempio, abbiamo una tabella clienti e una tabella ordini. Alcuni utenti non sono più presenti nella tabella clienti perché hanno eliminato il proprio account, anche se hanno effettuato ordini. Vogliamo restituire comunque tutti i clienti e gli ordini.
SELECT
c.nome AS cliente,
o.id AS ordine,
o.totale
FROM clienti c
LEFT JOIN ordini o ON c.id = o.cliente_id
UNION
SELECT
c.nome AS cliente,
o.id AS ordine,
o.totale
FROM clienti c
RIGHT JOIN ordini o ON c.id = o.cliente_id;