Kirjoittaja: Antti Laaksonen (2009).
Tässä oppaassa tutustumme aiempaa monimutkaisempiin tietokantahakuihin, joissa tietoa haetaan samaan aikaan monesta taulusta. Tällöin SELECT-kyselyyn liittyy useita tauluja, joiden rivit yhdistetään toisiinsa sopivalla tavalla. Käytettävät esimerkit pohjautuvat edellisen oppaan tilanteisiin, joissa joka viikko tarjouksessa on jokin tuote ja tuotteet muodostavat erikoishintaisia tuotepaketteja.
Tässä oppaassa käytössä on aiemmista osista tuttu taulu tuotteet:
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 2 | porkkana | 2 | | 3 | peruna | 3 | | 4 | retiisi | 4 | | 5 | lanttu | 1 | | 6 | selleri | 3 | | 7 | nauris | 4 | | 8 | purjo | 2 | +----+----------+-------+
Lisäksi luodaan taulu tarjoukset, jossa kerrotaan eri viikkojen tarjoustuotteet. Kenttä viikko sisältää viikon numeron, ja kenttä tuote_id on viittaus tauluun tuotteet, eli siinä on jonkin tuotteen tunnusnumero.
CREATE TABLE tarjoukset (
id INT PRIMARY KEY AUTO_INCREMENT,
viikko INT,
tuote_id INT
);Lisätään viikkojen 26–30 tarjoustuotteiksi selleri, kaali, peruna, selleri ja nauris:
INSERT INTO tarjoukset (viikko, tuote_id) VALUES (26, 6); INSERT INTO tarjoukset (viikko, tuote_id) VALUES (27, 1); INSERT INTO tarjoukset (viikko, tuote_id) VALUES (28, 3); INSERT INTO tarjoukset (viikko, tuote_id) VALUES (29, 6); INSERT INTO tarjoukset (viikko, tuote_id) VALUES (30, 7);
Taulun sisältö on nyt seuraava:
+----+--------+----------+ | id | viikko | tuote_id | +----+--------+----------+ | 1 | 26 | 6 | | 2 | 27 | 1 | | 3 | 28 | 3 | | 4 | 29 | 6 | | 5 | 30 | 7 | +----+--------+----------+
Lisäksi luodaan taulu paketit, jossa on tuotepaketteja, sekä taulu sisallot, jossa kerrotaan, mitä tuotteita eri paketeissa on. Taulussa sisallot on viittaus sekä tauluun tuotteet että tauluun paketit. Taulun avain muodostuu kahdesta kentästä, minkä vuoksi se täytyy merkitä erikseen.
CREATE TABLE paketit (
id INT PRIMARY KEY AUTO_INCREMENT,
nimi TEXT,
hinta INT
);
CREATE TABLE sisallot (
tuote_id INT,
paketti_id INT,
PRIMARY KEY (tuote_id, paketti_id)
);Lisätään kaksi tuotepakettia, "Isoäidin herkut" ja "Voimapari":
INSERT INTO paketit (nimi, hinta) VALUES ('Isoäidin herkut', 8);
INSERT INTO paketit (nimi, hinta) VALUES ('Voimapari', 6);Paketti "Isoäidin herkut" sisältää nauriksen, porkkanan ja retiisin:
INSERT INTO sisallot (tuote_id, paketti_id) VALUES (7, 1); INSERT INTO sisallot (tuote_id, paketti_id) VALUES (2, 1); INSERT INTO sisallot (tuote_id, paketti_id) VALUES (4, 1);
Paketti "Voimapari" sisältää retiisin ja sellerin:
INSERT INTO sisallot (tuote_id, paketti_id) VALUES (4, 2); INSERT INTO sisallot (tuote_id, paketti_id) VALUES (6, 2);
Taulut paketit ja sisallot näyttävät nyt tältä:
+----+-----------------+-------+ | id | nimi | hinta | +----+-----------------+-------+ | 1 | Isoäidin herkut | 8 | | 2 | Voimapari | 6 | +----+-----------------+-------+
+----------+------------+ | tuote_id | paketti_id | +----------+------------+ | 2 | 1 | | 4 | 1 | | 4 | 2 | | 6 | 2 | | 7 | 1 | +----------+------------+
Kun SELECT-kyselyssä on monta taulua, tulostaulun lähtökohtana ovat kaikki mahdolliset tavat valita yksi rivi kustakin taulusta. Esimerkiksi seuraava kysely hakee kaikki taulujen tuotteet ja tarjoukset rivien yhdistelmät. Tulostaulussa on 40 riviä, koska taulusta tuotteet rivin voi valita 8 tavalla, taulusta tarjoukset rivin voi valita 5 tavalla ja 8 * 5 = 40.
SELECT * FROM tuotteet, tarjoukset
+----+----------+-------+----+--------+----------+ | id | nimi | hinta | id | viikko | tuote_id | +----+----------+-------+----+--------+----------+ | 1 | kaali | 3 | 1 | 26 | 6 | | 2 | porkkana | 2 | 1 | 26 | 6 | | 3 | peruna | 3 | 1 | 26 | 6 | | 4 | retiisi | 4 | 1 | 26 | 6 | | 5 | lanttu | 1 | 1 | 26 | 6 | | 6 | selleri | 3 | 1 | 26 | 6 | | 7 | nauris | 4 | 1 | 26 | 6 | | 8 | purjo | 2 | 1 | 26 | 6 | | 1 | kaali | 3 | 2 | 27 | 1 | | 2 | porkkana | 2 | 2 | 27 | 1 | | 3 | peruna | 3 | 2 | 27 | 1 | | 4 | retiisi | 4 | 2 | 27 | 1 | | 5 | lanttu | 1 | 2 | 27 | 1 | | 6 | selleri | 3 | 2 | 27 | 1 | | 7 | nauris | 4 | 2 | 27 | 1 | | 8 | purjo | 2 | 2 | 27 | 1 | | 1 | kaali | 3 | 3 | 28 | 3 | | 2 | porkkana | 2 | 3 | 28 | 3 | | 3 | peruna | 3 | 3 | 28 | 3 | | 4 | retiisi | 4 | 3 | 28 | 3 | | 5 | lanttu | 1 | 3 | 28 | 3 | | 6 | selleri | 3 | 3 | 28 | 3 | | 7 | nauris | 4 | 3 | 28 | 3 | | 8 | purjo | 2 | 3 | 28 | 3 | | 1 | kaali | 3 | 4 | 29 | 6 | | 2 | porkkana | 2 | 4 | 29 | 6 | | 3 | peruna | 3 | 4 | 29 | 6 | | 4 | retiisi | 4 | 4 | 29 | 6 | | 5 | lanttu | 1 | 4 | 29 | 6 | | 6 | selleri | 3 | 4 | 29 | 6 | | 7 | nauris | 4 | 4 | 29 | 6 | | 8 | purjo | 2 | 4 | 29 | 6 | | 1 | kaali | 3 | 5 | 30 | 7 | | 2 | porkkana | 2 | 5 | 30 | 7 | | 3 | peruna | 3 | 5 | 30 | 7 | | 4 | retiisi | 4 | 5 | 30 | 7 | | 5 | lanttu | 1 | 5 | 30 | 7 | | 6 | selleri | 3 | 5 | 30 | 7 | | 7 | nauris | 4 | 5 | 30 | 7 | | 8 | purjo | 2 | 5 | 30 | 7 | +----+----------+-------+----+--------+----------+
Suurin osa edellisen kyselyn tuottamista riveistä ei ole kiinnostavia, koska eri tauluista valituilla riveillä ei ole mitään tekemistä toistensa kanssa. Vain ne rivit ovat kiinnostavia, joissa taulun tuotteet kenttä id ja taulun tarjoukset kenttä tuote_id ovat samat, koska niistä riveistä näkee, mitkä tuotteet ovat tarjouksessa eri viikkoina.
Tämän kiinnostavan tiedon saamiseksi esille taulut täytyy kytkeä lisäämällä kyselyyn ehto taulun tuotteet kentän id ja taulun tarjoukset kentän tuote_id vastaavuudesta. Kun kyselyssä on monta taulua, ehdoissa mainitaan myös taulun nimi.
SELECT * FROM tuotteet, tarjoukset WHERE tuotteet.id = tarjoukset.tuote_id;
+----+---------+-------+----+--------+----------+ | id | nimi | hinta | id | viikko | tuote_id | +----+---------+-------+----+--------+----------+ | 6 | selleri | 3 | 1 | 26 | 6 | | 1 | kaali | 3 | 2 | 27 | 1 | | 3 | peruna | 3 | 3 | 28 | 3 | | 6 | selleri | 3 | 4 | 29 | 6 | | 7 | nauris | 4 | 5 | 30 | 7 | +----+---------+-------+----+--------+----------+
Tulostaulussa on vielä paljon turhaa tietoa, minkä vuoksi valitaan tarkemmin haettavat kentät.
SELECT tarjoukset.viikko, tuotteet.nimi FROM tuotteet, tarjoukset WHERE tuotteet.id = tarjoukset.tuote_id;
+--------+---------+ | viikko | nimi | +--------+---------+ | 26 | selleri | | 27 | kaali | | 28 | peruna | | 29 | selleri | | 30 | nauris | +--------+---------+
Seuraavassa on muutamia esimerkkiä tuotepakettien tarkastelusta. Nyt kyselyissä on mukana kolme taulua, jotka täytyy kytkeä toisiinsa kahden ehdon avulla.
Seuraava kysely hakee paketin "Isoäidin herkut" tuotteiden nimet:
SELECT tuotteet.nimi
FROM tuotteet, paketit, sisallot
WHERE paketit.nimi = 'Isoäidin herkut' AND
sisallot.paketti_id = paketit.id AND
sisallot.tuote_id = tuotteet.id;+----------+ | nimi | +----------+ | retiisi | | porkkana | | nauris | +----------+
Seuraava kysely hakee kaikkien pakettien tiedot:
SELECT paketit.nimi, tuotteet.nimi
FROM tuotteet, paketit, sisallot
WHERE sisallot.paketti_id = paketit.id AND
sisallot.tuote_id = tuotteet.id;+-----------------+----------+ | nimi | nimi | +-----------------+----------+ | Isoäidin herkut | retiisi | | Isoäidin herkut | porkkana | | Isoäidin herkut | nauris | | Voimapari | retiisi | | Voimapari | selleri | +-----------------+----------+
Seuraava kysely laskee, kuinka monta tuotetta eri paketeissa on:
SELECT paketit.nimi, COUNT(*) AS maara
FROM tuotteet, paketit, sisallot
WHERE sisallot.paketti_id = paketit.id AND
sisallot.tuote_id = tuotteet.id
GROUP BY paketit.id;+-----------------+-------+ | nimi | maara | +-----------------+-------+ | Isoäidin herkut | 3 | | Voimapari | 2 | +-----------------+-------+
Seuraava kysely näyttää, miten paljon paketin ostamalla säästää:
SELECT paketit.nimi,
SUM(tuotteet.hinta) AS erikseen,
paketit.hinta AS paketissa
FROM tuotteet, paketit, sisallot
WHERE sisallot.paketti_id = paketit.id AND
sisallot.tuote_id = tuotteet.id
GROUP BY paketit.id;+-----------------+----------+-----------+ | nimi | erikseen | paketissa | +-----------------+----------+-----------+ | Isoäidin herkut | 10 | 8 | | Voimapari | 7 | 6 | +-----------------+----------+-----------+
Monen taulun kyselyissä taulujen nimiä voi joutua kirjoittamaan lukuisia kertoja. Joskus on kätevää antaa tauluille väliaikaiset lyhyemmät nimet. Edellisen kyselyn voisi kirjoittaa myös näin:
SELECT p.nimi,
SUM(t.hinta) AS erikseen,
p.hinta AS paketissa
FROM tuotteet AS t, paketit AS p, sisallot AS s
WHERE s.paketti_id = p.id AND
s.tuote_id = t.id
GROUP BY p.id;Tarkastellaan lopuksi tilannetta, jossa halutaan laskea jokaisesta tuotteesta, kuinka monta kertaa se on ollut viikon tuote.
Luonnollinen ratkaisuajatus on muodostaa kysely seuraavasti:
SELECT tuotteet.nimi, COUNT(*) FROM tuotteet, tarjoukset WHERE tuotteet.id = tarjoukset.tuote_id GROUP BY tuotteet.id;
+---------+----------+ | nimi | COUNT(*) | +---------+----------+ | kaali | 1 | | peruna | 1 | | selleri | 2 | | nauris | 1 | +---------+----------+
Tästä listasta puuttuvat kuitenkin tuotteet, jotka eivät ole olleet koskaan viikon tuotteina. Ongelmana on, että jos tuote ei esiinny lainkaan taulussa tarjoukset, sitä ei valita koskaan mukaan tauluista tuotteet ja tarjoukset muodostettaviin rivipareihin.
Ratkaisu on käyttää merkintää JOIN, joka on vaihtoehtoinen tapa yhdistää tietoa eri tauluista. Siinä voi käyttää lisämerkintöjä LEFT ja RIGHT, jotka tarkoittavat, että vasemmasta tai oikeasta taulusta otetaan mukaan myös rivit, joilla ei ole vastinetta toisessa taulussa.
Seuraava kysely laskee kaikkien tuotteiden esiintymiskerrat:
SELECT tuotteet.nimi, COUNT(tarjoukset.id) FROM tuotteet LEFT JOIN tarjoukset ON tuotteet.id = tarjoukset.tuote_id GROUP BY tuotteet.id;
+----------+----------------------+ | nimi | COUNT(tarjoukset.id) | +----------+----------------------+ | kaali | 1 | | porkkana | 0 | | peruna | 1 | | retiisi | 0 | | lanttu | 0 | | selleri | 2 | | nauris | 1 | | purjo | 0 | +----------+----------------------+
Kyselyn toiminta on helpompi ymmärtää, jos mukaan otetaan kaikki kentät:
SELECT * FROM tuotteet LEFT JOIN tarjoukset ON tuotteet.id = tarjoukset.tuote_id GROUP BY tuotteet.id;
+----+----------+-------+------+--------+----------+ | id | nimi | hinta | id | viikko | tuote_id | +----+----------+-------+------+--------+----------+ | 1 | kaali | 3 | 2 | 27 | 1 | | 2 | porkkana | 2 | NULL | NULL | NULL | | 3 | peruna | 3 | 3 | 28 | 3 | | 4 | retiisi | 4 | NULL | NULL | NULL | | 5 | lanttu | 1 | NULL | NULL | NULL | | 6 | selleri | 3 | 1 | 26 | 6 | | 7 | nauris | 4 | 5 | 30 | 7 | | 8 | purjo | 2 | NULL | NULL | NULL | +----+----------+-------+------+--------+----------+
Neljälle tuotteelle (porkkana, retiisi, lanttu ja purjo) ei ole vastinetta taulussa tarjoukset, minkä vuoksi jokainen niistä saa tulostauluun yhden rivin, jossa kaikki taulun tarjoukset kentät ovat tyhjiä. Lisäksi COUNT(tarjoukset.id) ottaa huomioon vain ne rivit, joilla tarjoukset.id ei ole tyhjä.
Huomio! Kommentoi tässä ainoastaan tämän oppaan hyviä ja huonoja puolia. Älä kirjoita muita kysymyksiä tähän. Jos koodisi ei toimi tai tarvitset muuten vain apua ohjelmoinnissa, lähetä viesti keskusteluun.