Osa 1

Tiedon hakeminen useammasta tietokantataulusta

Harjoitellaan seuraavaksi tiedon hakemista useammasta tietokantataulusta.

Käytössämme on asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta.

Alla olevaan tietokannan kuvaukseen on merkitty tietokantataulujen ja sarakkeiden nimien lisäksi sarakkeiden tyypit — esimerkiksi merkintä id:Integer tarkoittaa kokonaislukutyyppistä saraketta, jonka nimi on id, ja merkintä postitoimipaikka:String merkkijonomuotoista saraketta, jonka nimi on postitoimipaikka. Tietokantatauluihin on merkitty myös pää- ja viiteavaimet.

  • Asiakas((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Ravintola((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Annos((pk) id:Integer, (fk) ravintola_id -> Ravintola, nimi:String, koko:String, hinta:double)
  • Tilaus((pk) id:Integer, (fk) asiakas_id -> Asiakas, aika:Date, kuljetustapa:String, vastaanotettu:Boolean, toimitettu:Boolean)
  • RaakaAine((pk) id:Integer, nimi:String)
  • AnnosRaakaAine((fk) annos_id - > Annos, (fk) raaka_aine_id -> RaakaAine)
  • TilausAnnos((fk) tilaus_id - > Tilaus, (fk) annos_id -> Annos)

Kuvana tietokanta näyttää seuraavalta.

Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta.  
Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta.

Asiakkaasta tallennetaan asiakkaan yksilöivä pääavain (id), nimi, puhelinnumero, katuosoite, postinumero, ja postitoimipaikka. Jokaisesta tilauksesta tallennetaan tilauksen yksilöivä pääavain (id), tilauksen tehneen asiakkaan yksilöivä viiteavain (asiakas_id), aika, kuljetustapa sekä tieto siitä onko tilaus vastaanotettu ja toimitettu.

Taulu TilausAnnos on liitostaulu, joka sisältää viiteavaimen tauluun Tilaus (tilaus_id) ja viiteavaimen tauluun Annos (annos_id). Käytännössä taulu kytkee tilaukset annoksiin: kussakin tilauksessa voi olla monta annosta.

Taulu Annos sisältää annoksen yksilöivät pääavaimen (id), viiteavaimen annosta tarjoavaan ravintolaan (ravintola_id), sekä tiedon annoksen nimestä, koosta ja hinnasta. Taulu Ravintola sisältää ravintolan tiedot.

Annoksen sisältämät raaka-aineet saadaan selville taulujen RaakaAine ja AnnosRaakaAine avulla. Taulu RaakaAine sisältää raaka-aineen yksilöivät pääavaimen (id) sekä raaka-aineen nimen, ja Taulu AnnosRaakaAine on liitostaulu, joka kytkee RaakaAine-taulun ja Annos-taulun rivejä, ja joka kertoo mitä raaka-aineita kukin annos sisältää.

Kunkin ravintolan tarjoamat annokset

Tarkastellaan kysymystä "Mitä annoksia kukin ravintola tarjoaa?".

Löytääksemme kysymykseen vastauksen, meidän tulee tunnistaa tietokannassa polku tietokantataulusta Ravintola tietokantatauluun Annos. Yhteys löytyy nopeasti — jokaisella annoksella on tieto ravintolasta, missä annosta tarjoillaan. Tämä tieto on tallennettu tietokantataulun Annos sisältämään viiteavaimeen ravintola_id, joka viittaa taulun Ravintola pääavaimeen id. Tämä tarkoittaa käytännössä sitä, että kun tiedämme annoksen, tiedämme myös missä ravintolassa kyseistä annosta tarjotaan.

Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna ravintolan ja annoksen välinen yhteys.  
Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna ravintolan ja annoksen välinen yhteys.

Aloitetaan kysely tietokantataulusta Ravintola. Listataan ensin kaikkien ravintoloiden nimet.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola;

Liitetään tähän seuraavaksi tietokantataulu Annos. Liitos tapahtuu Annos-taulun sisältämän Ravintola-tauluun viittaavan viiteavaimen ravintola_id ja Ravintola-taulun pääavaimen välillä.

SELECT Ravintola.nimi AS ravintola, Annos.nimi AS annos
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id;

Lauseke JOIN Annos ON Annos.ravintola_id = Ravintola.id pyytää käytännössä tietokannanhallintajärjestelmää liittämään kyselyn edellisen osan tuloksen osaksi Annos-taulusta haettavaa tietoa. Liitos tapahtuu annetun liitosehdon (ON Annos.ravintola_id = Ravintola.id) avulla, eli rivit yhdistetään taulun Annos sarakkeen ravintola_id ja taulun Ravintola sarakkeen id perusteella.

Järjestetään lopuksi tulokset ravintolan ja annoksen perusteella.

SELECT Ravintola.nimi AS ravintola, Annos.nimi AS annos
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  ORDER BY Ravintola.nimi, Annos.nimi;

Tarkastellaan yllä kuvattua SQL-kyselä konkreettisesti taulujen esimerkkisisältöjen avulla. Oletetaan, että tietokantataulun Ravintola sisältö on seuraava (sisällöstä puuttuu tarkoituksella sarakkeita).

idnimi
1Grillenium Falcon
2Open Sesame
3Bean Me Up

Oletetaan, että taulun Annos sisältö on seuraava.

idravintola_idnimi
11Chewbacca
21Jack to the Future
32Open Sesame Salad
42Baba Ghannouj

Kun suoritamme alla kuvatun kyselyn, joka liittää edellä kuvatut taulut yhteen, saamme tuloksena yhden taulun.

SELECT *
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id;

Yllä kuvatun kyselyn tuloksena muodostunut taulu sisältää liitettyjen taulujen kaikki sarakkeet sekä ne rivit, jotka on pystytty yhdistämään. Yhdistäminen tapahtuu yllä olevassa kyselyssä taulun Ravintola pääavaimen id ja taulun Annos tauluun Ravintola viittaavan viiteavaimen ravintola_id avulla.

Tulos on seuraava.

idnimiidravintola_idnimi
1Grillenium Falcon11Chewbacca
1Grillenium Falcon21Jack to the Future
2Open Sesame32Open Sesame Salad
2Open Sesame42Baba Ghannouj

Sarakkeiden määrittely kyselyyn on helppoa. Alla kyselyn tuloksena muodostuvaan tauluun otetaan vain ravintolan ja annoksen nimi, jonka lisäksi sarakkeet nimetään kuvaavammin.

SELECT Ravintola.nimi AS ravintola, Annos.nimi AS annos
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id;

Kyselyn tulos on seuraava.

ravintolaannos
Grillenium FalconChewbacca
Grillenium FalconJack to the Future
Open SesameOpen Sesame Salad
Open SesameBaba Ghannouj

Voimme halutessamme rajata kyselyn tuloksia. Rajaus tapahtuu edellisestä osasta tutulla WHERE-komennolla, jota seuraa rajausehdot. Alla tulokset rajataan vain ravintolaan, jonka nimi on Open Sesame.

SELECT Ravintola.nimi AS ravintola, Annos.nimi AS annos
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  WHERE Ravintola.nimi = 'Open Sesame';

Kyselyn tulos on seuraava.

ravintolaannos
Open SesameOpen Sesame Salad
Open SesameBaba Ghannouj

Myös järjestäminen tapahtuu tutulla tavalla. Järjestetään tulokset lopulta annoksen nimen perusteella. Järjestyskomento ORDER BY tulee kyselyn loppuun.

SELECT Ravintola.nimi AS ravintola, Annos.nimi AS annos
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  WHERE Ravintola.nimi = 'Open Sesame'
  ORDER BY Annos.nimi;

Kyselyn tulos on seuraava.

ravintolaannos
Open SesameBaba Ghannouj
Open SesameOpen Sesame Salad

Seuraavaksi tutustutaan useampia tauluja yhdistäviin kyselyihin. Kyselyiden perusperiaate on sama kuin yllä.

Annoksen raaka-aineiden selvittäminen

Tarkastellaan seuraavaksi kysymystä "Mitä raaka-aineita annoksessa 'The Fortress Stilt Fisherman Indulgence' on?".

Löytääksemme kysymykseen vastauksen, meidän tulee tunnistaa tietokannassa polku tietokantataulusta Annos tietokantatauluun RaakaAine. Yhteys löytyy kulkemalla taulusta Annos taulun AnnosRaakaAine kautta tauluun RaakaAine.

Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna annoksen ja raaka-aineen välinen yhteys.  
Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna annoksen ja raaka-aineen välinen yhteys.

Aloitetaan kysely tietokantataulusta Annos. Haetaan ensin annos, jonka nimi on 'The Fortress Stilt Fisherman Indulgence'.

SELECT * FROM Annos
  WHERE Annos.nimi = 'The Fortress Stilt Fisherman Indulgence';

Kytketään kyselyyn seuraavaksi taulu AnnosRaakaAine.

SELECT * FROM Annos
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  WHERE Annos.nimi = 'The Fortress Stilt Fisherman Indulgence';

Ja kytketään kyselyyn tämän jälkeen tietokantataulu RaakaAine.

SELECT * FROM Annos
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Annos.nimi = 'The Fortress Stilt Fisherman Indulgence';

Muutetaan tulostusta vielä niin, että tulostuksessa on vain raaka-aineen nimet, jotka on järjestetty aakkosjärjestykseen.

SELECT RaakaAine.nimi FROM Annos
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Annos.nimi = 'The Fortress Stilt Fisherman Indulgence';
  ORDER BY RaakaAine.nimi

Asiakkaan syömät raaka-aineet

Oletetaan, että haluamme seuraavaksi etsiä kaikki raaka-aineet, joita Leevi-niminen asiakas on saattanut syödä. Ensimmäinen askeleemme ongelman ratkaisuun on polun etsiminen taulusta Asiakas tauluun RaakaAine. Polku löytyy.

Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna yhteys asiakkaasta raaka-aineeseen.  
Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna yhteys asiakkaasta raaka-aineeseen.

Aloitetaan polkua kuvaavan SQL-kyselyn rakentaminen. Aloitamme taulusta Asiakas ja etsimme polkua tauluun RaakaAine. Jotta pääsemme taulusta Asiakas tauluun RaakaAine, tulee meidän vierailla tauluissa TilausAnnos, Annos ja AnnosRaakaAine.

Haemme aluksi asiakkaan nimeltä Leevi.

SELECT Asiakas.nimi AS asiakas
  FROM Asiakas
  WHERE Asiakas.nimi = 'Leevi';

Kytketään tähän seuraavaksi kaikki Leevin tekemät tilaukset, jotka löytyvät taulusta Tilaus.

SELECT Asiakas.nimi AS asiakas
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  WHERE Asiakas.nimi = 'Leevi';

Yhdistetään edelliseen kyselyyn taulu TilausAnnos.

SELECT Asiakas.nimi AS asiakas
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  JOIN TilausAnnos ON TilausAnnos.tilaus_id = Tilaus.id
  WHERE Asiakas.nimi = 'Leevi';

Yhdistetään tähän taulu Annos.

SELECT Asiakas.nimi AS asiakas
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  JOIN TilausAnnos ON TilausAnnos.tilaus_id = Tilaus.id
  JOIN Annos ON Annos.id = TilausAnnos.annos_id
  WHERE Asiakas.nimi = 'Leevi';

Yhdistetään tähän seuraavaksi taulu AnnosRaakaAine.

SELECT Asiakas.nimi AS asiakas
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  JOIN TilausAnnos ON TilausAnnos.tilaus_id = Tilaus.id
  JOIN Annos ON Annos.id = TilausAnnos.annos_id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  WHERE Asiakas.nimi = 'Leevi';

Olemme lähes perillä! Yhdistetään kyselyyn vielä taulu RaakaAine.

SELECT Asiakas.nimi AS asiakas
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  JOIN TilausAnnos ON TilausAnnos.tilaus_id = Tilaus.id
  JOIN Annos ON Annos.id = TilausAnnos.annos_id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Asiakas.nimi = 'Leevi';

Kyselymme yhdistää taulut ja löytää Leevi-nimiseen asiakkaaseen liittyvät tiedot. Edellisestä kyselystä raaka-aineiden tulostaminen tosin puuttuu, joten lisätään tämä vielä osaksi kyselyä.

SELECT Asiakas.nimi AS asiakas, RaakaAine.nimi AS raaka_aine
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  JOIN TilausAnnos ON TilausAnnos.tilaus_id = Tilaus.id
  JOIN Annos ON Annos.id = TilausAnnos.annos_id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Asiakas.nimi = 'Leevi';

Kyselyn tuloksessa sama raaka-aine voi esiintyä useammalla rivillä. Tämä johtuu siitä, että Leevi on voinut tilata saman ruuan useampaan otteeseen. Lisäämällä SELECT-komennon jälkeen määreen DISTINCT voimme rajata tulosrivit niin, että samansisältöinen rivi ei esiinny tulostuksessa useampaan kertaan. Alla olevassa esimerkissä kukin raaka-aine esiintyy korkeintaan kerran.

SELECT DISTINCT Asiakas.nimi AS asiakas, RaakaAine.nimi AS raaka_aine
  FROM Asiakas
  JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  JOIN TilausAnnos ON TilausAnnos.tilaus_id = Tilaus.id
  JOIN Annos ON Annos.id = TilausAnnos.annos_id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Asiakas.nimi = 'Leevi';

Valkosipulia Unicafessa syöneet

Tarkastellaan yhä samaa tietokantaa. Etsitään tällä kertaa ne henkilöt, jotka ovat syöneet valkosipulia Unicafe-nimisessä ravintolassa.

Haluamme siis yhdistää taulut Asiakas, Ravintola, ja RaakaAine. Tämä onnistuu seuraamalla taulujen välisiä yhteyksiä. Asiakkaasta ravintolaan pääsee taulujen Tilaus, TilausAnnos, ja Annos kautta. Asiakkasta RaakaAineeseen pääsee taas taulujen Tilaus, TilausAnnos, Annos, ja AnnosRaakaAine kautta. Pääsemme ottamaan kaikki taulut mukaan kyselyymme!

Tietokantakaaviossa on korostettuna kyselyssä tarvittavat yhteydet.  
Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta. Tietokantakaaviossa on korostettuna kyselyssä tarvittavat yhteydet.

Aloitetaan SQL-kyselyn rakentaminen. Aloitetaan taulusta Ravintola, sillä haemme Unicafe-nimistä ravintolaa, ja pyritään tauluun RaakaAine, sillä olemme kiinnostuneita valkosipulista.

Haemme aluksi ravintolan nimeltä Unicafe.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  WHERE Ravintola.nimi = 'Unicafe';

Kytketään tähän seuraavaksi kaikki ravintolan annokset. Annokset löytyvät taulusta Annos.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  WHERE Ravintola.nimi = 'Unicafe';

Yhdistetään edelliseen kyselyyn taulu AnnosRaakaAine.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  WHERE Ravintola.nimi = 'Unicafe';

Yhdistetään tähän taulu RaakaAine.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Ravintola.nimi = 'Unicafe';

Ja rajataan raaka-aineet valkosipuliin.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli';

Käytössämme on nyt polku ravintolasta raaka-aineeseen. Kyselyssä rajaudutaan vain Unicafe-nimiseen ravintolaan sekä sen tarjoamiin annoksiin, joissa on valkosipulia.

Etsitään seuraavaksi asiakkaat, jotka ovat tilanneet kyseisen ravintolan valkosipulia sisältäviä annoksia.

Aloitetaan kytkemällä edellä muodostettuun kyselyyn taulu TilausAnnos.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  JOIN TilausAnnos ON TilausAnnos.annos_id = Annos.id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli';

Kytketään tilaus tietoon tilauksessa tilattuihin annoksiin.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  JOIN TilausAnnos ON TilausAnnos.annos_id = Annos.id
  JOIN Tilaus ON Tilaus.id = TilausAnnos.tilaus_id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli';

Ja kytketään lopulta tilaukset niihin liittyviin asiakkaisiin.

SELECT Ravintola.nimi AS ravintola
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  JOIN TilausAnnos ON TilausAnnos.annos_id = Annos.id
  JOIN Tilaus ON Tilaus.id = TilausAnnos.tilaus_id
  JOIN Asiakas ON Asiakas.id = Tilaus.asiakas_id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli';

Halusimme alunperin tiedon asiakkaista, jotka ovat tilanneet valkosipulia sisältävää ruokaa Unicafe-nimisestä ravintolasta. Tällä hetkellä kyselymme tulostaa ravintoloiden nimet — muutetaan kyselyä siten, että se tulostaa asiakkaiden nimet.

SELECT Asiakas.nimi AS asiakas
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  JOIN TilausAnnos ON TilausAnnos.annos_id = Annos.id
  JOIN Tilaus ON Tilaus.id = TilausAnnos.tilaus_id
  JOIN Asiakas ON Asiakas.id = Tilaus.asiakas_id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli';

Tälläkin kertaa asiakkaat tulostuvat useampaan kertaan mikäli he ovat tehneet useita valkosipulia sisältäviä tilauksia Unicafe-ravintolasta. Lisätään kyselyyn määre DISTINCT, jolloin kukin asiakas näkyy tuloksessa korkeintaan kerran.

SELECT DISTINCT Asiakas.nimi AS asiakas
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  JOIN TilausAnnos ON TilausAnnos.annos_id = Annos.id
  JOIN Tilaus ON Tilaus.id = TilausAnnos.tilaus_id
  JOIN Asiakas ON Asiakas.id = Tilaus.asiakas_id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli';

Järjestetään tulokset vielä lopuksi asiakkaan nimen perusteella, mikä edesauttaa tulosten lukemista.

SELECT DISTINCT Asiakas.nimi AS asiakas
  FROM Ravintola
  JOIN Annos ON Annos.ravintola_id = Ravintola.id
  JOIN AnnosRaakaAine ON AnnosRaakaAine.annos_id = Annos.id
  JOIN RaakaAine ON RaakaAine.id = AnnosRaakaAine.raaka_aine_id
  JOIN TilausAnnos ON TilausAnnos.annos_id = Annos.id
  JOIN Tilaus ON Tilaus.id = TilausAnnos.tilaus_id
  JOIN Asiakas ON Asiakas.id = Tilaus.asiakas_id
  WHERE Ravintola.nimi = 'Unicafe'
    AND RaakaAine.nimi = 'Valkosipuli'
  ORDER BY Asiakas.nimi;

Muita liitoskyselyitä

Tutustuimme edellä JOIN-avainsanaan, jonka avulla liitetään tauluja toisiinsa. Taulut yhdistetään toisiinsa pää- ja viiteavainten perusteella seuraavasti:

SELECT * FROM Asiakas
    JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id;

Avainsana JOIN on lyhennetty kirjoitusmuoto komennosta INNER JOIN. Yllä olevan kyselyn voi kirjoittaa myös seuraavassa muodossa.

SELECT * FROM Asiakas
  INNER JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id;

Tarkastellaan kyselyn tulosta hieman pienemmillä tauluilla. Alla olevissa taulussa Asiakas asiakkaasta on tallennettu pääavain (id) sekä nimi. Tilauksesta on tallennettu viiteavain asiakkaaseen (asiakas_id) ja tilauksen tekoajan.

id nimi
1Sylvi
2Lilja
3Leevi
4Anton
asiakas_idaika
12018-10-01 16:20
12017-11-01 14:18
22016-05-01 12:11

Kysely, joka käyttää INNER JOIN-muotoista liitosta palauttaa vain ne asiakkaat, jotka ovat tehneet tilauksia.

SELECT * FROM Asiakas
  INNER JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id;

Yllä oleva kysely tuottaa seuraavanlaisen taulun.

id nimiasiakas_idaika
1Sylvi12018-10-01 16:20
1Sylvi12017-11-01 14:18
2Lilja22016-05-01 12:11

Kyselyn tuloksessa asiakkaita Leevi ja Anton ei esiinny lainkaan!

JOIN (eli INNER JOIN) tyyppinen kysely valitsee vastaukseen vain ne rivit, joiden kohdalla ehto Asiakas.id = Tilaus.asiakas_id pätee. Vastauksessa on siis vain ne Asiakas-taulun ja Tilaus-taulun rivit, joissa Asiakas-taulun pääavaimelle löytyy vastine Tilaus-taulun viiteavaimista. Mikäli vastineita on useampi, kuten yllä, jokaiselle vastineelle luodaan kyselyn tulostauluun oma erillinen rivi.

Tämä tarkoittaa sitä, että osa riveistä voi jäädä tulostamatta. Tämä on usein toivottavaa: mikäli halutaan asiakkaat ja heidän tilaukset, ei kyselyssä todennäköisesti haluta tietoa asiakkaista, jotka eivät ole tehneet tilauksia.

Tutustutaan seuraavaksi liitoskyselyyn, jonka avulla voidaan saada selville myös ne asiakkaat, jotka eivät ole tehneet yhtäkään tilausta.

LEFT JOIN

Vaihtamalla JOIN-liitoskysely LEFT JOIN-liitoskyselyyn vastaus sisältää myös ne LEFT JOIN-komentoa edeltävän taulun rivit, joille liitosehto ei täyttynyt.

Alla oleva kysely listaa tilauksia tehneiden asiakkaiden lisäksi myös ne asiakkaat, joilla ei ole yhtäkään tilausta. Tällöin tilaukseen liittyvä vastauksen osa jää tyhjäksi.

SELECT * FROM Asiakas
  LEFT JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id;
id nimiasiakas_idaika
1Sylvi12018-10-01 16:20
1Sylvi12017-11-01 14:18
2Lilja22016-05-01 12:11
3Leevi
4Anton

Tyhjä arvo on NULL-arvo, jota voi käyttää myös osana kyselyn ehtoa. Ehto sarake on tyhjä kirjoitetaan muodossa sarake IS NULL. Asiakkaat, jotka eivät ole tehneet yhtäkään tilausta löytyy esimerkiksi seuraavasti.

SELECT * FROM Asiakas
  LEFT JOIN Tilaus ON Asiakas.id = Tilaus.asiakas_id
  WHERE Tilaus.asiakas_id IS NULL;

Kyselyn tulos on seuraava.

id nimiasiakas_idaika
3Leevi
4Anton

Liitoskyselytyypit lyhyesti

Kyselyn JOIN-tyypin voi muotoilla usealla eri tavalla:

  • INNER JOIN — palauta vain ne rivit, joihin valintaehto kohdistuu.

  • LEFT JOIN — palauta kaikki FROM-komentoa seuraavan taulun rivit, ja liitä niihin LEFT JOIN-komentoa seuraavan taulun rivit niiltä kohdin, kuin se on ON-liitosehdossa määritellyn ehdon mukaan mahdollista

  • RIGHT JOIN — palauta kaikki RIGHT JOIN-komentoa seuraavan taulun rivit, ja liitä niihin FROM-komentoa seuraavan taulun rivit niiltä kohdin, kuin se on ON-liitosehdossa määritellyn ehdon mukaan mahdollista

  • FULL JOIN — palauta kaikki FROM-komentoa seuraavan taulun rivit sekä kaikki FULL JOIN-komentoa seuraavan taulun rivit, ja liitä ne toisiinsa niiltä kohdin, kuin se on ON-liitosehdossa määritellyn ehdon mukaan mahdollista

Pääsit aliluvun loppuun! Jatka tästä seuraavaan osaan: