Luku 3

Yhteenveto toimivaksi

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

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

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.