Luku 3

Viittaukset ja kyselyt

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ä ilmoittamalla 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;

Itse asiassa 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 
Jatka tästä seuraavaan osaan: