Luku 3

JOIN-syntaksi

Tähän mennessä olemme hakeneet tietoa tauluista listaamalla taulut kyselyn FROM-osassa, mikä toimii yleensä hyvin. Kuitenkin joskus on tarpeen vaihtoehtoinen JOIN-syntaksi. Siitä on hyötyä silloin, kun kyselyn tuloksesta näyttää "puuttuvan" tietoa.

Kyselytavat

Seuraavassa on kaksi tapaa toteuttaa sama kysely, ensin käyttäen ennestään tuttua tapaa ja sitten käyttäen JOIN-syntaksia.

SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit, Opettajat
WHERE Kurssit.opettaja_id = Opettajat.id;
SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit JOIN Opettajat ON Kurssit.opettaja_id = Opettajat.id;

JOIN-syntaksissa taulujen nimien välissä esiintyy sana JOIN ja lisäksi taulujen rivit toisiinsa kytkevä ehto annetaan erillisessä ON-osassa.

Tässä tapauksessa JOIN-syntaksi on vain vaihtoehtoinen tapa toteuttaa kysely eikä se tuo mitään uutta. Kuitenkin näemme seuraavaksi, miten voimme laajentaa syntaksia niin, että se antaa meille uusia mahdollisuuksia kyselyissä.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa tietokannassa on tutut taulut Kurssit ja Opettajat, mutta taulussa Kurssit yhdeltä kurssilta puuttuu opettaja:

id          nimi              opettaja_id
----------  ----------------  -----------
1           Laskennan mallit  3          
2           Ohjelmoinnin per  1          
3           Ohjelmoinnin jat  1          
4           Tietokantojen pe             
5           Tietorakenteet j  3          

Rivin 4 sarakkeessa opettaja_id on arvo NULL, joten jos suoritamme jommankumman äskeisen kyselyn, ongelmaksi tulee, että rivi 4 ei täsmää mihinkään taulun Opettajat riviin. Tämän seurauksena tulostauluun ei tule riviä kurssista Tietokantojen perusteet:

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietorakenteet j  Kivinen 

Ratkaisu ongelmaan on käyttää LEFT JOIN -syntaksia, joka tarkoittaa, että mikäli jokin vasemman taulun rivi ei yhdisty mihinkään oikean taulun riviin, kyseinen vasemman taulun rivi pääsee silti mukaan yhdeksi riviksi tulostauluun. Kyseisellä rivillä jokaisen oikeaan tauluun perustuvan sarakkeen arvona on NULL.

Tässä tapauksessa voimme toteuttaa kyselyn näin:

SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit LEFT JOIN Opettajat ON Kurssit.opettaja_id = Opettajat.id;

Nyt tulostauluun ilmestyy myös kurssi Tietokantojen perusteet ilman opettajaa:

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen

Miten kysely toimii?

Jälleen hyvä tapa saada selkoa kyselystä on yksinkertaistaa sitä:

SELECT * FROM Kurssit LEFT JOIN Opettajat ON Kurssit.opettaja_id = Opettajat.id;
id          nimi              opettaja_id  id          nimi      
----------  ----------------  -----------  ----------  ----------
1           Laskennan mallit  3            3           Kivinen   
2           Ohjelmoinnin per  1            1           Kaila     
3           Ohjelmoinnin jat  1            1           Kaila     
4           Tietokantojen pe                                     
5           Tietorakenteet j  3            3           Kivinen  

Tästä näkee, että koska vasemman taulun rivi 4 ei täsmää mihinkään oikean taulun riviin, niin kyseisestä rivistä tulee tulostauluun yksi rivi, jossa jokainen sarake oikean taulun osuudessa on NULL.

ON vs. WHERE

Sana ON on oleellinen LEFT JOIN -kyselyssä, koska se asettaa ehdon niin, että mukaan otetaan myös vasemman taulun ylimääräiset rivit:

SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit LEFT JOIN Opettajat ON Kurssit.opettaja_id = Opettajat.id;
nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen

Jos käytämme sen sijasta sanaa WHERE, ylimääräiset rivit jäävät pois:

SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit LEFT JOIN Opettajat WHERE Kurssit.opettaja_id = Opettajat.id;
nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietorakenteet j  Kivinen

Sinänsä kyselyssä voi esiintyä sekä ON että WHERE:

SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit LEFT JOIN Opettajat ON Kurssit.opettaja_id = Opettajat.id
WHERE Kurssit.nimi <> 'Ohjelmoinnin perusteet';

Tällöin ON-osa hoitaa taulujen yhdistämisen ja WHERE-osa rajaa tuloksia lisää:

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen   

Jos molemmat ehdot ovatkin ON-osassa, kyselyn tulos muuttuu taas:

SELECT Kurssit.nimi, Opettajat.nimi
FROM Kurssit LEFT JOIN Opettajat ON Kurssit.opettaja_id = Opettajat.id
AND Kurssit.nimi <> 'Ohjelmoinnin perusteet';
nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per            
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen   
Jatka tästä seuraavaan osaan: