Operaatorid UNION ja JOIN
UNION
Operaatorit UNION kasutatakse kahe või enama SELECT lause tulemustabelite ühendamiseks.
SELECT <päring 1> UNION [ALL] SELECT <päring 2>... [ORDER BY <järjestus>]
Seejuures peab silmas pidama, et igas UNION operaatoriga ühendatud SELECT lauses
peab olema sama arv veerge, veergudel peavad olema samad andmetüübid ja igas SELECT lauses peavad olema veerud samas järjekorras.
Kui kasutatakse UNION ALL, siis tulemustabelisse tuleb ka korduvaid kirjeid, üksnes UNION'i kasutades korduvaid kirjeridu pole ehk siis see töötab sarnaselt nagu SELECT DISTINCT.
Näiteks tahame teada, millist tähte kasutatakse enam nime alguses (nii ees- kui ka perenimede puhul). Tulemuse järjestame esinemissageduse alusel. Et aga eristada, milline täht näitab, et see on esimene eesnimes, kasutame konstanti 'e' ja perenime puhul 'p'. Kui oleks vaja ainult eesnimede esitähti, ja konstanti 'e' kirjutaksime päringu
SELECT LEFT(eesnimi,1) AS algustäht, COUNT(*) AS arv, 'e' FROM isikud GROUP BY LEFT(eesnimi,1);
Analoogse päringuga saaksime ka perenimede algustähed. Soovides need kaks tulemustabelit panna kokku, kirjutame päringu:
SELECT LEFT(eesnimi,1) AS algustäht, COUNT(*) AS arv, 'e' AS 'mis nimest' FROM isikud GROUP BY LEFT(eesnimi,1) UNION ALL SELECT LEFT(perenimi,1), COUNT(*), 'p' FROM isikud GROUP BY LEFT(perenimi,1) ORDER BY 2 DESC, 1;
Tulemuseks saame tabeli, mille algus näeb välja selline:
Kokku on pandud kaks päringut ja mitte nii, et esmalt esimese ja siis teise päringu tulemus, vaid järjestusega on koostatud ühine tabel.
Erinevad JOIN operaatorid
Oleme juba kasutanud operaatorit KEY JOIN, mida saab kasutada juhul, kui kahe tabeli vahel on üks välisvõti. Näiteks meie male andmebaasi tabelite Isikud ja Klubid vahel on võimalik tabeleid siduda nii WHERE osas (WHERE isikud.klubi=klubid.id) kui ka operaatoriga KEY JOIN (FROM isikud KEY JOIN klubid). Samuti oleme vaadanud, kuidas JOIN … ON abil siduda tabeleid juhul, kui tabelite vahel on mitu välisvõtit.
Meie andmebaasis tabelid Isikud ja pariid. Saame siduda ka neid nii WHERE osas (WHERE partiid.valge=isikud.id) kui ka operaatoriga JOIN … ON (FROM isikud JOIN partiid ON partiid.valge=isikud.id).
NATURAL JOIN operaatori puhul seotakse tabelid samanimeliste veergude abil. Siin peab just mõtlema, kas sellisel seosel on mõtet. Näiteks meil on Male andmebaasi kõikides tabelites samanimelised veerud id, kuid nendega pole mõtet tabeleid siduda. Kasutades loomulikku ühendit näiteks tabelite Isikud ja Klubid puhul (FROM isikud NATURAL JOIN klubid) tehakse selline ühendamine: WHERE isikud.id=klubid.id
Lisaks on aga võimalik kasutada erinevaid ühendamisvõimalusi, mida on kõige ülevaatlikum kirjeldada hulgadiagrammide abil:
|
Ühendamisvõimalus |
Selgitus |
Diagramm |
|---|---|---|
LEFT (OUTER) JOIN
| väljastab vastavad kirjed vasakust tabelist ning ühisosa kirjed parema tabeliga | 
|
RIGHT (OUTER) JOIN
| väljastab kõik kirjed paremast tabelist ning ühisosa kirjed vasaku tabeliga | 
|
(INNER) JOIN
| ühend, mis väljastab mõlema tabeli ühisosa | 
|
FULL OUTER JOIN
| väljastab kõik kirjed mõlemast tabelist | 
|
Samuti saab kasutada lisaks siin piiranguid. Näiteks LEFT OUTER JOIN … WHERE TableB.id IS NULL väljastab vaid need vasakpoolsest tabelist (Tabel A), mis ei vasta ühelegi parempoolse tabeli (Tabel B) kirjele.
Analoogselt RIGHT OUTER JOIN … WHERE TableA.id IS NULL väljastab vaid need parempoolsest tabelist (Tabel B), mis ei vasta ühelegi vasakpoolse tabeli (Tabel A) kirjele
Või näiteks FULL OUTER JOIN … WHERE TableA.Id IS NULL OR TableB.Id IS NULL väljastab kirjed, mis kuuluvad tabelitesse A või B, kuid millel pole ühisosa.
Mõned näited erinevate JOIN operaatorite kasutamise kohta.
Soovime teada, kes (ees ja perenimi) mängisid valgetega ning võitsid (lisame ka valge tulemuse). Selle saame päringuga:
SELECT DISTINCT eesnimi, perenimi, valge_tulemus FROM isikud JOIN partiid ON isikud.id=partiid.valge AND valge_tulemus=2;
On aga ka selliseid mängijaid, kes küll mängisid valgetega, kuid ühtegi korda ei võitnud. Et ka neid saada tulemustabelisse, kirjutame
SELECT DISTINCT eesnimi, perenimi, valge_tulemus FROM isikud LEFT OUTER JOIN partiid ON isikud.id=partiid.valge AND valge_tulemus=2;
Tulemustabelisse tulevad nüüd need mängijad, kes võitsid vähemalt ühel korral valgetega mängides valge_tulemusega 2 ja teistel on valge_tulemus veerus (NULL).
Teises praktikumis leidsite turniirid, mitu partiid on Arvo või Anna nimelised mängijad mänginud valgetega. Kui võtta vaid Arvo partiid, siis näete, et Arvo on osalenud ainult kahel turniiril ja need kaks turniiri väljastatakse. Kui tahame aga näha ka neid turniire, millel Arvo pole osalenud, kirjutame päringu, kus võtame vasakpoolsest ehk turniiride tabelist kõik väärtused:
SELECT turniirid.nimi, count(isikud.id) FROM turniirid LEFT OUTER JOIN partiid ON turniirid.id = partiid.turniir LEFT OUTER JOIN isikud ON isikud.id = partiid.valge AND isikud.eesnimi = 'Arvo' GROUP BY turniirid.nimi
Näiteid saate vaadata ka inglisekeesest materjalist Visual Representation of SQL Joins - CodeProject