Liitoskyselyt

Useita tauluja kyselyn FROM-osassa

Kun kyselyn FROM-osassa annetaan useita tauluja, on tuloksena näiden taulujen ristitulo . Liitos (eli rivien yhdistely ehdon perusteella) saadaan aikaan antamalla liitosehdot kyselyn WHERE-osassa.

Seuraavassa esimerkissä haetaan kevään 2001 'Ohjelmointi 1' kursseille ilmoittautuneiden opiskelijoiden nimet. Tätä varten on tehtävä liitos  taulujen ilmoittautuminen, opintojakso ja opiskelija välillä. Ilmoittautumiset saadaan selville taulusta ilmoittautuminen ja opiskelijoiden nimet taulusta opiskelija.Taulu opintojakso tarvitaan, jotta saataisiin selville kurssin 'Tietokantojen perusteet' kurssikoodi. Taulua kurssi ei tarvita.

Esimerkki L1 (ks. esimerkkikannan kaavio )

select distinct sukunimi, etunimet
from opiskelija, opintojakso, ilmoittautuminen
where 
  ilmoittautuminen.opiskelijanumero=
    opiskelija.opiskelijanumero and
  ilmoittautuminen.kurssikoodi=
    opintojakso.kurssikoodi and
  opintojakso.nimi_suomeksi=
    'Ohjelmointi 1' and
  ilmoittautuminen.lukuvuosi=2001 and
  ilmoittautuminen.lukukausi='K' and
  ilmoittautuminen.perumisaika is null
order by sukunimi, etunimet

Liitosehdot esimerkissä ovat

ilmoittautuminen.opiskelijanumero= opiskelija.opiskelijanumero ja
ilmoittautuminen.kurssinumero=opintojakso.kurssinumero .

Tässä kyselyssä ei tulostauluun oteta mitään tietoja tauluista opintojakso ja ilmoittautuminen. Ne osallistuvat liitokseen vain siksi, että liitoksen avulla saadaan rajattua taulusta opiskelija poimittavia arvoja. Kyselyn FROM osassa voi siis olla tauluja, joista ei oteta mitään tietoja tulostauluun. Kaikki sellaiset taulut, joista otetaan tietoja tulostauluun on lueteltava kyselyn FROM-osassa.

Taulujen järjestyksellä kyselyn FROM-osassa ei ole merkitystä kyselyn vastauksen kannalta. Samoin WHERE-osan ehdot voi antaa missä järjestyksessä tahansa. Näillä kummallakin saattaa kuitenkin järjestelmäkohtaisesti olla merkitystä kyselyn suoritusaikaan, joten erityisesti isoja tauluja käsiteltäessä on syytä selvittää onko taulujen tai ehtojen järjestyksellä merkitystä ja millainen merkitys niillä on.

Liitosehdon täydellisyys

Koska liitosehdot karsivat riviyhdistelmiä ristitulosta, on oleellista, että liitosehdot ovat täydellisinä mukana WHERE-osassa. Muuten tulokseen tulee ylimääräisiä rivejä. Seuraavassa esimerkissä taulujen liittämiseen tarvitaan moniosaiset liitosehdot. Kyselyllä selvitetään 'Ohjelmointi 1' kurssin luentoja pitäneiden opettajien nimet.

Esimerkki L2. (ks. esimerkkikannan kaavio)

select tyo.lukuvuosi,tyo.lukukausi,tyo.kurssinumero,
   sukunimi, etunimet 
from opettaja, opintojakso, opetustehtava, tyo
where 
  opettaja.opetunnus=tyo.opettaja and
  tyo.kurssikoodi= opetustehtava.kurssikoodi and
  tyo.lukukausi=opetustehtava.lukukausi and
  tyo.lukuvuosi=opetustehtava.lukuvuosi and
  tyo.kurssinumero= opetustehtava.kurssinumero and
  tyo.opetusnumero= opetustehtava.opetusnumero and
  opetustehtava.kurssikoodi=opintojakso.kurssikoodi and
  opetustehtava.tyyppi='luento' and
  opintojakso.nimi_suomeksi=
    'Ohjelmointi 1' 
order by tyo.lukuvuosi,tyo.lukukausi,tyo.kurssinumero

Opetustehtava ja tyo taulut liitetään vertaamalla taulussa tyo olevaa viiteavainta taulun opetustehtava pääavaimeen. Taulujen kytkeytymista havainnollista seuraava kuva

Koska taulun avain on moniosainen (5 saraketta) tarvitaan liitosehtoon viisi alkeisehtoa. Minkä tahansa näistä puuttuminen vääristäisi tuloksen. Jos esimerkiksi ehto

tyo.opetusnumero= opetustehtava.opetusnumero

puuttuisi, kytkettäisiin luennointitehtävään kaikki kyseisen kurssin opettajat. Jos taas ehto

tyo.lukukausi=opetustehtava.lukukausi

puuttuisi, kytkettäisiin tietyn lukukauden luennointitehtäviin myös eri lukukausina saman numeroista tehtävää hoitavat opettajat. Kokeile miten ehtojen poisottaminen vaikuttaa tulokseen.

Edellisessä esimerkissä samanniminen sarake esiintyy useassa taulussa, joten nimi on tarkennettava sekä tulostaulun määrittelyssä että ehdoissa.

Yleisin taulujen liitostapa on kytkeä taulut yhteen vertaamalla toisessa taulussa olevaa viiteavainta viitatun taulun pääavaimeen .

Taulujen tilapäinen uudelleennimeäminen

Taulu voidaan kyselyn sisäisesti nimetä uudelleen antamalla sille viitenimi (correlation name). Standardin mukaan Viitenimi annetaan kyselyn FROM-osassa taulunimen perässä valinnaisen avainsanan AS jälkeen. Oraclessa avainsanaa 'AS' ei tässä tilanteessa sallita. Jos taululle annetaan viitenimi on kaikki viittaukset tauluun tehtävä käyttäen tätä nimeä. Viitenimeä voi käyttää vaikkapa kirjoitustyön määrän vähentämiseen, esimerkki alla:

Esimerkki L3. (ks. esimerkkikannan kaavio )

select tyo.lukuvuosi,tyo.lukukausi,tyo.kurssinumero,
   sukunimi, etunimet 
from opettaja, opintojakso oj, 
     opetustehtava ot, tyo
where 
  opettaja.opetunnus=tyo.opettaja and
  tyo.kurssikoodi= ot.kurssikoodi and
  tyo.lukukausi=ot.lukukausi and
  tyo.lukuvuosi=ot.lukuvuosi and
  tyo.kurssinumero= ot.kurssinumero and
  tyo.opetusnumero= ot.opetusnumero and
  ot.kurssikoodi=oj.kurssikoodi and
  ot.tyyppi='luento' and
  oj.nimi_suomeksi=
    'Ohjelmointi 1' 
order by tyo.lukuvuosi,tyo.lukukausi,tyo.kurssinumero

Taululle on välttämättä annettava viitenimi, mikäli sama taulu esiintyy from osaan useaan kertaan. Viitenimi erottelee tällöin taulun esiintymät. Seuraavassa esimerkissä yritetään löytää opintojaksopareja, joille on annettu sama englanninkielinen nimi, mutta kyseessä on eri kurssi.

Esimerkki L4. (ks. esimerkkikannan kaavio )

select eka.nimi_engl as eng, eka.kurssikoodi as koodi1, 
       eka.nimi_suomeksi as nimi1,
       toka.kurssikoodi as koodi2, toka.nimi_suomeksi as nimi2  
from opintojakso eka, opintojakso toka
where eka.nimi_engl=toka.nimi_engl and
      eka.kurssikoodi<toka.kurssikoodi
order by eng

Seuraava kuva havainnollistaa taulujen käyttöä kyselyssä.

Jos yhdelle tulosriville halutaan saada tietoja usealta saman lähtötaulun riviltä, on lähtötaulu otettava FROM-osaan niin monta kertaa kuin kytkettäviä rivejä on. Jos vaikkapa halutaan tietoja kolmelta riviltä on sama taulu annettava kolmesti. Samoin on meneteltävä, jos saman taulun rivit on muuten kytkettävä yhteen tulosrivin aikaansaamiseksi. Edellä oli esimerkki tällaisesta kytkennästä. Seuraavassa esimerkissä halutaan liitosoperaatioita käyttäen saada selville opettajat, jotka sekä luennoivat että ohjaavat harjoituksia samoilla kevään 2001 kursseilla. Yksinkertaisempia tapoja tämän kyselyn esittämiseen käsitellään myöhemmin.

Esimerkki L5. (ks. esimerkkikannan kaavio )

select 
   distinct sukunimi, etunimet, nimi_suomeksi 
from opettaja, opintojakso oj, 
     opetustehtava luento, 
     opetustehtava harj, 
     tyo luennointi, tyo harj_ohjaus
where 
  opettaja.opetunnus=luennointi.opettaja and
  opettaja.opetunnus=harj_ohjaus.opettaja and
  luennointi.kurssikoodi= luento.kurssikoodi and
  luennointi.lukukausi=luento.lukukausi and
  luennointi.lukuvuosi=luento.lukuvuosi and
  luennointi.kurssinumero= luento.kurssinumero and
  luennointi.opetusnumero= luento.opetusnumero and
  luento.kurssikoodi=oj.kurssikoodi and
  luento.tyyppi='luento' and
  harj_ohjaus.kurssikoodi=harj.kurssikoodi and
  harj_ohjaus.lukukausi= harj.lukukausi and
  harj_ohjaus.lukuvuosi= harj.lukuvuosi and
  harj_ohjaus.kurssinumero= harj.kurssinumero and
  harj_ohjaus.opetusnumero= harj.opetusnumero and
  harj.kurssikoodi=oj.kurssikoodi and
  harj.tyyppi='harjoitus' and
  luento.kurssikoodi=harj.kurssikoodi and
  luento.lukukausi=harj.lukukausi and
  luento.lukuvuosi=harj.lukuvuosi and
  luento.kurssinumero= harj.kurssinumero and
  luennointi.lukuvuosi=2001 and
  luennointi.lukukausi='K'

Esimerkin kysely sisältää varsin monimutkaiset liitosehdot. Alla oleva kuva havainnollistaa kyselyä.

Vaihtoehtoinen liitosnotaatio ja ulkoliitos

SQL-92 standardissa esiteltiin vaihtoehtoinen tapa liitoksen määrittelyyn. Tämä esitystapa mahdollistaa myös ulkoliitoksen esittämisen. Esitystavassa kyselyn FROM-osaan voidaan ottaa liitostulos.

Esimerkki L6. (ks. esimerkkikannan kaavio )

kurssi INNER JOIN opintojakso 
ON kurssi.kurssikoodi=opintojakso.kurssikoodi

Liitostapoja on määritelty neljä:

• inner: normaaliliitos
• left outer: vasemmanpuoleinen ulkoliitos
• right outer: oikeanpuoleinen ulkoliitos
• full outer: molemminpuoleinen ulkoliitos

Alla esimerkki ulkoliitoksesta. Esimerkissä laaditaan luettelo Opintojaksojen opetuksesta eri lukukausina. Tulokseen halutaan myös sellaiset opintojaksot, joita ei ole opetettu.

Esimerkki L7. (ks. esimerkkikannan kaavio )

select opintojakso.nimi_suomeksi nimi, opintojakso.kurssikoodi koodi, 
       kurssi.lukuvuosi, kurssi.lukukausi 
from opintojakso left outer join kurssi  
     on kurssi.kurssikoodi=opintojakso.kurssikoodi
order by opintojakso.nimi_suomeksi

Kyselyjen tehokkuudesta

Valinnat ja erityisesti liitokset ovat keskeisesti kyselyjen tehokkuuteen vaikuttava tekijä.  Liitosoperaatiossa riville on löydettävä ehdon täyttävät parit joko samasta tai jostain toisesta taulusta. Liitoksen muodostamiseen on käytössä useita strategioita, joiden valinta riippuu siitä minkälaisia teknisen tason rakenteita tauluilla on ja minkä kokoisia taulut ovat.

Rivien hakua taulusta jonkin sarakkeen perusteella voidaan tehostaa määrittelemällä kyseiseen sarakkeeseen perustuva hakuindeksi (index). Vertailukohdaksi käy kirjan avainsanojen hakemisto. Avainsarakkeeseen perustuva hakuindeksi nopeuttaa avaimen perusteella tapahtuvia hakuja merkittävästi. Jos esimerkiksi taulussa olisi 10000 riviä, niin jonkin satunnaisen rivin haku ilman hakuindeksiä vaatisi, että käydään läpi keskimäärin puolet taulusta. Usein yhteen levymuistin lohkoon mahtuu useita taulun rivejä. Oletetaan, että tässä esimerkkitilanteessa lohkoon mahtuisi 10 riviä. Keskimääräinen hakuaika olisi tällöin ilman hakuindeksiä noin 500 levyhaun vaatima aika. Hakuindeksiä käytettäessä päästään tämän kokoisella taululla noin kolmeen (3) levyhakuun. Avaimen perusteella tapahtuvat haut ovat tarpeen esimerkiksi tilanteissa, joissa liitos tehdään avainta ja siihen viittaavaa viiteavainta vertaamalla. Avaimeen perustuva haku joudutaan tekemään myös aina lisäyksen yhteydessä avaimen yksikäsitteisyyden varmistamiseksi. Jotkin järjestelmät, esim. Oracle, tekevätkin automaattisesti hakuindeksin taulun avaimen perusteella tarvitsematta mitään muita määrityksiä. Hakuindeksi voi perustua yhteen tai useampaan sarakkeeseen.

Hakuindeksien määrittelyn syntaksi ei sisälly SQL-standardiin. Yleinen indeksin määrittely on kuitenkin rakenteeltaan seuraava

Unique-määre hakuindeksin määrittelyssä tarkoittaa sitä, että sama indeksoitavan sarakkeen arvo ei voi esiintyä taulussa kuin yhdellä rivillä. Esimerkiksi avain täyttää tämän ehdon.

Hakuindeksit eivät varsinaisesti kuulu tämän kurssin sisältöön, mutta niiden määrittelyyn on syytä perehtyä, jos ollaan tekemisissä isojen taulujen kanssa. Hakuindeksin käyttö ei näy mitenkään kyselyssä. Sen hyväksikäyttö on kyselyn optimoijan tehtävä, mutta indeksien määrittely ja perustaminen on tietokannan suunnittelijan vastuulla.