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
- veergude arv peab olema sama;
- veergude andmetüübid peavad olema samad (vajadusel saab mõningaid andmetüüpe päringus muuta
CASTfunktsiooni abil (näiteks SELECT CAST (1 AS TEXT); lause muudab täisarvu 1 sõneks); - igas
SELECTlauses 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 enim 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.
UNION poolt ühendatavate SELECT laused võivad sisaldada ka WHERE ja HAVING tingimusi. Näiteks tahame selliseid ees- ja perenime algustähti, mida esineks enam kui 10 korral:
SELECT LEFT(eesnimi,1) AS algustäht, COUNT(*) AS arv, 'e' AS "mis nimest" FROM isikud GROUP BY LEFT(eesnimi,1) HAVING COUNT(*) > 10 UNION ALL SELECT LEFT(perenimi,1), COUNT(*), 'p' FROM isikud GROUP BY LEFT(perenimi,1) HAVING COUNT(*) > 10 ORDER BY 2 DESC, 1;
Tingimused võivad olla ka erinevad, näiteks oleks võinud kirjutada ka esimesele tingimuseks, et eesnimede tähed, mida esineb enam kui 10 korral ning teisele tingimuseks, et perenimed, mis algavad K, L või M-iga.
Vaata videost, kuidas kasutada operaatorit UNION:
JOIN
Oleme juba vaadanud, kuidas JOIN … ON abil siduda tabeleid. Erinevaid võimalusi tabeleid ühendada pakubki JOIN operaator. JOIN tüüp määrab, milline alamhulk ühendatud tabelitest tagastatakse.
Järgmiseks vaatleme erinevaid JOIN tüüpe detailsemalt.
INNER JOIN
INNER JOIN puhul pannakse tulemusse kui vasakpoolse tabeli vastav veerg = parempoolse tabeli vastav veerg ehk tagastatakse vasakpoolse ja parempoolse tabeli ühisosa.
Näiteks tahame väljastada isikud (ees- ja perenimi) ja nende klubid (klubi nimi ning asukoht). Pärime INNER JOIN abil ühisosa tabelitest isikud (vasakpoolne) ja klubid (parempoolne). JOIN tingimuseks on isikud.klubi = klubi.id.
SELECT eesnimi, perenimi, nimi, asukoht FROM isikud INNER JOIN klubid ON isikud.klubi = klubid.id;
Näeme, et kahe tabeli tulemused on horisontaalselt ühendatud st tulemuses on mõlema tabeli veerud. UNION puhul olid päringud vertikaalselt ühendatud.
Samuti näeme, et tühjasid välju ei esine. Seda seetõttu, et tulemuses on toodud mõlema tabeli ühisosa. INNER JOIN ja JOIN, mida oleme praktikumis kirjutanud, on funkstionaalselt samad (annavad sama tulemuse), kuid öeldakse, et kasutades INNER JOINi on SQL-koodi lihtsam lugeda eriti kui see sisaldab veei teisi JOIN tüüpe.
LEFT JOIN
LEFT JOIN väljastab kõik kirjed vasakpoolsest tabelist ja vastavalt JOIN tingimusele kirjed parempoolsest tabelist. Kui parempoolsest tabelist vastet ei leita, siis lisatakse vasakpoolsest tabelist võetud kirjele juurde parempoolse tulemuse tühjad NULL väljad. Näiteks meil on tabelis ka isikuid, kes ei kuulunud ühtegi klubisse. INNER JOIN abil saime ainult need isikud, kel on ka klubi olemas. Kasutades nüüd LEFT JOINi väljastatakse tulemustabelisse ka klubisse mittekuuluvad isikud.
SELECT eesnimi, perenimi, nimi, asukoht FROM isikud LEFT JOIN klubid ON isikud.klubi = klubid.id;
Paneme tähele, et vasakpoolne LEFT poolel on kõik andmed ja paremal RIGHT on väljad, mis vastasid kas JOIN tingimusele isikud.klubi = klubid.id või siis kui sellist vastavust ei leitud (näiteks Tarmo ja Tiina Kooser), siis lisati sinna NULL väärtused.
RIGHT JOIN
RIGHT JOIN väljastab kõik kirjed parempoolsest tabelist ja vastavalt JOIN tingimusele kirjed vasakpoolsest tabelist. Kui vasakpoolsest tabelist vastet ei leita, siis lisatakse tühjad NULL väljad.
Näiteks soovime kõikide klubide nimesid ja millised isikud sinna kuuluvad, kui klubis isikuid pole, siis klubinimi peaks ikka päringutabelis olema. Sooritame selle päringu RIGHT JOIN abil:
SELECT eesnimi, perenimi, nimi, asukoht FROM isikud RIGHT JOIN klubid ON isikud.klubi = klubid.id;
Kui kerime päringutabeli lõppu, siis lõpus on ka kaks klubi, millel pole liikmeid.
Mõtle, kuidas sama tulemust oleks saanud kirjutada kasutades LEFT JOIN'i. Kui soovid oma teadmist kontrollida, ava test.
FULL JOIN
FULL JOIN väljastab kõik kirjed vasak- ja parempoolsest tabelist. Juhul kui JOIN vasakpoolsest tabelist vastet ei leia, lisatakse tulemusse vastavad tühjad NULL väljad vasakule ja juhul kui JOIN parempoolsest tabelist vastet ei leia, lisatakse tulemusse vastavad tühjad NULL väljad paremale.
SELECT eesnimi, perenimi, nimi, asukoht FROM isikud FULL JOIN klubid ON isikud.klubi = klubid.id;
Loomulikult saab erinevate JOIN tüüpidega kasutada ka päringulause teisi osasid nagu WHERE, GROUP BY, HAVING, ORDER BY ja LIMIT.
SQL Visualizer https://sql-joins.leopard.in.ua/
| Alampäringud | Tabelite loomine ja välisvõti |