Inner Join Vs Outer Join: Get Ready to Explore the Exact Differences Between Inner and Outer Join

Ennen kuin tutkimme Inner Join Vs Outer Joinin eroja, katsotaan ensin, mikä on SQL JOIN?

Lauseketta join käytetään tietueiden yhdistämiseen tai tietueiden käsittelyyn kahdesta tai useammasta taulusta join-ehdon avulla. Join-ehto ilmaisee, miten kunkin taulukon sarakkeet sovitetaan toisiinsa.

Join perustuu näiden taulukoiden väliseen liittyvään sarakkeeseen. Yleisin esimerkki on kahden taulukon yhdistäminen ensisijaisen avainsarakkeen ja vieraan avainsarakkeen kautta.

Esitettäkö, että meillä on taulukko, joka sisältää työntekijän palkan, ja toinen taulukko, joka sisältää työntekijän tiedot.

Tässä tapauksessa on yhteinen sarake, kuten työntekijän tunniste (employee ID), joka yhdistää nämä kaksi taulukkoa. Tämä työntekijän ID-sarake olisi työntekijän tiedot -taulujen ensisijainen avain ja vieras avain työntekijän palkka -taulussa.

On erittäin tärkeää, että kahden kokonaisuuden välillä on yhteinen avain. Voit ajatella taulukkoa entiteettinä ja avainta yhteisenä linkkinä kahden taulukon välillä, jota käytetään liitosoperaatiossa.

Periaatteessa SQL:ssä on kahdenlaisia liitoksia eli Inner Join ja Outer Join. Outer Join jaetaan edelleen kolmeen tyyppiin eli Left Outer Join, Right Outer Join ja Full Outer Join.

Tässä artikkelissa tarkastellaan Inner Joinin ja Outer Joinin eroa yksityiskohtaisesti. Cross Joins ja Unequal Joins jätetään tämän artikkelin ulkopuolelle.

Mikä on Inner Join?

Inner Join palauttaa vain ne rivit, joiden arvot täsmäävät molemmissa taulukoissa (tässä tarkastellaan, että liitos tehdään kahden taulukon välillä).

Mikä on Outer Join?

Outer Join sisältää sekä täsmäävät että jotkin ei-yhdenmukaiset rivit kahden taulukon välillä. Outer Join eroaa Inner Joinista pohjimmiltaan siinä, miten se käsittelee väärän vastaavuuden ehtoa.

Outer Joinia on 3 tyyppiä:

• Left Outer Join: Palauttaa kaikki vasemmanpuoleisen taulun rivit ja yhteensopivat tietueet molempien taulujen välillä.
• Right Outer Join: Palauttaa kaikki rivit RIGHT-taulusta ja vastaavat tietueet molempien taulujen välillä.
• Full Outer Join: Yhdistää Left Outer Joinin ja Right Outer Joinin tulokset.

Difference between Inner and Outer Join

Kuten yllä olevasta kaaviosta käy ilmi, on olemassa kaksi kokonaisuutta eli taulukko 1 ja taulukko 2 ja molemmilla taulukoilla on yhteisiä tietoja.

Inner Join palauttaa näiden taulukoiden yhteisen alueen (vihreällä tummennettu alue yllä olevassa kaaviossa) eli kaikki tietueet, jotka ovat yhteisiä taulukon 1 ja taulukon 2 välillä.

Left Outer Join palauttaa kaikki taulukon 1 rivit ja vain ne taulukon 2 rivit, jotka ovat yhteisiä myös taulukon 1 kanssa. Right Outer Join tekee juuri päinvastoin. Se antaa kaikki tietueet taulukosta 2 ja vain vastaavat vastaavat tietueet taulukosta 1.

Sen lisäksi Full Outer Join antaa kaikki tietueet taulukosta 1 ja taulukosta 2.

Aloitetaan esimerkillä, jotta tämä olisi selkeämpää.

Esitetään, että meillä on kaksi taulukkoa: EmpDetails ja EmpSalary.

EmpDetails taulukko:

EmpPalkkataulukko:

Tehdään Inner Join näille kahdelle taulukolle ja havainnoidaan tulos:

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails INNER JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

Yllä olevassa tulosjoukossa, näet, että Inner Join on palauttanut 6 ensimmäistä tietuetta, jotka esiintyivät sekä EmpDetails- että EmpSalary-tietueissa ja joilla oli sama avain i.EmployeeID. Jos siis A ja B ovat kaksi oliota, Inner Join palauttaa tulosjoukon, joka on yhtä suuri kuin ’Tietueet A:ssa ja B:ssä’ täsmäävän avaimen perusteella.

Katsotaan nyt, mitä Left Outer Join tekee.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails LEFT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

Yllä olevasta tulosjoukosta, näet, että vasen ulompi liitos on palauttanut kaikki 10 tietuetta vasemmanpuoleisesta taulusta i.eli EmpDetails-taulusta, ja koska ensimmäiset 6 tietuetta täsmäävät, se on palauttanut työntekijän palkan näiden täsmäävien tietueiden osalta.

Koska lopuilla tietueilla ei ole täsmäävää avainta oikeanpuoleisessa taulussa eli EmpSalary-taulussa, se on palauttanut niitä vastaavan NULL-arvon. Koska Lilyllä, Sitalla, Farahilla ja Jerryllä ei ole vastaavaa työntekijätunnusta EmpSalary-taulukossa, heidän palkkansa näkyy tulosjoukossa NULLina.

Jos A ja B ovat siis kaksi oliota, vasen ulompi liitos palauttaa tulosjoukon, joka on yhtä suuri kuin ’Tietueet A:ssa EI B:ssä’ vastaavan avaimen perusteella.

Katsotaanpa nyt, mitä oikeanpuoleinen ulompi liitos tekee.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails RIGHT join EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

Ylläolevassa tulosjoukossa, näet, että Right Outer Join on tehnyt juuri päinvastoin kuin Left Join. Se on palauttanut kaikki palkat oikeasta taulukosta eli EmpSalary-taulukosta.

Mutta koska Rose, Sakshi ja Jack eivät löydä vastaavaa työntekijätunnusta vasemmasta taulukosta eli EmpDetails-taulukosta, olemme saaneet heidän työntekijätunnuksensa ja työntekijänimensä NULLina vasemmasta taulukosta.

Jos A ja B ovat siis kaksi oliota, oikea ulompi liitos palauttaa tulosjoukon, joka on yhtä suuri kuin ’Tietueet B:ssä EI A’, joka perustuu vastaavaan avaimeen.

Katsotaan myös, mikä on tulosjoukko, jos teemme select-operaation molempien taulukoiden kaikille sarakkeille.

Query:

SELECT *FROM EmpDetails RIGHT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

Nyt, siirrymme Full Joiniin.

Full outer join tehdään silloin, kun halutaan kaikki tiedot molemmista taulukoista riippumatta siitä, onko vastaavuutta vai ei. Jos siis haluan kaikki työntekijät, vaikka en löytäisikään vastaavaa avainta, suoritan alla esitetyn kyselyn.

Query:

SELECT *FROM EmpDetails FULL JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

Yllä olevasta tulosjoukosta näkee, että koska kuusi ensimmäistä tietuetta täsmäävät molemmissa taulukoissa, olemme saaneet kaikki tiedot ilman NULLia. Seuraavat neljä tietuetta ovat olemassa vasemmassa taulukossa mutta eivät oikeassa taulukossa, joten oikean taulukon vastaavat tiedot ovat NULL.

Kolme viimeistä tietuetta ovat olemassa oikeassa taulukossa mutta eivät vasemmassa taulukossa, joten vasemman taulukon vastaavat tiedot ovat NULL. Jos A ja B ovat siis kaksi kokonaisuutta, Full Outer Join palauttaa tulosjoukon, joka on yhtä suuri kuin ”Tietueet A:ssa JA B:ssä”, riippumatta vastaavasta avaimesta.

Teoreettisesti kyseessä on Left Joinin ja Right Joinin yhdistelmä.

Suorituskyky

Verrataanpa sisäistä liitosta vasemmanpuoleiseen ulkoiseen liitokseen SQL-palvelimessa. Kun puhutaan toiminnan nopeudesta, left outer JOIN ei tietenkään ole nopeampi kuin inner join.

Määritelmän mukaan outer join, olipa se sitten vasen tai oikea, sen on suoritettava kaikki inner joinin työ sekä lisätyö nolla- laajentaa tuloksia. Ulomman liitoksen odotetaan palauttavan suuremman määrän tietueita, mikä lisää sen kokonaissuoritusaikaa entisestään jo pelkästään suuremman tulosjoukon vuoksi.

Siten ulompi liitos on hitaampi kuin sisäinen liitos.

Lisäksi voi olla joitakin erityistilanteita, joissa Left join on nopeampi kuin Inner join, mutta emme voi jatkaa niiden korvaamista keskenään, koska left outer join ei vastaa toiminnallisesti inner joinia.

Keskustellaanpa tapausta, jossa Left join voi olla nopeampi kuin Inner join. Jos liitosoperaatioon osallistuvat taulukot ovat liian pieniä, esimerkiksi niissä on alle 10 tietuetta, eikä taulukoissa ole riittävästi indeksejä kattamaan kyselyä, siinä tapauksessa Left Join on yleensä nopeampi kuin Inner Join.

Luotaan kaksi alla olevaa taulukkoa ja tehdään niiden välille INNER JOIN ja LEFT OUTER JOIN esimerkkinä:

CREATE TABLE #Table1(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table1 (ID, Name) VALUES (1, 'A')INSERT #Table1 (ID, Name) VALUES (2, 'B')INSERT #Table1 (ID, Name) VALUES (3, 'C')INSERT #Table1 (ID, Name) VALUES (4, 'D')INSERT #Table1 (ID, Name) VALUES (5, 'E')CREATE TABLE #Table2(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table2 (ID, Name) VALUES (1, 'A')INSERT #Table2 (ID, Name) VALUES (2, 'B')INSERT #Table2 (ID, Name) VALUES (3, 'C')INSERT #Table2 (ID, Name) VALUES (4, 'D')INSERT #Table2 (ID, Name) VALUES (5, 'E')SELECT *FROM #Table1 t1INNER JOIN #Table2 t2ON t2.Name = t1.Name

SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS barON (55=55);

Kuten yllä näkyy, molemmat kyselyt ovat palauttaneet saman tulosjoukon. Tässä tapauksessa, jos tarkastelet molempien kyselyiden suoritussuunnitelmaa, huomaat, että sisäinen liitos on maksanut enemmän kuin ulompi liitos. Tämä johtuu siitä, että sisäisessä liitoksessa SQL-palvelin tekee hash-ottelun, kun taas vasemmanpuoleisessa liitoksessa se tekee sisäkkäisiä silmukoita.

Hash-ottelu on yleensä nopeampi kuin sisäkkäiset silmukat. Mutta tässä tapauksessa, koska rivien määrä on niin pieni eikä indeksiä voi käyttää (koska teemme liitoksen nimi-sarakkeeseen), hash-operaatio on osoittautunut kalleimmaksi sisäiseksi liitoskyselyksi.

Jos kuitenkin muutat liitoskyselyssä täsmäävän avaimen nimestä ID:ksi ja jos taulukossa on suuri määrä rivejä, huomaat, että sisäinen liitos on nopeampi kuin vasen ulompi liitos.

MS Access Inner ja Outer Join

Kun käytät useita tietolähteitä MS Access -kyselyssä, voit käyttää JOIN:eja hallitaksesi haluamiasi tietueita sen mukaan, miten tietolähteet on yhdistetty toisiinsa.

Sisäisessä liitoksessa vain molempien taulukoiden toisiinsa liittyvät tietueet yhdistetään yhteen tulosjoukkoon. Tämä on Accessin oletusarvoinen liitos, ja se on myös useimmin käytetty liitos. Jos käytät joinia, mutta et nimenomaisesti määritä, minkä tyyppisestä joinista on kyse, Access olettaa, että kyseessä on sisäinen join.

Olkoisissa joinissa yhdistetään oikein kaikki toisiinsa liittyvät tiedot molemmista taulukoista sekä kaikki jäljelle jäävät rivit toisesta taulukosta. Full outer joinissa kaikki tiedot yhdistetään mahdollisuuksien mukaan.

Left Join vs Left Outer Join

SQL-palvelimessa avainsana outer on valinnainen, kun käytetään left outer joinia. Näin ollen ei ole merkitystä sillä, kirjoitetaanko ’LEFT OUTER JOIN’ vai ’LEFT JOIN’, sillä molemmilla saadaan sama tulos.

A LEFT JOIN B on vastaava syntaksi kuin A LEFT OUTER JOIN B.

Alhaalla on luettelo SQL-palvelimen vastaavista syntakseista:

Vasemmanpuoleinen ulkoinen liitos vs. oikeanpuoleinen ulkoinen liitos

Olemme jo nähneet tämän eron tässä artikkelissa. Voit tutustua Left Outer Join- ja Right Outer Join -kyselyihin ja tulosjoukkoon nähdäksesi eron.

Vasemman ja oikean Joinin tärkein ero on ei-yksilöityvien rivien sisällyttämisessä. Left outer join sisältää yhteensopimattomat rivit taulukosta, joka on join-lausekkeen vasemmalla puolella, kun taas Right outer join sisältää yhteensopimattomat rivit taulukosta, joka on join-lausekkeen oikealla puolella.

Vähemmistö kysyy, kumpi on parempi eli Left join vai Right join? Periaatteessa ne ovat samantyyppisiä operaatioita, mutta niiden argumentit ovat päinvastaiset. Kun siis kysytään, kumpaa join-operaatiota käytetään, kysytään itse asiassa, kirjoitetaanko a<b vai b>a. Kyse on vain mieltymyksestä.

Yleensä ihmiset käyttävät SQL-kyselyissään mieluummin Left joinia. Suosittelen, että pysyt johdonmukaisena tavassa, jolla kirjoitat kyselyn, jotta vältyt sekaannuksilta kyselyn tulkinnassa.

Olemme nähneet tähän mennessä kaiken Inner joinista ja kaikentyyppisistä Outer joinista. Tehdäänpä nopeasti yhteenveto Inner Joinin ja Outer Joinin erosta.

Difference between Inner Join and Outer Join in Tabular Format

Sisäisen ja ulomman liitoksen vs. liitoksen (Union)

Väillä kerroilla sekoitamme liitoksen (Join) ja liitoksen (Union) toisiinsa, ja tämä on myös yksi yleisimmin kysytyistä kysymyksistä SQL:n työhaastatteluissa. Olemme jo nähneet eron inner joinin ja outer joinin välillä . Katsotaan nyt, miten JOIN eroaa UNIONista.

UNION sijoittaa kyselyrivin toistensa perään, kun taas join luo kartesiittisen tuotteen ja osittaa sen. Näin ollen UNION ja JOIN ovat täysin erilaisia operaatioita.

Suorittakaamme alla olevat kaksi kyselyä MySQL:ssä ja katsomme niiden tulokset.

UNION-kysely:

SELECT 28 AS bahUNIONSELECT 35 AS bah;

Tulos:

JOIN kysely:

YHTEENVETO-operaatio sijoittaa kahden tai useamman kyselyn tulokset yhteen tulosjoukkoon. Tämä tulosjoukko sisältää kaikki tietueet, jotka on palautettu kaikkien UNION-operaatioon osallistuvien kyselyjen kautta. Näin ollen UNION-operaatio periaatteessa yhdistää kaksi tulosjoukkoa yhteen.

Yhdistämisoperaatio hakee tietoja kahdesta tai useammasta taulusta näiden taulujen välisten loogisten suhteiden eli yhdistämisehdon perusteella. Join-kyselyssä yhden taulukon tietoja käytetään tietueiden valitsemiseen toisesta taulukosta. Sen avulla voidaan yhdistää samankaltaisia tietoja, jotka ovat eri taulukoissa.

Yksinkertaistettuna voidaan sanoa, että UNION yhdistää rivejä kahdesta taulukosta, kun taas join yhdistää sarakkeita kahdesta tai useammasta taulukosta. Molempia käytetään siis n taulukon tietojen yhdistämiseen, mutta ero on siinä, miten tiedot yhdistetään.

Alhaalla on kuvalliset esitykset UNIONista ja JOINista.

Yllä on kuvallinen esitys Join-operaatiosta, joka kuvaa, että jokainen tulosjoukon tietue sisältää sarakkeita molemmista taulukoista eli taulukosta A ja taulukosta B. Tulos palautetaan kyselyssä käytetyn join-ehdon perusteella.

Yhteenliittymä on yleensä denormalisoinnin tulos (normalisoinnin vastakohta), ja se käyttää yhden taulukon vierasta avainta etsiäkseen sarakkeen arvoja käyttämällä toisen taulukon ensisijaista avainta.

Yllä olevassa kuvallisessa esityksessä UNION-operaatiosta näkyy, että jokainen tulosjoukon tietue on rivi jommastakummasta kahdesta taulukosta. UNION-toiminnon tuloksessa on siis yhdistetty taulukon A ja taulukon B rivit.

Lisälukemista =>> MySQL UNION explained with examples

Conclusion

Tässä artikkelissa olemme nähneet tärkeimmät erot Inner Joinin ja Outer Joinin välillä SQL:ssä. Näimme myös Outer Joinin luokittelun eli Left joinin, Right joinin ja Full joinin. Olemme nähneet, miten kukin näistä join-tyypeistä toimii ja miten ne eroavat toisistaan.

Teimme myös suorituskykyvertailun näiden join-tyyppien välillä. Keskustelimme myös siitä, miten join eroaa unionista.

Lue myös =>> MySQL Join Types

Toivottavasti tämä artikkeli on auttanut sinua selvittämään epäilyksiäsi eri join-tyyppien eroista. Olemme varmoja, että tämä todellakin saa sinut päättämään, minkä join-tyypin valitset halutun tulosjoukon perusteella.