On sanomattakin selvää, että liitokset ovat yksi relaatiotietokantojen tunnistettavimmista ominaisuuksista. Liitosten avulla voimme yhdistää tiedot yhdestä tai useammasta taulukosta toisiinsa liittyvän ehdon perusteella yhtenäisen tietotoiminnon luomiseksi.
SQL:ssä on erilaisia liitoksia, joista jokaisella on ainutlaatuinen tapa käsitellä tietoja osallistuvista taulukoista tai tuloksena olevasta joukosta. Yksi yleisimmistä liitostyypeistä SQL:ssä on OUTER JOIN.
OUTER JOIN SQL:ssä hakee kaikki vastaavat rivit mukana olevista taulukoista sekä vastaamattomat rivit yhdestä tai molemmista taulukoista. Se on kätevä, kun käsittelet taulukoita, jotka sisältävät NULL-arvoja tai puuttuvia joukkoja.
Tutkitaanpa tarkemmin, mitä nämä liitokset tekevät, miten ne toimivat ja kuinka voimme käyttää niitä SQL-tietokannassa.
Vaatimukset:
Tässä opetusohjelmassa työskentelemme MySQL 8.0:n kanssa ja käytämme Sakilan mallitietokantaa. Voit kuitenkin käyttää mitä tahansa muuta sopivaksi katsomaasi tietojoukkoa.
Ulkoliitostyypit
SQL:ssä on kolme päätyyppiä ULKOJOHTAJA. Tämän tyyppisiä ULKOliitoksia ovat mm.
-
Vasen OUTER JOINS
-
Oikea ULKOLIITTYMÄ
-
TÄYDET ULKOliitokset
LEFT OUTTER JOINS:n tapauksessa liitos hakee kaikki rivit vasemmasta taulukosta ja vain vastaavat rivit oikeasta taulukosta. Jos oikeanpuoleisesta taulukosta ei löydy vastaavia rivejä, liitos palauttaa oikean taulukon sarakkeiden NULL-arvot.
Tämä on samanlainen kuin RIGHT OUTTER JOIN. Se kuitenkin hakee kaikki rivit oikeasta taulukosta, mutta vain vastaavat rivit vasemmasta taulukosta. Jos vasemmanpuoleisesta taulukosta ei ole vastaavia rivejä, liitos sisältää NULL-arvot vasemman taulukon sarakkeille.
Lopuksi meillä on TÄYDET ULKOliitokset. Tämäntyyppinen liitos yhdistää sekä OIKEA- että VASEN-ulkoliitokset. Seurauksena on, että liitos hakee kaikki rivit, kun joko vasemmassa tai oikeassa taulukossa on osuma. Jos vastaavuutta ei löydy, liitos palauttaa taulukon sarakkeiden NULL-arvot ilman vastaavuutta.
SQL OUTER JOINin syntaksi
Seuraava ilmaisee SQL OUTER JOINin syntaksin. On kuitenkin hyvä pitää mielessä, että syntaksi voi vaihdella hieman kohdetietokantakoneen mukaan.
Seuraava on yleinen rakenne:
VALITSE sarakkeettaulukosta 1
[VASEN | OIKEA | FULL] OUTER JOIN table2
PÄÄLLÄ taulukko1.sarakkeen_nimi = taulukko2.sarakkeen_nimi;
OUTER JOINin syntaksi SQL:ssä on melko itsestään selvä.
Esimerkkejä:
Tarkastellaanpa esimerkkiä siitä, kuinka voimme soveltaa eri tyyppisiä ULKOJOHTAJA SQL:ssä.
Kuten mainitsimme, käytämme esittelyyn Sakilan näytetietokantaa. Tässä tapauksessa käytämme 'asiakas'- ja 'maksu'-taulukoita.
Esimerkki 1: LEFT OUTTER JOIN
Aloitetaan ULKOINEN LIITTYMINEN. Oletetaan, että haluamme hakea kaikki asiakastiedot ja heidän maksutietonsa, jos niitä on saatavilla.
Tämä tekee LEFT ULKOLIITTYMISTÄ soveltuvan, koska haluamme kaikki asiakastiedot (vasemmalla) ja maksutiedot, jos ne ovat saatavilla (oikealla).
Jos asiakas ei ole suorittanut maksua, liitos näyttää NULL-arvot maksuun liittyville sarakkeille.
Esimerkki on seuraava:
VALITSEc.customer_id,
c.first_name,
c.last_name,
p.summa,
p.maksupäivä
FROM
asiakas c
LEFT OUTTER JOIN maksu p
PÄÄLLÄ
c.asiakastunnus = p.asiakastunnus;
Annettuun kyselyyn sisällytämme sarakkeet 'customer_id', 'first_name' ja 'last_name' 'asiakas'-taulukosta. Sisällytämme myös summan ja 'maksupäivämäärän' 'maksu'-taulukosta.
Tämän jälkeen suoritamme LEFT OUTTER JOINin 'asiakas'- ja 'maksu'-taulukoiden välillä 'asiakastunnuksen' perusteella.
Nämä ovat kaikki asiakkaat (riippumatta siitä, onko maksu suoritettu tai ei) ja heidän maksutietonsa (jos sellaisia on).
Esimerkkituloste on seuraava:
Esimerkki 2: RIGHT OUTTER JOIN
Siirrytään nyt OIKEAAN ULKOON LIITTYMISEEN. Oletetaan, että haluamme tässä tapauksessa sisällyttää kaikki maksutiedot ja mahdollisen asiakkaan.
Tässä tapauksessa, jos asiakas suorittaa maksun, liittyminen näyttää kyseisen asiakkaan tiedot. Jos maksussa ei ole liitettyä asiakasta, se näyttää asiakkaisiin liittyvien sarakkeiden NULL-arvot.
VALITSEc.customer_id,
c.first_name,
c.last_name,
p.summa,
p.payment_date
FROM
asiakas c
RIGHT OUTER JOIN maksu p
PÄÄLLÄ
c.asiakastunnus = p.asiakastunnus;
Tuloksena oleva sarja on seuraava:
Esimerkki 3: FULL OUTTER JOIN
FULL OUTTER JOIN puolestaan hakee kaikki asiakastiedot ja maksut. Tämä sisältää kaikki asiakkaat ja kaikki maksut ja näyttää NULL-arvot, jos taulukot eivät täsmää.
VALITSEc.customer_id,
c.first_name,
c.last_name,
p.summa,
p.maksupäivä
FROM
asiakas c
FULL OUTER JOIN maksu p
PÄÄLLÄ
c.asiakastunnus = p.asiakastunnus;
On hyvä pitää mielessä, että MySQL ei tue natiivisti FULL OUTER JOINia. Sinun täytyy tehdä jiujitsu-taikaa LEFT JOIN, UNION ja RIGHT JOIN avulla. Melko ärsyttävää, voimme lisätä.
Johtopäätös
Tässä opetusohjelmassa opimme kaiken ULKOISTA LIITTYMISTÄ. Opimme mitä on ULKOJOIN SQL:ssä, OUTER JOINS -tyypit ja esimerkkejä näiden ULKOJOIN-tyyppien käytöstä.