3. Monen taulun kyselyt

Taulujen viittaukset

Keskeinen idea tietokannoissa on, että taulun rivi voi viitata toisen taulun riviin. Tällöin voimme muodostaa kyselyn, joka kerää 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 oikeastaan?

Ideana on, että kun kyselyssä on monta taulua, kyselyn tulosrivien lähtökohtana ovat kaikki tavat valita rivien yhdistelmiä tauluista. Tämän jälkeen WHERE-osan ehdoilla voi määrittää, mitkä 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 ne eivät liity toisiinsa mitenkään. Esimerkiksi ensimmäinen tulosrivi kertoo vain, että on olemassa kurssi Laskennan mallit ja toisaalta on olemassa opettaja Kaila. Tämän vuoksi 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ä parantaa kyselyä 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 niille 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, 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 x korissa on tuote y”.

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

Tuotteet
idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4
Asiakkaat
idnimi
1Uolevi
2Maija
3Aapeli
Ostokset
asiakas_idtuote_id
12
15
21
24
25

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ä.

Esimerkki

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

Tuotteet
idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4
Asiakkaat
idnimi
1Uolevi
2Maija
3Aapeli
Ostokset
asiakas_idtuote_id
12
15
21
24
25

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:

id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         
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, ja kysely toimii mainiosti SQLitessä. Muissa tietokannoissa vaatimuksena voi kuitenkin olla, 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

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 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.

JOIN-kyselyperhe

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

SQLiten rajoituksena on kuitenkin, 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, 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   

Yhteenveto toimivaksi

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

Tuotteet
idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4
Asiakkaat
idnimi
1Uolevi
2Maija
3Aapeli
Ostokset
asiakas_idtuote_id
12
15
21
24
25

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

Ratkaisu

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                     

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    

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      

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