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:
-
Yksi moneen -suhde: Taulun A rivi liittyy enintään yhteen taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.
-
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:
JOIN: toimii kuten tavallinen kahden taulun kyselyLEFT JOIN: jos vasemman taulun rivi ei yhdisty mihinkään oikean taulun riviin, se valitaan kuitenkin mukaan erikseenRIGHT JOIN: jos oikean taulun rivi ei yhdisty mihinkään vasemman taulun riviin, se valitaan kuitenkin mukaan erikseenFULL JOIN: sekä vasemmasta että oikeasta taulusta valitaan erikseen mukaan rivit, jotka eivät yhdisty toisen taulun riviin
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.