SQLite Liitos: Luonnollinen vasen ulompi, sisempi, ristiin pöytien kanssa

SQLite tukee erilaisia SQL Liittyy, kuten SISÄLIITTYMINEN, LEFT OUTTER JOIN ja CROSS JOIN. Jokaista JOIN-tyyppiä käytetään eri tilanteissa, kuten tässä opetusohjelmassa nähdään.

Johdatus SQLite JOIN-lauseke

Kun työskentelet tietokannassa, jossa on useita taulukoita, sinun on usein hankittava tietoja näistä useista taulukoista.

JOIN-lausekkeen avulla voit linkittää kaksi tai useampia taulukkoja tai alikyselyjä yhdistämällä ne. Voit myös määrittää, minkä sarakkeen mukaan taulukot on linkitettävä ja millä ehdoilla.

Jokaisella JOIN-lauseella on oltava seuraava syntaksi:

Jokainen liittymislauseke sisältää:

• Taulukko tai alikysely, joka on vasen taulukko; taulukko tai alikysely ennen join-lausetta (sen vasemmalla puolella).
• JOIN-operaattori – määritä liitostyyppi (joko INNER JOIN, LEFT OUTTER JOIN tai CROSS JOIN).
• JOIN-rajoitus – kun olet määrittänyt yhdistettävät taulukot tai alikyselyt, sinun on määritettävä liitosrajoitus, joka on ehto, jossa tätä ehtoa vastaavat vastaavat rivit valitaan liitostyypin mukaan.

Huomaa, että kaikki seuraavat SQLite JOIN-taulukoiden esimerkkejä, sinun on suoritettava sqlite3.exe ja avattava yhteys mallitietokantaan virtaavana:

Vaihe 1) Tässä vaiheessa avaa Oma tietokone ja siirry seuraavaan hakemistoon ”C:\sqlite” ja avaa sitten ”sqlite3.exe”:

Vaihe 2) Avaa tietokanta “TutorialsSampleDB.db” seuraavalla komennolla:

Nyt olet valmis suorittamaan minkä tahansa tyyppisiä kyselyjä tietokannassa.

SQLite INNER JOIN

INNER JOIN palauttaa vain ne rivit, jotka vastaavat liitosehtoa, ja poistaa kaikki muut rivit, jotka eivät vastaa liitosehtoa.

esimerkki

Seuraavassa esimerkissä yhdistämme kaksi taulukkoa ”Opiskelijat” ja ”Osasto” DepartmentId-arvolla saadaksemme kunkin opiskelijan osaston nimen seuraavasti:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Koodin selitys

INNER JOIN toimii seuraavasti:

• Valitse-lauseessa voit valita mitkä tahansa sarakkeet, jotka haluat valita kahdesta viitatusta taulukosta.
• INNER JOIN -lause kirjoitetaan ensimmäisen taulukon jälkeen, johon viitataan "From"-lauseella.
• Sitten liitosehto määritetään PÄÄLLÄ.
• Viitetyille taulukoille voidaan määrittää aliakset.
• SISÄINEN sana on valinnainen, voit kirjoittaa JOIN.

ulostulo

INNER JOIN tuottaa sekä opiskelija- että laitoksen taulukoista tietueet, jotka vastaavat ehtoa ”Students.DepartmentId = Departments.DepartmentId”. Täsmäämättömät rivit ohitetaan eikä niitä sisällytetä tulokseen.

Tästä syystä kyselystä saatiin vain 8 opiskelijaa 10 opiskelijasta, joiden osastot olivat IT, matematiikka ja fysiikka. Opiskelijoita ”Jena” ja ”George” ei kuitenkaan otettu mukaan, koska heidän osastotunnuksensa on tyhjä (null), mikä ei vastaa osastotaulukon osastotunnus-saraketta. Seuraavassa kuvassa:

SQLite LIITY… KÄYTTÖ

INNER JOIN voidaan kirjoittaa käyttämällä "KÄYTTÖ"-lausetta redundanssin välttämiseksi, joten sen sijaan, että kirjoitat "ON Students.DepartmentId = Departments.DepartmentId", voit kirjoittaa vain "USING(Osastotunnus)".

Voit käyttää "JOIN .. KÄYTTÖ" aina, kun liitosehdon sarakkeet, joita vertaat, ovat samannimiä. Tällaisissa tapauksissa niitä ei tarvitse toistaa käyttämällä on-ehtoa, vaan ilmoita vain sarakkeiden nimet ja SQLite huomaa sen.

Ero INNER JOINin ja JOINin välillä.. KÄYTTÖ:

”JOIN … USING” -komennolla et kirjoita liitosehtoa, vaan kirjoitat vain liitossarakkeen, joka on yhteinen molemmille liitetyille taulukoille. Sen sijaan, että kirjoittaisimme table1:een ”INNER JOIN table2 ON table1.cola = table2.cola”, kirjoitamme sen muodossa ”table1 JOIN table2 USING(cola)”.

esimerkki

Seuraavassa esimerkissä yhdistämme kaksi taulukkoa ”Opiskelijat” ja ”Osasto” DepartmentId-arvolla saadaksemme kunkin opiskelijan osaston nimen seuraavasti:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

Selitys

• Toisin kuin edellisessä esimerkissä, emme kirjoittaneet ”ON Students.DepartmentId = Departments.DepartmentId”. Kirjoitimme vain ”USING(DepartmentId)”.
• SQLite päättelee liitosehdon automaattisesti ja vertaa osastotunnusta molemmista taulukoista – opiskelijat ja laitokset.
• Voit käyttää tätä syntaksia aina, kun kahdella vertaamallasi sarakkeella on sama nimi.

ulostulo

Tämä antaa sinulle täsmälleen saman tuloksen kuin edellinen esimerkki:

SQLite LUONNOLLINEN LIITTYMINEN

NATURAL JOIN on samanlainen kuin JOIN…USING, ero on siinä, että se testaa automaattisesti molemmissa taulukoissa olevien sarakkeiden arvojen tasa-arvon.

Ero SISÄLIITTYMISEN ja LUONNON LIITTYMISEN välillä:

• INNER JOIN -funktiossa sinun on määritettävä liitosehto, jota sisempi liitos käyttää kahden taulukon liittämiseen. Luonnollisessa liitoksessa et kirjoita liitosehtoa. Kirjoitat vain kahden taulukon nimet ilman ehtoja. Tämän jälkeen luonnollinen liitos tarkistaa automaattisesti, ovatko molempien taulukoiden kaikkien sarakkeiden arvot yhtäpitäviä. Luonnollinen liitos päättelee liitosehdon automaattisesti.
• NATURAL JOIN -kohdassa kaikki sarakkeet molemmista samannimisistä taulukoista yhdistetään toisiinsa. Jos meillä on esimerkiksi kaksi taulukkoa, joissa on kaksi yhteistä sarakkeen nimeä (kaksi saraketta on olemassa kahdessa taulukossa samalla nimellä), luonnollinen liitos yhdistää kaksi taulukkoa vertaamalla molempien sarakkeiden arvoja eikä vain yhden sarakkeen arvoja. sarakkeessa.

esimerkki

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

Selitys

• Meidän ei tarvitse kirjoittaa sarakenimiä sisältävää liitosehtoa (kuten teimme INNER JOIN -funktiossa). Meidän ei edes tarvinnut kirjoittaa sarakenimeä kerran (kuten teimme JOIN USING -funktiossa).
• Luonnollinen liitos skannaa molemmat sarakkeet kahdesta taulukosta. Se havaitsee, että ehto tulisi koostua vertailemalla osastotunnusta molemmista taulukoista Opiskelijat ja Osastot.

ulostulo

NATURAL JOIN antaa täsmälleen saman tulosteen kuin INNER JOIN- ja JOIN USING -esimerkeistä saamamme tuloste, koska esimerkissämme kaikki kolme kyselyä ovat ekvivalentteja. Joissakin tapauksissa tuloste on kuitenkin erilainen kuin sisäisessä liitoksessa ja luonnollisessa liitoksessa. Esimerkiksi jos on useita samannimisiä taulukoita, luonnollinen liitos vertaa kaikkia sarakkeita toisiinsa. Sisäinen liitos kuitenkin vertaa vain liitosehdon sarakkeita.

SQLite LEFT OUTTER JOIN

SQL-standardi määrittelee kolme OUTER JOIN -tyyppiä: LEFT, RIGHT ja FULL, mutta SQLite tukee vain luonnollista LEFT OUTTER JOIN -toimintoa.

LEFT OUTER JOIN -kyselyssä kaikki vasemmasta taulukosta valitsemiesi sarakkeiden arvot sisällytetään kyselyn tulokseen, joten riippumatta siitä, vastaako arvo liitosehtoa vai ei, se sisällytetään tulokseen.

Jos vasemmanpuoleisessa taulukossa on 'n' riviä, kyselyn tuloksissa on 'n' riviä. Oikeanpuoleisen taulukon sarakkeiden arvoissa, jotka eivät kuitenkaan vastaa liitosehtoa, on arvon "null".

Joten saat rivien määrän, joka vastaa vasemman liitoksen rivien määrää. Jotta saat vastaavat rivit molemmista taulukoista (kuten INNER JOIN -tulokset) sekä ristiriitaiset rivit vasemmasta taulukosta.

esimerkki

Seuraavassa esimerkissä yritämme "LEFT JOIN" yhdistää kaksi taulukkoa "Opiskelijat" ja "Osastot":

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Selitys

• SQLite LEFT JOIN -syntaksi on sama kuin INNER JOIN; kirjoitat LEFT JOIN kahden taulukon väliin, ja sitten liitosehto tulee ON-lauseen jälkeen.
• Ensimmäinen taulukko from-lauseen jälkeen on vasen taulukko. Toinen luonnollisen LEFT JOINin jälkeen määritetty taulukko on oikea taulukko.
• OUTER-lause on valinnainen; LEFT natural OUTER JOIN on sama kuin LEFT JOIN.

ulostulo

Kuten näet, kaikki opiskelijat-taulukon rivit, yhteensä 10 opiskelijaa, ovat mukana. Vaikka neljännellä ja viimeisellä opiskelijalla, Jenalla ja Georgella, olisi osastotunnukset, joita ei ole Osastot-taulukossa, heidätkin otetaan mukaan.

Ja näissä tapauksissa sekä Jenan että Georgen departmentName-arvo on ”null”, koska departments-taulukossa ei ole departmentName-arvoa, joka vastaisi heidän departmentId-arvoaan.

Selitetäänpä edellistä vasemman liitoksen käyttöä käyttävää kyselyä tarkemmin Venn-diagrammeja käyttäen:

VASEN LIITOS antaa kaikkien opiskelijoiden nimet opiskelijat-taulukosta, vaikka opiskelijalla olisi osastotunnus, jota ei ole osastot-taulukossa. Joten kysely ei anna vain vastaavia rivejä SISÄISEN LIITOS -kyselynä, vaan antaa sinulle ylimääräisen osan, joka sisältää vasemmanpuoleisen taulukon eli opiskelijat-taulukon vastaavat rivit.

Huomaa, että jokaisella opiskelijan nimellä, jolla ei ole vastaavaa osastoa, osaston nimelle on "null"-arvo, koska sille ei ole vastaavaa arvoa, ja nämä arvot ovat arvoja vastaamattomilla riveillä.

SQLite Ristiliittymä

CROSS JOIN antaa karteesisen tuotteen valituille sarakkeille kahdesta yhdistetystä taulukosta sovittamalla kaikki ensimmäisen taulukon arvot kaikkiin toisen taulukon arvoihin.

Joten jokaiselle ensimmäisen taulukon arvolle saat 'n' vastaavuuksia toisesta taulukosta, jossa n on taulukon toisten rivien lukumäärä.

Toisin kuin INNER JOIN- ja LEFT OUTER JOIN -operaatioissa, CROSS JOIN -operaatiossa ei tarvitse määrittää liitosehtoa, koska SQLite ei tarvitse sitä CROSS JOIN -toimintoa varten.

SQLite tuottaa loogisen tulosjoukon yhdistämällä kaikki ensimmäisen taulukon arvot kaikkiin toisen taulukon arvoihin.

Jos esimerkiksi valitsit sarakkeen ensimmäisestä taulukosta (colA) ja toisen sarakkeen toisesta taulukosta (colB). ColA sisältää kaksi arvoa (1,2) ja colB sisältää myös kaksi arvoa (3,4).

Sitten CROSS JOINin tulos on neljä riviä:

• Kaksi riviä yhdistämällä sarakkeen A ensimmäinen arvo, joka on 1, sarakkeen B (3,4) kahteen arvoon, jotka ovat (1,3), (1,4).
• Samoin kaksi riviä yhdistämällä colA:n toinen arvo, joka on 2, sarakkeen B (3,4) kahteen arvoon, jotka ovat (2,3), (2,4).

esimerkki

Seuraavassa kyselyssä kokeilemme CROSS JOIN -toimintoa Opiskelijat- ja Osastot-taulukoiden välillä:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

Selitys

• In SQLite Valitse useista taulukoista, valitsimme vain kaksi saraketta "studentsname" opiskelijataulukosta ja "departmentName" osastotaulukosta.
• Ristiliitokselle emme määrittäneet mitään liitosehtoa, vain kaksi taulukkoa yhdistettynä ja niiden välissä CROSS JOIN -lauseke.

ulostulo

Kuten näet, tulos on 40 riviä; Opiskelijoiden taulukon 10 arvoa vastaavat osastotaulukon 4 osastoa. seuraavasti:

• Neljä arvoa neljälle osastolle osastotaulukosta täsmäsi ensimmäisen opiskelijan Michelin kanssa.
• Osastot-taulukon neljälle osastolle neljä arvoa täsmäsi toisen opiskelijan, Johnin, arvoon.
• Osastot-taulukon neljälle osastolle neljä arvoa täsmäsi kolmannen opiskelijan, Jackin, arvoon… ja niin edelleen.

UKK