OUTER JOIN

Gli argomenti di questa pagina

  • Definizione di OUTER JOIN o LEFT JOIN e RIGHT JOIN
  • La sintassi
  • OUTER JOIN fra due tabella
  • Esclusione di record
  • JOIN sulla stessa tabella
  • Record non corrispondenti

Definizione di OUTER JOIN o LEFT JOIN e RIGHT JOIN

Mentre la INNER JOIN recupera le righe da più tabelle scartando tutti i record che non trovino riscontro nelle condizioni della Query, la OUTER JOIN estrae tutte le righe da una tabella e le corrispondenti righe da una seconda tabella. Una OUTER JOIN viene specificata come una LEFT [OUTER] JOIN e una RIGHT [OUTER] JOIN.

La sintassi

E' molto simile a quella della INNER JOIN ma, contrariamente alla INNER JOIN, è molto importante la posizione delle due tabelle rispetto alla JOIN

SELECT elenco campi
FROM tabella1 OUTER JOIN tabella2
ON specifica di JOIN
WHERE condizioni di ricerca
ORDER BY elenco campi

Dallo schema proposto dalla sintassi si nota che nella specifica di JOIN si ha una tabella a sinistra ed una a destra. Ebbene: la LEFT JOIN e la RIGHT JOIN si riferiscono appunto alla disposizione che tali tabelle occupano nella specifica.

OUTER JOIN fra due tabella

Ma vediamo subito alcuni esempi che chiariscono questi concetti. Ove possibile si cercherà di vedere la differenza tra la INNER JOIN ed una delle OUTER JOIN.
Proviamo a porgere al DB la seguente richiesta:

Elencare tutti i libri e mostrare se sono stati prestati

La prima cosa che ci viene in mente è questa Query (vista tra l'altro nella pagina precedente). Purtroppo notiamo subito che molti dei libri non figurano nell'elenco. Questi libri che mancano non sono mai stati prestati e di conseguenza la INNER JOIN non li accetta nel recordset.

Esempio 1
1
2
3
4
5
6
7
8
9
10
11
12
<?php
$Query 
"SELECT 
sql_libri.id, 
sql_libri.Titolo, 
sql_libri.gruppo, 
DATE_FORMAT( sql_lib_prestiti.data_prestito, '%d-%m-%Y' ) AS prestato 
FROM sql_libri
    INNER JOIN sql_lib_prestiti 
        ON sql_libri.id = sql_lib_prestiti.id_libro 
ORDER BY sql_lib_prestiti.data_prestito DESC
"
;
?>
10 record trovati
10 record visualizzati
id Titolo gruppo prestato
20 Poser 4 Pro curiouslabs 02-05-2014
2 Macromedia Flash 6 macromedia 19-04-2014
2 Macromedia Flash 6 macromedia 01-04-2014
8 Macromedia Spectra macromedia 29-03-2014
21 Kai`s PhotoSoap2 scansoft 10-03-2014
15 CorelDRAW corel 13-02-2014
5 Adobe Photoshop 7.0 adobe 20-01-2014
5 Adobe Photoshop 7.0 adobe 15-03-2013
10 Adobe Digital Video Collection adobe 02-02-2013
9 Adobe After Effects 5.5 adobe 01-01-2012

Infatti il problema si deve risolvere con una LEFT JOIN che accetta tutti i record della tabella di sinistra anche se non hanno una corrispondenza nella tabella di destra. Ora nel riquadro sottostante sono visibili tutti i libri prestati e non prestati. Ovviamente, per motivi di spazio, non vengono visualizzati tutti.

Esempio 2
1
2
3
4
5
6
7
8
9
10
11
12
<?php
$Query 
"SELECT 
sql_libri.id, 
sql_libri.Titolo, 
sql_libri.gruppo, 
DATE_FORMAT( sql_lib_prestiti.data_prestito, '%d-%m-%Y' ) AS prestito 
FROM sql_libri
    LEFT JOIN sql_lib_prestiti 
        ON sql_libri.id = sql_lib_prestiti.id_libro 
ORDER BY sql_lib_prestiti.data_prestito DESC
"
;
?>
24 record trovati
15 record visualizzati
id Titolo gruppo prestito
20 Poser 4 Pro curiouslabs 02-05-2014
2 Macromedia Flash 6 macromedia 19-04-2014
2 Macromedia Flash 6 macromedia 01-04-2014
8 Macromedia Spectra macromedia 29-03-2014
21 Kai`s PhotoSoap2 scansoft 10-03-2014
15 CorelDRAW corel 13-02-2014
5 Adobe Photoshop 7.0 adobe 20-01-2014
5 Adobe Photoshop 7.0 adobe 15-03-2013
10 Adobe Digital Video Collection adobe 02-02-2013
9 Adobe After Effects 5.5 adobe 01-01-2012
1 Macromedia Freehand 10 macromedia NULL
3 Macromedia Director Shockwave macromedia NULL
22 PhotoShow scansoft NULL
7 Macromedia Sitespring macromedia NULL
11 Adobe GoLive/LiveMotion Pack adobe NULL
... ... ... ...

Esclusione di record

Una richiesta lecita potrebbe essere quella di voler vedere se un'azione è stata mai compiuta.

Mostra i libri che non sono mai stati prestati

Esempio 3
1
2
3
4
5
6
7
<?php
$Query 
"SELECT sql_libri.id, sql_libri.Titolo, sql_libri.gruppo 
FROM sql_libri
LEFT JOIN sql_lib_prestiti ON sql_libri.id = sql_lib_prestiti.id_libro
WHERE sql_lib_prestiti.data_prestito IS NULL
"
;
?>
14 record trovati
14 record visualizzati
id Titolo gruppo
1 Macromedia Freehand 10 macromedia
3 Macromedia Director Shockwave macromedia
4 Macromedia Authorware 6 macromedia
6 Macromedia Homesite macromedia
7 Macromedia Sitespring macromedia
11 Adobe GoLive/LiveMotion Pack adobe
12 Adobe Illustrator 10 adobe
13 Adobe Dimensions 3.0 adobe
14 Adobe PageMaker 7.0 adobe
16 Corel corel
17 KPT Tools 6 corel
18 KPT Vector Effects corel
19 Bryce corel
22 PhotoShow scansoft

JOIN sulla stessa tabella

Iniziamo col mostrare integralmente la tabella sql_libri

22 record visualizzati
id gruppo codice Titolo prezzo quantita
1 macromedia MC004 Macromedia Freehand 10 78.99 197
2 macromedia MC003 Macromedia Flash 6 78.99 291
3 macromedia MC002 Macromedia Director Shockwave 140.00 144
4 macromedia MC001 Macromedia Authorware 6 98.99 237
5 adobe AD001 Adobe Photoshop 7.0 670.00 498
6 macromedia MC005 Macromedia Homesite 90.00 398
7 macromedia MC006 Macromedia Sitespring 120.00 219
8 macromedia MC007 Macromedia Spectra 240.00 36
9 adobe AD002 Adobe After Effects 5.5 160.00 5
10 adobe AD003 Adobe Digital Video Collection 670.00 1
11 adobe AD004 Adobe GoLive/LiveMotion Pack 499.99 110
12 adobe AD005 Adobe Illustrator 10 179.50 48
13 adobe AD006 Adobe Dimensions 3.0 279.99 149
14 adobe AD007 Adobe PageMaker 7.0 80.00 309
15 corel CR001 CorelDRAW 670.00 448
16 corel CR002 Corel 190.00 119
17 corel CR003 KPT Tools 6 42.00 559
18 corel CR004 KPT Vector Effects 72.00 560
19 corel CR005 Bryce 112.00 692
20 curiouslabs CL001 Poser 4 Pro 420.99 36
21 scansoft SS001 Kai`s PhotoSoap2 130.00 359
22 scansoft SS002 PhotoShow 230.00 179

Per ogni gruppo indica il prezzo più alto dei libri in esso compresi

Per mostrare il prezzo più alto dei libri di ciascun gruppo, qui chiamato max_prezzo, possiamo usare questa Query, già vista nella pagina Riepiloghi con subtotali nell'esempio 6.

esempio 4
1
2
3
4
5
6
7
<?php
$Query 
"SELECT gruppo, MAX( prezzo ) AS max_prezzo
FROM sql_libri
GROUP BY gruppo
ORDER BY max_prezzo 
"
;
?>
5 record trovati
5 record visualizzati
gruppo max_prezzo
scansoft 230.00
macromedia 240.00
curiouslabs 420.99
adobe 670.00
corel 670.00

Ma se a fianco del max_prezzo volessimo vedere anche il titolo o i titoli che hanno questo max_prezzo ci viene in mente di creare questa Query che, purtroppo, risulterà errata perchè i gruppi ed i relativi valori massimi trovati non risultano allineati coi corrispondenti titoli. L'errato resultset generato dalla Query è rilevato confrontando i prezzi restituiti dal resultset con quelle visializzati nella tabella originale mostrata più sopra.

Esempio 4 a
1
2
3
4
5
6
7
<?php
$Query 
"SELECT gruppo, Titolo, MAX( prezzo ) AS max_prezzo
FROM sql_libri
GROUP BY gruppo 
ORDER BY max_prezzo 
"
;
?>
5 record trovati
5 record visualizzati
gruppo Titolo max_prezzo
scansoft Kai`s PhotoSoap2 230.00
macromedia Macromedia Freehand 10 240.00
curiouslabs Poser 4 Pro 420.99
adobe Adobe Photoshop 7.0 670.00
corel CorelDRAW 670.00

Per ottenere il perfetto allineamento del prezzo trovato col libro a cui si riferisce occorre cambiare radicalmente la Query.
In questa Query infatti si lavora con due copie della stessa tabella:
una che usa il suo nome di origine e l'altra che usa un suo ALIAS creando di fatto una LEFT JOIN.

Esempio 4 b
1
2
3
4
5
6
7
8
9
<?php
$Query 
"SELECT sql_libri.gruppo, sql_libri.Titolo, sql_libri.prezzo
FROM sql_libri
LEFT JOIN sql_libri AS tab_1 
ON sql_libri.gruppo = tab_1.gruppo AND sql_libri.prezzo < tab_1.prezzo
WHERE tab_1.gruppo IS NULL
ORDER BY gruppo, Titolo 
"

?>
6 record trovati
6 record visualizzati
gruppo Titolo prezzo
adobe Adobe Digital Video Collection 670.00
adobe Adobe Photoshop 7.0 670.00
corel CorelDRAW 670.00
curiouslabs Poser 4 Pro 420.99
macromedia Macromedia Spectra 240.00
scansoft PhotoShow 230.00

Record non corrispondenti

Nella pagina precedente abbiamo visto come ottenere un elenco dei clienti che hanno preso dei libri. Abbiamo visto come il problema si risolve facilmente con una INNER JOIN sulle tabelle imteresate. Vediamo ora come ottenere un elenco dei clienti che non hanno mai preso un libro.

Mostrare l'elenco dei clienti che non hanno mai preso un libro

La prima soluzione è più semplice perchè le tabelle coinvolte sono solo due: sql_agenda come tabella di sinistra della JOIN e sql_lib_prestiti come tabella destra.
Con una LEFT JOIN vengono presi i record della tabella di sinistra e, mediante la clausola WHERE, andiamo a recuperare solo quei record che sono identificati come NULL dalla specifica data alla LEFT JOIN.

Esempio 5
1
2
3
4
5
6
7
8
9
<?php
$Query 
"SELECT sql_agenda.cognome, sql_agenda.nome 
FROM sql_agenda 
    LEFT JOIN sql_lib_prestiti
        ON sql_agenda.id = sql_lib_prestiti.id_iscritto
    WHERE sql_lib_prestiti.id_iscritto IS NULL
    ORDER BY sql_agenda.cognome ASC
"
;
?>
94 record trovati
15 record visualizzati
cognome nome
Armonia Laura
Baldoni Serena
Baroni Fernando
Battista Antomia
Bazzotti Pietro
Bertazzo Luciana
Biancarosa Francesca
Bianchi Emilio
Boscati Andrea
Branda Anna Maria
Broccardo Andrea
Bronzi Edoardo
Brunetti Pierpaolo
Burranca Cristian
Buscetti Editta
... ...

Mostrare l'elenco dei clienti e le loro città che non hanno mai preso un libro

Questa seconda Query è lievemente più complessa. Infatti oltre ai semplici nomi vogliamo mostrare anche le città dei clienti e qui le tabelle coinvolte sono tre: sql_agenda, sql_citta e sql_lib_prestiti.
Per far questo dobbiamo eseguire due JOIN:
Una INNER JOIN per unire i clienti alle città;
Una LEFT JOIN per unire il recordset creato con la tabella dei prestiti
Anche in questo caso, mediante la clausola WHERE, andiamo a recuperare tutti quei record che sono identificati come NULL dalla specifica data alla LEFT JOIN.

Esempio 6
1
2
3
4
5
6
7
8
9
10
11
12
<?php
$Query 
"SELECT sql_agenda.cognome, sql_agenda.nome, 
sql_citta.citta, sql_citta.cap 
FROM (sql_agenda 
    INNER JOIN sql_citta 
        ON sql_agenda.id_citta = sql_citta.id) 
    LEFT JOIN sql_lib_prestiti
        ON sql_agenda.id = sql_lib_prestiti.id_iscritto
    WHERE sql_lib_prestiti.id_iscritto IS NULL
    ORDER BY sql_agenda.cognome ASC
"
;
?>
94 record trovati
15 record visualizzati
cognome nome citta cap
Armonia Laura Giusvalla 17010
Baldoni Serena Borgo S. Dalmazzo 12011
Baroni Fernando AbanoTerme 35031
Battista Antomia Bevagna 06031
Bazzotti Pietro Abbadia Lariana 23821
Bertazzo Luciana Isola di bene vagienna 12040
Biancarosa Francesca Agropoli 56011
Bianchi Emilio Castel Vittorio 18037
Boscati Andrea Ville di Fano 67010
Branda Anna Maria Bevagna 06031
Broccardo Andrea Abbadia Lariana 23821
Bronzi Edoardo Giusvalla 17010
Brunetti Pierpaolo Isola di bene vagienna 12040
Burranca Cristian Agropoli 56011
Buscetti Editta Bevagna 06031
... ... ... ...

 

 



settore tecnico il sito di lorettabweb il Forum di sostegno
il forum il forum il forum