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 Teachers tietoa opettajista. Jokaisella opettajalla on id-numero, jolla voimme viitata siihen.

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Taulun sisältö voisi olla esimerkiksi seuraava:

id  name      
--  ----------
1   Kaila     
2   Luukkainen
3   Kivinen   
4   Laaksonen 

Taulussa Courses on puolestaan tietoa kursseista ja jokaisen kurssin kohdalla viittaus kurssin opettajaan.

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Tässä sarake teacher_id viittaa taulussa Teachers olevan rivin id-numeroon, minkä tuo esille merkintä REFERENCES Teachers.

Taulun sisältö voisi olla esimerkiksi seuraava:

id  name              teacher_id
--  ----------------  ----------
 Laskennan mallit  3         
 Tietoverkot       1         
 Graduseminaari    1         
 PHP-ohjelmointi   4         
 Neuroverkot       3         

Tämä tarkoittaa, että esimerkiksi kurssia Laskennan mallit luennoi opettaja, jonka id-numero on 3. Taulusta Teachers selviää, että kyseinen opettaja on Kivinen.

Voimme nyt hakea kurssit opettajineen seuraavalla kyselyllä, joka hakee tietoa samaan aikaan tauluista Courses ja Teachers:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Koska kyselyssä on monta taulua, ilmoitamme sarakkeiden taulut. Esimerkiksi Courses.name viittaa taulun Courses sarakkeeseen name.

Kysely antaa seuraavan tuloksen:

name              name     
----------------  ---------
Laskennan mallit  Kivinen  
Tietoverkot       Kaila    
Graduseminaari    Kaila    
PHP-ohjelmointi   Laaksonen
Neuroverkot       Kivinen  

Mitä tässä tapahtui?

Yllä olevassa kyselyssä uutena asiana on, että kysely koskee useaa taulua (FROM Courses, Teachers), 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 Courses, Teachers;

Koska taulussa Courses on 5 riviä ja taulussa Teachers on 4 riviä, kyselyn tulostaulussa on 5 * 4 = 20 riviä. Tulostaulu sisältää kaikki mahdolliset tavat valita ensin jokin rivi taulusta Courses ja sitten jokin rivi taulusta Teachers:

id  name              teacher_id  id  name      
--  ----------------  ----------  --  ----------
 Laskennan mallit  3           1   Kaila     
 Laskennan mallit  3           2   Luukkainen
 Laskennan mallit  3           3   Kivinen   
 Laskennan mallit  3           4   Laaksonen 
 Tietoverkot       1           1   Kaila     
 Tietoverkot       1           2   Luukkainen
 Tietoverkot       1           3   Kivinen   
 Tietoverkot       1           4   Laaksonen 
 Graduseminaari    1           1   Kaila     
 Graduseminaari    1           2   Luukkainen
 Graduseminaari    1           3   Kivinen   
 Graduseminaari    1           4   Laaksonen 
 PHP-ohjelmointi   4           1   Kaila     
 PHP-ohjelmointi   4           2   Luukkainen
 PHP-ohjelmointi   4           3   Kivinen   
 PHP-ohjelmointi   4           4   Laaksonen 
 Neuroverkot       3           1   Kaila     
 Neuroverkot       3           2   Luukkainen
 Neuroverkot       3           3   Kivinen   
 Neuroverkot       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
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Tämän seurauksena kysely alkaa antaa mielekkäitä tuloksia:

id  name              teacher_id  id  name     
--  ----------------  ----------  --  ---------
 Laskennan mallit  3           3   Kivinen  
 Tietoverkot       1           1   Kaila    
 Graduseminaari    1           1   Kaila    
 PHP-ohjelmointi   4           4   Laaksonen
 Neuroverkot       3           3   Kivinen  

Tämän jälkeen voimme vielä siistiä kyselyn tuloksia valitsemalla meitä kiinnostavat sarakkeet:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Näin päädymme samaan tulokseen kuin aiemmin:

name              name     
----------------  ---------
Laskennan mallit  Kivinen  
Tietoverkot       Kaila    
Graduseminaari    Kaila    
PHP-ohjelmointi   Laaksonen
Neuroverkot       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
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id AND Teachers.name = 'Kivinen';

Näin saamme haettua kurssit, joiden opettajana on Kivinen:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Neuroverkot       Kivinen

Taulujen lyhyet nimet

Voimme tiivistää monen taulun kyselyä antamalla tauluille vaihtoehtoiset lyhyet nimet, joiden avulla voimme viitata niihin kyselyssä. Esimerkiksi kysely

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

voidaan esittää lyhemmin näin:

SELECT
  C.name, T.name
FROM
  Courses AS C, Teachers AS T
WHERE
  C.teacher_id = T.id;

Sana AS ei ole pakollinen, eli voimme lyhentää kyselyä lisää:

SELECT
  C.name, T.name
FROM
  Courses C, Teachers T
WHERE
  C.teacher_id = T.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.name, B.name FROM Teachers A, Teachers B;

Kyselyn tulos on seuraava:

name        name      
----------  ----------
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, jossa on kolme taulua: Products, Customers ja Purchases. Liitostaulu Purchases 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	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_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
  Customers.name, Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

Kyselyn ideana on hakea tauluista Customers ja Products taulun Purchases rivejä vastaavat tiedot. Jotta saamme mielekkäitä tuloksia, kytkemme rivit yhteen kahden ehdon avulla. Kysely tuottaa seuraavan tulostaulun:

name    name    
------  --------
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 Customers, Products, Purchases;

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  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  1   retiisi   7      1            2         
 Uolevi  1   retiisi   7      1            5         
 Uolevi  1   retiisi   7      2            1         
 Uolevi  1   retiisi   7      2            4         
 Uolevi  1   retiisi   7      2            5         
 Uolevi  2   porkkana  5      1            2         
 Uolevi  2   porkkana  5      1            5         
 Uolevi  2   porkkana  5      2            1         
 Uolevi  2   porkkana  5      2            4         
 Uolevi  2   porkkana  5      2            5         
 Uolevi  3   nauris    4      1            2         
 Uolevi  3   nauris    4      1            5         
 Uolevi  3   nauris    4      2            1         
 Uolevi  3   nauris    4      2            4         
 Uolevi  3   nauris    4      2            5         
...

Sitten kun lisäämme kyselyyn ehdot, saamme rajattua kiinnostavat rivit:

SELECT
  *
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  2   porkkana  5      1            2         
 Uolevi  5   selleri   4      1            5         
 Maija   1   retiisi   7      2            1         
 Maija   4   lanttu    8      2            4         
 Maija   5   selleri   4      2            5         

Kun vielä määritämme halutut sarakkeet, tuloksena on lopullinen kysely:

SELECT
  Customers.name, Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

name    name    
------  --------
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
  Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id AND
  Customers.name = 'Maija';

name
----------
retiisi   
lanttu    
selleri   

Seuraava kysely puolestaan kertoo, keiden korissa on selleri:

SELECT
  Customers.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id AND
  Products.name = 'selleri';

name
----------
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	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_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
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Kyselyn tulos on seuraava:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
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
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id;

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  2   porkkana  5      1            2         
 Uolevi  5   selleri   4      1            5         
 Maija   1   retiisi   7      2            1         
 Maija   4   lanttu    8      2            4         
 Maija   5   selleri   4      2            5         

Kun kyselyyn lisätään ryhmittely GROUP BY Customers.id, rivit jakautuvat kahteen ryhmään sarakkeen Customers.id mukaan:

Ryhmä 1:

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
1   Uolevi  2   porkkana  5      1            2         
1   Uolevi  5   selleri   4      1            5         

Ryhmä 2:

id  name   id  name     price  customer_id  product_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(Products.id) sekä ostosten yhteishinta SUM(Products.price).

Huomaa, että kyselyssä ryhmittely tapahtuu sarakkeen Customers.id mukaan, mutta kyselyssä haetaan sarake Customers.name. Tämä on sinänsä järkevää, koska sarake Customers.id määrää sarakkeen Customers.name. Tämä kysely toimii esimerkiksi SQLitessä, mutta jotkin tietokannat vaativat tässäkin tapauksessa, että sellaisenaan haettavan sarakkeen tulee esiintyä myös ryhmittelyssä. Tällöin ryhmittelyn tulisi olla GROUP BY Customers.id, Customers.name.

Puuttuvan rivin ongelma

Äskeinen kysely toimii sinänsä hyvin, mutta jotain puuttuu:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
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
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

SELECT
  Courses.name, Teachers.name
FROM
  Courses JOIN Teachers ON Courses.teacher_id = Teachers.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 Courses ja Teachers, mutta taulussa Courses yhdeltä kurssilta puuttuu opettaja:

id  name              teacher_id
--  ----------------  ----------
 Laskennan mallit  3         
 Tietoverkot       1         
 Graduseminaari    1         
 PHP-ohjelmointi   NULL      
 Neuroverkot       3         

Rivin 4 sarakkeessa teacher_id on arvo NULL, joten jos suoritamme jommankumman äskeisen kyselyn, ongelmaksi tulee, että rivi 4 ei täsmää mihinkään taulun Teachers riviin. Tämän seurauksena tulostauluun ei tule riviä kurssista PHP-ohjelmointi:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
Neuroverkot       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
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

Nyt tulostauluun ilmestyy myös kurssi Tietokantojen perusteet ilman opettajaa:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Miten kysely toimii?

Jälleen hyvä tapa saada selkoa kyselystä on yksinkertaistaa sitä:

SELECT
  *
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

id  name              teacher_id  id    name   
--  ----------------  ----------  ----  -------
 Laskennan mallit  3           3     Kivinen
 Tietoverkot       1           1     Kaila  
 Graduseminaari    1           1     Kaila  
 PHP-ohjelmointi   NULL        NULL  NULL   
 Neuroverkot       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 eli INNER JOIN: toimii kuten tavallinen kahden taulun kysely
LEFT JOIN: jos vasemman taulun rivi ei yhdisty mihinkään oikean taulun riviin, se valitaan kuitenkin mukaan erikseen
RIGHT JOIN: jos oikean taulun rivi ei yhdisty mihinkään vasemman taulun riviin, se valitaan kuitenkin mukaan erikseen
FULL 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
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Jos käytämme sen sijasta sanaa WHERE, vasemman taulun ylimääräiset rivit jäävät pois:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers
WHERE
  Courses.teacher_id = Teachers.id;

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
Neuroverkot       Kivinen

Sinänsä kyselyssä voi esiintyä sekä ON että WHERE:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id
WHERE
  Courses.name <> 'Tietoverkot';

Tällöin ON-osa hoitaa taulujen yhdistämisen ja WHERE-osa rajaa tuloksia lisää:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Tässä tapauksessa WHERE-osan vaikutuksena on, että tulostaulussa ei ole riviä, jossa kurssina on Tietoverkot.

Jos molemmat ehdot ovatkin ON-osassa, kyselyn tulos muuttuu taas:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers
          ON Courses.teacher_id = Teachers.id AND
             Courses.name <> 'Tietoverkot';

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       NULL   
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Tässä tapauksessa kursseja Tietoverkot ja PHP-ohjelmointi 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	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Muodostimme yhteenvedon ostoskoreista seuraavalla kyselyllä:

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Kuitenkin ongelmaksi tuli, että Aapeli puuttuu yhteenvedosta:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
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
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Nyt myös Aapeli ilmestyy mukaan yhteenvetoon:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
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
  Customers.name, COUNT(Products.id), IFNULL(SUM(Products.price), 0)
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Tämän seurauksena mahdollinen NULL muuttuu arvoksi 0:

name    COUNT(Products.id)  IFNULL(SUM(Products.price), 0)
------  ------------------  ------------------------------
Uolevi  2                   9                             
Maija   3                   19                            
Aapeli  0                   0                             

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 Customers ja Purchases:

SELECT
  *
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id;

id  name    customer_id  product_id
--  ------  -----------  ----------
 Uolevi  1            2         
 Uolevi  1            5         
 Maija   2            1         
 Maija   2            4         
 Maija   2            5         
 Aapeli  NULL         NULL      

Toinen vaihe puolestaan yhdistää yllä olevan tulostaulun ja taulun Products:

SELECT
  *
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id;

id  name    customer_id  product_id  id    name      price
--  ------  -----------  ----------  ----  --------  -----
 Uolevi  1            2           2     porkkana  5    
 Uolevi  1            5           5     selleri   4    
 Maija   2            1           1     retiisi   7    
 Maija   2            4           4     lanttu    8    
 Maija   2            5           5     selleri   4    
 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.