Tietokantojen perusteet

kesä 2024

3. Monen taulun kyselyt

Taulujen viittaukset

Keskeinen idea tietokannoissa on, että taulun rivi voi viitata toisen taulun riviin. Tällöin voidaan muodostaa kyselyjä, jotka keräävät tietoa useista tauluista viittausten perusteella. Käytännössä viittauksena on yleensä toisessa taulussa olevan rivin id-numero.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa tietokannassa on tietoa kursseista ja niiden opettajista. Oletamme, että jokaisella kurssilla on yksi opettaja ja sama opettaja voi opettaa monta kurssia.

Tallennamme tauluun Opettajat tietoa opettajista. Jokaisella opettajalla on id-numero, jolla voimme viitata siihen.

id          nimi      
----------  ----------
1           Kaila     
2           Luukkainen
3           Kivinen   
4           Laaksonen 

Taulussa Kurssit on puolestaan tietoa kursseista ja jokaisen kurssin kohdalla viittaus kurssin opettajaan.

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

Voimme nyt hakea kurssit opettajineen seuraavalla kyselyllä, joka hakee tietoa samaan aikaan tauluista Kurssit ja Opettajat:

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

Koska kyselyssä on monta taulua, ilmoitamme sarakkeiden taulut. Esimerkiksi Kurssit.nimi viittaa taulun Kurssit sarakkeeseen nimi.

Kysely antaa seuraavan tuloksen:

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

Mitä tässä tapahtui?

Yllä olevassa kyselyssä uutena asiana on, että kysely koskee useaa taulua (FROM Kurssit, Opettajat), mutta mitä tämä tarkoittaa?

Ideana on, että kun kyselyssä on monta taulua, tulostaulun lähtökohtana ovat kaikki tavat valita rivien yhdistelmiä tauluista. Tämän jälkeen WHERE-osan ehdoilla voi määrittää, mitkä rivien yhdistelmät ovat kiinnostuksen kohteena.

Hyvä tapa saada ymmärrystä monen taulun kyselyn toiminnasta on tarkastella ensin kyselyä, joka hakee kaikki sarakkeet ja jossa ei ole WHERE-osaa. Yllä olevassa esimerkkitilanteessa tällainen kysely on seuraava:

SELECT * FROM Kurssit, Opettajat;

Koska taulussa Kurssit on 5 riviä ja taulussa Opettajat on 4 riviä, kyselyn tulostaulussa on 5 * 4 = 20 riviä. Tulostaulu sisältää kaikki mahdolliset tavat valita ensin jokin rivi taulusta Kurssit ja sitten jokin rivi taulusta Opettajat:

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

Suurin osa tulosriveistä ei ole kuitenkaan kiinnostavia, koska niillä olevat tiedot eivät liity toisiinsa. Esimerkiksi ensimmäinen tulosrivi kertoo vain, että on olemassa kurssi Laskennan mallit ja on olemassa opettaja Kaila.

Jotta kysely antaisi mielekkäitä tuloksia, rajaamme hakua niin, että opettajan id-numeron tulee olla sama kummankin taulun riveissä:

SELECT
  *
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;

Tämän seurauksena kysely alkaa antaa mielekkäitä tuloksia:

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  4            4           Laaksonen 
5           Tietorakenteet j  3            3           Kivinen   

Tämän jälkeen voimme vielä siistiä kyselyn tuloksia valitsemalla meitä kiinnostavat sarakkeet:

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

Näin päädymme samaan tulokseen kuin aiemmin:

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

Lisää ehtoja kyselyssä

Monen taulun kyselyissä WHERE-osa kytkee toisiinsa meitä kiinnostavat taulujen rivit, mutta lisäksi voimme laittaa WHERE-osaan muita ehtoja samaan tapaan kuin ennenkin. Esimerkiksi voimme suorittaa seuraavan kyselyn:

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id AND
  Opettajat.nimi = 'Kivinen';

Näin saamme haettua kurssit, joiden opettajana on Kivinen:

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Tietorakenteet j  Kivinen 

Taulujen lyhyet nimet

Voimme tiivistää monen taulun kyselyä antamalla tauluille vaihtoehtoiset lyhyet nimet, joiden avulla voimme viitata niihin kyselyssä. Esimerkiksi kysely

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

voidaan esittää lyhemmin näin:

SELECT
  K.nimi, O.nimi
FROM
  Kurssit AS K, Opettajat AS O
WHERE
  K.opettaja_id = O.id;

Sana AS ei ole pakollinen, eli voimme lyhentää kyselyä lisää:

SELECT
  K.nimi, O.nimi
FROM
  Kurssit K, Opettajat O
WHERE
  K.opettaja_id = O.id;

Saman taulun toistaminen

Monen taulun kyselyssä voi esiintyä myös monta kertaa sama taulu, kunhan toistuvalle taululle annetaan eri nimet. Esimerkiksi seuraava kysely hakee kaikki tavat valita kahden opettajan pari:

SELECT A.nimi, B.nimi FROM Opettajat A, Opettajat B;

Kyselyn tulos on seuraava:

nimi        nimi      
----------  ----------
Kaila       Kaila     
Kaila       Luukkainen
Kaila       Kivinen   
Kaila       Laaksonen 
Luukkainen  Kaila     
Luukkainen  Luukkainen
Luukkainen  Kivinen   
Luukkainen  Laaksonen 
Kivinen     Kaila     
Kivinen     Luukkainen
Kivinen     Kivinen   
Kivinen     Laaksonen 
Laaksonen   Kaila     
Laaksonen   Luukkainen
Laaksonen   Kivinen   
Laaksonen   Laaksonen 

Liitostaulut

Taulujen välillä esiintyy yleensä kahdenlaisia suhteita:

  1. Yksi moneen -suhde: Taulun A rivi liittyy enintään yhteen taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.

  2. Monta moneen -suhde: Taulun A rivi voi liittyä useaan taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.

Tapauksessa 1 voimme lisätä tauluun A sarakkeen, joka viittaa tauluun B, kuten teimme edellisen osion esimerkissä. Tapauksessa 2 tilanne on kuitenkin hankalampi, koska yksittäinen viittaus kummankaan taulun rivissä ei riittäisi. Ratkaisuna on luoda kolmas liitostaulu (join table), joka sisältää tiedot viittauksista.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa verkkokaupassa on tuotteita ja asiakkaita ja jokainen asiakas on valinnut tiettyjä tuotteita ostoskoriin. Tietyn asiakkaan korissa voi olla useita tuotteita, ja toisaalta tietty tuote voi olla usean asiakkaan korissa.

Rakennamme tietokannan niin, että siinä on kolme taulua: Tuotteet, Asiakkaat ja Ostokset. Liitostaulu Ostokset ilmaisee, mitä tuotteita on kunkin asiakkaan ostoskorissa. Sen jokainen rivi esittää yhden parin muotoa “asiakkaan id korissa on tuote id”.

Oletetaan, että taulujen sisällöt ovat seuraavat:

id nimi hinta
1 retiisi 7
2 porkkana 5
3 nauris 4
4 lanttu 8
5 selleri 4
id nimi
1 Uolevi
2 Maija
3 Aapeli
asiakas_id tuote_id
1 2
1 5
2 1
2 4
2 5

Tämä tarkoittaa, että Uolevin korissa on porkkana ja selleri sekä Maijan korissa on retiisi, lanttu ja selleri. Aapelin korissa ei ole mitään tuotetta.

Nyt voimme hakea asiakkaat ja tuotteet seuraavasti:

SELECT
  A.nimi, T.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;

Kyselyn ideana on hakea tauluista Asiakkaat ja Tuotteet taulun Ostokset rivejä vastaavat tiedot. Jotta saamme mielekkäitä tuloksia, kytkemme rivit yhteen kahden ehdon avulla. Kysely tuottaa seuraavan tulostaulun:

nimi        nimi      
----------  ----------
Uolevi      porkkana  
Uolevi      selleri   
Maija       retiisi   
Maija       lanttu    
Maija       selleri   

Miten kysely toimii?

Voimme taas tutkia kyselyn toimintaa hakemalla kaikki sarakkeet ja poistamalla ehdot:

SELECT * FROM Asiakkaat A, Tuotteet T, Ostokset O;

Tämän kyselyn tulostaulussa on kaikki tavat valita jokin asiakas, tuote ja ostokset. Tulostaulussa on 5 * 3 * 5 = 75 riviä ja se alkaa näin:

id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      1           retiisi     7           1           2         
1           Uolevi      1           retiisi     7           1           5         
1           Uolevi      1           retiisi     7           2           1         
1           Uolevi      1           retiisi     7           2           4         
1           Uolevi      1           retiisi     7           2           5         
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      2           porkkana    5           1           5         
1           Uolevi      2           porkkana    5           2           1         
1           Uolevi      2           porkkana    5           2           4         
1           Uolevi      2           porkkana    5           2           5         
1           Uolevi      3           nauris      4           1           2         
1           Uolevi      3           nauris      4           1           5         
1           Uolevi      3           nauris      4           2           1         
1           Uolevi      3           nauris      4           2           4         
1           Uolevi      3           nauris      4           2           5         
...

Sitten kun lisäämme kyselyyn ehdot, saamme rajattua kiinnostavat rivit:

SELECT
  *
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;
id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         
2           Maija       1           retiisi     7           2           1         
2           Maija       4           lanttu      8           2           4         
2           Maija       5           selleri     4           2           5     

Kun vielä määritämme halutut sarakkeet, tuloksena on lopullinen kysely:

SELECT
  A.nimi, T.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;
nimi        nimi      
----------  ----------
Uolevi      porkkana  
Uolevi      selleri   
Maija       retiisi   
Maija       lanttu    
Maija       selleri   

Lisää ehtoja kyselyyn

Voimme lisätä kyselyyn lisää ehtoja, jos haluamme saada selville muuta ostoskoreista. Esimerkiksi seuraava kysely hakee Maijan korissa olevat tuotteet:

SELECT
  T.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id AND A.nimi = 'Maija';
nimi      
----------
retiisi   
lanttu    
selleri   

Seuraava kysely puolestaan kertoo, keiden korissa on selleri:

SELECT
  A.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id AND T.nimi = 'selleri';
nimi      
----------
Uolevi    
Maija    

Yhteenveto tauluista

Voimme käyttää koostefunktioita ja ryhmittelyä myös usean taulun kyselyissä. Ne käsittelevät tulostaulua samalla periaatteella kuin yhden taulun kyselyissä.

Tarkastellaan edelleen tietokantaa, jossa on tuotteita, asiakkaita ja ostoksia:

id nimi hinta
1 retiisi 7
2 porkkana 5
3 nauris 4
4 lanttu 8
5 selleri 4
id nimi
1 Uolevi
2 Maija
3 Aapeli
asiakas_id tuote_id
1 2
1 5
2 1
2 4
2 5

Seuraava kysely luo yhteenvedon, joka näyttää jokaisesta asiakkaasta, montako tuotetta hänen ostoskorissaan on ja mikä on tuotteiden yhteishinta.

SELECT
  A.nimi, COUNT(T.id), SUM(T.hinta)
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id
GROUP BY
  A.id;

Kyselyn tulos on seuraava:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
Uolevi      2            9           
Maija       3            19          

Uolevin korissa on siis 2 tavaraa, joiden yhteishinta on 9, ja Maijan korissa on 3 tavaraa, joiden yhteishinta on 19.

Miten kysely toimii?

Kyselyn perusta on tässä:

SELECT
  *
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;
id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         
2           Maija       1           retiisi     7           2           1         
2           Maija       4           lanttu      8           2           4         
2           Maija       5           selleri     4           2           5     

Kun kyselyyn lisätään ryhmittely GROUP BY A.id, rivit jakautuvat kahteen ryhmään sarakkeen A.id mukaan:

Ryhmä 1:

id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         

Ryhmä 2:

id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
2           Maija       1           retiisi     7           2           1         
2           Maija       4           lanttu      8           2           4         
2           Maija       5           selleri     4           2           5     

Näille ryhmille lasketaan sitten tuotteiden määrä COUNT(T.id) sekä ostosten yhteishinta SUM(T.hinta).

Huomaa, että kyselyssä ryhmittely tapahtuu sarakkeen A.id mukaan, mutta kyselyssä haetaan sarake A.nimi. Tämä on sinänsä järkevää, koska sarake A.id määrää sarakkeen A.nimi. Tämä kysely toimii SQLitessä, mutta muut tietokannat voivat vaatia, että sellaisenaan haettavan sarakkeen tulee aina esiintyä myös ryhmittelyssä. Tällöin ryhmittelyn tulisi olla GROUP BY A.id, A.nimi.

Puuttuvan rivin ongelma

Äskeinen kysely toimii sinänsä hyvin, mutta jotain puuttuu:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
Uolevi      2            9           
Maija       3            19          

Kyselyn puutteena on vielä, että tuloksissa ei ole lainkaan kolmatta tietokannassa olevaa asiakasta eli Aapelia. Koska Aapelin korissa ei ole mitään, Aapelin rivi ei yhdisty minkään muun rivin kanssa eikä pääse osaksi tulostaulua.

Olemme törmänneet ongelmaan, mutta onneksi löydämme siihen ratkaisun pian.

JOIN-syntaksi

Tähän mennessä olemme hakeneet tietoa tauluista listaamalla taulut kyselyn FROM-osassa, mikä toimii yleensä hyvin. Kuitenkin joskus on tarpeen JOIN-syntaksi, josta 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  NULL        
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  NULL
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  NULL         NULL        NULL
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.

JOIN-kyselyperhe

Itse asiassa JOIN-kyselystä on olemassa peräti neljä eri muunnelmaa:

SQLiten rajoituksena on, että vain kaksi ensimmäistä kyselytapaa ovat mahdollisia. Onneksi LEFT JOIN on yleensä se, mitä haluamme.

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, vasemman taulun 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   

Tässä tapauksessa WHERE-osan vaikutuksena on, että tulostaulussa ei ole riviä, jossa kurssina on Ohjelmoinnin perusteet.

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   

Tässä tapauksessa kursseja Ohjelmoinnin perusteet ja Tietokantojen perusteet vastaavat rivit jäävät ylimääräisiksi vasemmassa taulussa, koska niitä ei voida liittää mihinkään oikean taulun riviin ON-ehdossa.

Yhteenveto toimivaksi

Nyt voimme pureutua aiempaan ongelmaan, jossa yhteenvetokyselystä puuttui tietoa. Tietokannassamme on edelleen seuraavat taulut:

id nimi hinta
1 retiisi 7
2 porkkana 5
3 nauris 4
4 lanttu 8
5 selleri 4
id nimi
1 Uolevi
2 Maija
3 Aapeli
asiakas_id tuote_id
1 2
1 5
2 1
2 4
2 5

Muodostimme yhteenvedon ostoskoreista seuraavalla kyselyllä:

SELECT
  A.nimi, COUNT(T.id), SUM(T.hinta)
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id
GROUP BY
  A.id;

Kuitenkin ongelmaksi tuli, että Aapeli puuttuu yhteenvedosta:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
Uolevi      2            9
Maija       3            19

Ongelman syynä on, että Aapelin ostoskori on tyhjä eli kun kysely valitsee yhdistelmiä taulujen riveistä, ei ole mitään sellaista riviä, jolla esiintyisi Aapeli. Ratkaisu ongelmaan on käyttää LEFT JOIN -syntaksia näin:

SELECT
  A.nimi, COUNT(T.id), SUM(T.hinta)
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
              LEFT JOIN Tuotteet T ON T.id = O.tuote_id
GROUP BY
  A.id;

Nyt myös Aapeli ilmestyy mukaan yhteenvetoon:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
Uolevi      2            9           
Maija       3            19          
Aapeli      0            NULL         

Koska Aapelin ostoskorissa ei ole tuotteita, hintojen summaksi tulee NULL. Voimme vielä parantaa kyselyä IFNULL-funktion avulla:

SELECT
  A.nimi, COUNT(T.id), IFNULL(SUM(T.hinta),0)
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
              LEFT JOIN Tuotteet T ON T.id = O.tuote_id
GROUP BY
  A.id;

Tämän seurauksena mahdollinen NULL muuttuu arvoksi 0:

nimi        COUNT(T.id)  IFNULL(SUM(T.hinta),0)
----------  -----------  ----------------------
Uolevi      2            9           
Maija       3            19          
Aapeli      0            0

Palaamme NULL-arvojen käsittelyyn tarkemmin myöhemmin.

Miten kysely toimii?

Kun kyselyssä on useita LEFT JOIN -osia, tulkintana on, että ne yhdistävät tauluja vasemmalta oikealle. Yllä olevassa kyselyssä voimme ajatella, että ensimmäinen vaihe yhdistää taulut Asiakkaat ja Ostokset:

SELECT
  *
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id;
id          nimi        asiakas_id  tuote_id  
----------  ----------  ----------  ----------
1           Uolevi      1           2         
1           Uolevi      1           5         
2           Maija       2           1         
2           Maija       2           4         
2           Maija       2           5         
3           Aapeli      NULL        NULL

Toinen vaihe puolestaan yhdistää yllä olevan tulostaulun ja taulun Tuotteet:

SELECT
  *
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
              LEFT JOIN Tuotteet T ON T.id = O.tuote_id;
id          nimi        asiakas_id  tuote_id    id          nimi        hinta     
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      1           2           2           porkkana    5         
1           Uolevi      1           5           5           selleri     4         
2           Maija       2           1           1           retiisi     7         
2           Maija       2           4           4           lanttu      8         
2           Maija       2           5           5           selleri     4         
3           Aapeli      NULL        NULL        NULL        NULL        NULL

Molemmissa vaiheissa Aapeli pääsee osaksi tulostaulua, koska kyseinen rivi ei täsmää minkään oikean taulun rivin kanssa.