13.2.2017

SQL/Liitokset

Liitokset - haut useaan tauluun

Tavallisin liitos on valintaliitos

ehtona =, !=, <, <=, >=, >
yhtäläisyysliitos (=) yleisin (vrt. Inner join)
taulut liitetään toisiinsa yleensä avaimilla (perus- ja viiteavain) Esim.

SELECT AsiakasNimi, tilausnumero
FROM   asiakas INNER JOIN tilaus
ON     asiakas.asiakastunnus =
       tilaus.asiakastunnus;

Kvalifiointi: taulun nimi sarakenimen eteen

pakollista, jos saman niminen sarake kahdessa taulussa

Perinteinen liitos

Liitosehto WHERE-lauseessa
Esim. hae yrityksen nimi, ko. yrityksen henkilöiden nimet ja palkat yli 2500 tienaavista

SELECT yritys.yrtun, Nimi,         -- Hae yrityksen tunnus, nimi
Sukunimi, Etunimi, Palkka          -- ja henkilön nimi + palkka
FROM yritys, henkilo               -- näistä tauluista
WHERE yritys.yrtun = henkilo.yrtun -- Liitosehto!
AND Palkka > 2500                  -- Hakuehto palkalle
ORDER BY Nimi                      -- Lajittelu

Perinteinen liitos / 2

SELECT osasto.ostun, osnimi, snimi, enimi, kunta
FROM osasto, henkilo                -- monta taulua -> liitos
WHERE osasto.ostun = henkilo.ostun  -- liitosehto
AND kunta = 'TURKU'
ORDER BY osnimi

====> ====> ====> ====> ====> ====> ====> ====> ====>
Liitoksen seurauksena liitetään kaikki
liitosehdon mukaiset rivit:

Metsä, Mikko |TURKU   | 1 <-----> |1|Tietohallinto
Ranta, Laura |TAMPERE | 1 <-----> |1|Tietohallinto
Virta, Jukka |TURKU   | 3 <-----> |3|Tuotanto
Meri, Leo    |TURKU   | 3 <-----> |3|Tuotanto
Jokinen, Lyly|TAMPERE | 3 <-----> |3|Tuotanto
====> ====> ====> ====> ====> ====> ====> ====> ====>

Näistä hyväksytään vain turkulaiset, joten tuloksena:

+ ----- + ------------- + --------- + --------- +
| ostun | osnimi        | snimi | enimi | kunta |
+ ----- + ------------- + --------- + --------- +
| 1     | Tietohallinto | Metsä | Mikko | TURKU |
| 3     | Tuotanto os.  | Virta | Jukka | TURKU |
| 3     | Tuotanto os.  | Meri  | Leo   | TURKU |
+ ----- + ------------- + ---------- + -------- +

Puuttuva liitosehto?

SELECT osasto.ostun, osnimi, snimi, enimi, kunta
FROM osasto, henkilo
WHERE kunta = 'TURKU'
ORDER BY osnimi

====> ====> ====> ====> ====> ====> ====> ===
+ ---------- + ----------- + ---------- + ---
| ostun | osnimi | snimi | enimi | kunta |
+ ---------- + ----------- + ---------- + ---
| 4 | Markkinointi  | Metsä | Mikko | TURKU |
| 4 | Markkinointi  | Virta | Jukka | TURKU |
| 4 | Markkinointi  | Meri  | Leo   | TURKU |
| 2 | Talous toim.  | Virta | Jukka | TURKU |
| 2 | Talous toim.  | Meri  | Leo   | TURKU |
| 2 | Talous toim.  | Metsä | Mikko | TURKU |
| 1 | Tietohallinto | Metsä | Mikko | TURKU |
| 1 | Tietohallinto | Virta | Jukka | TURKU |
| 1 | Tietohallinto | Meri  | Leo   | TURKU |
| 3 | Tuotanto os.  | Meri  | Leo   | TURKU |
| 3 | Tuotanto os.  | Metsä | Mikko | TURKU |
| 3 | Tuotanto os.  | Virta | Jukka | TURKU |
+ ---------- + ----------- + ---------- + ---
12 rows

Jokaiseen osastoon (4kpl) on liitetty jokainen turkulainen (3kpl)
=> 4*3 => 12 tulosriviä, kuten pyydettiin! => Ei järkeä!

Taulujen viitenimi

Tauluille voi antaa viitenimen SELECT-lauseen FROM-osassa
Annettuja viitenimiä voi käyttää kyselyn muissa osissa

SELECT os.ostun, os.osnimi, he.snimi, he.enimi, he.kunta
FROM osasto os , henkilo he
WHERE os.ostun = he.ostun
AND he.kunta = 'TURKU'
ORDER BY os.osnimi;

Käy usein myös AS-muodossa:

...
FROM osasto AS os, henkilo AS he
...

Uusi liitossyntaksi (suositellaan)

Liitosehto FROM-lauseessa
Hae yrityksen nimi, ko. yrityksen henkilöiden nimet ja palkat yli 2500 tienaavista

SELECT yritys.Yrtun, Nimi,      -- Hae yrityksen tunnus, nimi
Sukunimi, Etunimi, Palkka       -- henkilön suku-/etunimi+palkka
FROM yritys                     -- YRITYS –taulusta, joka
INNER JOIN henkilo              -- liitetään HENKILO-tauluun
ON yritys.Yrtun = henkilo.Yrtun -- Liitosehto
WHERE Palkka > 2500             -- Hakuehto palkalle
ORDER BY Nimi                   -- Lajittelu

Uusi liitossyntaksi / 2

Selkeämpi
Pakollinen ON-osa (ehto ei unohdu)

SELECT os.ostun, os.osnimi, he.snimi,
       he.enimi, he.kunta
FROM osasto AS os INNER JOIN henkilo AS he
  ON os.ostun = he.ostun
WHERE he.kunta = 'TURKU'
ORDER BY os.osnimi;

Monen taulun liitos (Taululkm >=3)

Hae henkilöiden (henkilo) projektit (projekti) ja tunnit projektia (proj_henk) kohti käyttäen henkilöiden ja projektien nimiä. Lajittele tehtyjen tuntien mukaan
Kolme taulua, kaksi liitosehtoa
Voit mietiä ensimmäisen liitoksen tulosjoukkoa "väliaikaisena" tauluna, johon liitetään kolmas taulu toisella liitosehdolla

SELECT he.htun, he.snimi, pr.pnimi, ph.tunnit
FROM henkilo AS he INNER JOIN proj_henk AS ph
  ON he.htun = ph.htun
INNER JOIN projekti AS pr
  ON pr.ptun = ph.ptun
ORDER BY ph.tunnit DESC;

Ulkoliitos (outer join)

Mukaan myös ne rivit, jotka eivät täytä join-ehtoa; esim.:

haetaan myös ne asiakkaat, joille ei löydy tilauksia

SELECT AsiakasNimi, tilausnumero
FROM asiakas
LEFT OUTER JOIN tilaus
ON asiakas.asiakastunnus = tilaus.asiakastunnus;

OUTER-sana voidaan jättää pois SQL-standardissa

Ulkoliitosesimerkki

Hae kaikki projektit henkilöineen ja henkilöiden tunnit projektia kohtia. Hae mukaan myös ne projektit, joissa ei ole vielä merkitty henkilöille tunteja (ei henkilöitä)

SELECT pr.ptun, pr.pnimi, ph.htun, ph.tunnit
FROM projekti AS pr LEFT OUTER JOIN
proj_henk AS ph
  ON pr.ptun = ph.ptun
ORDER BY ph.tunnit DESC;

Järjestys oleellinen: vasemmalla olevasta taulusta halutaan kaikki rivit

Liitos itseensä

Liitos itseensä: tarvitaan aliasnimeä
Esim. haetaan JAMKin eri yksiköt (org.hierarkiasta):

SELECT a.nimi
FROM asiakas a, asiakas b
WHERE a.emoyritystunnus = b.asiakastunnus
AND b.asiakastunnus = 'JAMK';

Joissakin tuotteissa pitää käyttää AS-sidesanaa aliasnimen edessä, joissakin pitää olla välilyönti
Kun aliasnimi on otettu käyttöön, ei alkuperäistä nimeä saa käyttää sarakkeen kvalifioinnissa

Liitos itseensä / Esimerkki

Kuvittele sama taulu itsensä kanssa vierekkäin ja tee niiden liitos

Hae ne käyttäjät, joilla on sama palkka kuin Jukka Virralla (htun=2134). Huom: Ei väliä mikä palkka Virralla on.

SELECT a.snimi, a.enimi, a.palkka
FROM henkilo AS a INNER JOIN henkilo AS b
ON a.palkka = b.palkka
AND b.htun = 2134;

Yhdiste (UNION)

Kaksi taulua (tai useampia) yhdistetään samantyyppisen tiedon avulla

Sama määrä saman tietotyypin sarakkeita
Vain yksi ORDER BY, jossa käytetään ekan SELECTin saraketunnuksia tai järjestysnumeroa

Yhdiste (UNION)

Esim. opettajien ja opiskelijoiden puhelinnumerot:

SELECT OpeNimi AS Nimi, PuhNro, ’opettaja’
FROM opettaja
UNION
SELECT OpiskNimi, PuhNro, ’opiskelija’
FROM opiskelija
ORDER BY 1;

Yhdiste (UNION)

Akateeminen esimerkki: Hae ne käyttäjät, joilla on sama palkka kuin Jukka Virralla (htun=2134) tai Mikko Metsällä (htun=2234)

SELECT a.snimi, a.enimi, a.palkka
FROM henkilo AS a INNER JOIN henkilo AS b
  ON a.palkka = b.palkka
AND b.htun = 2134
UNION
SELECT a.snimi, a.enimi, a.palkka
FROM henkilo AS a INNER JOIN henkilo AS b
  ON a.palkka = b.palkka
AND b.htun = 2234;

Saman saisi tietysti siistimmin aikaan muokkaamalla ensimmäisen SELECT-lauseen ehtoa muotoon AND (b.htun = 2134 OR b.htun = 2234)

Liitokset ja ryhmittely

Hae osastoittain osaston tunnus, nimi ja palkat yhteensä sekä palkkojen keskiarvo

SELECT os.ostun, os.osnimi, sum(he.palkka) AS summa,
       CAST(AVG(he.palkka) AS DECIMAL(7,2)) AS keskiarvo
FROM osasto AS os LEFT OUTER JOIN henkilo AS he
  ON os.ostun = he.ostun
GROUP BY os.ostun, os.osnimi