MySQL LIITTYMINEN Opetusohjelma: SISÄINEN, ULKOINEN, VASEN, OIKEA, RISTI
Marcus Allen
Mitä JOINS ovat?
Liitokset auttavat hakemaan tietoja kahdesta tai useammasta tietokantataulukosta.
Taulukot liittyvät toisiinsa ensisijaisilla ja vierailla avaimilla.
Huomautus: JOIN on väärinymmärretyin aihe SQL-kehittäjien keskuudessa. Yksinkertaisuuden ja ymmärtämisen helpottamiseksi käytämme uutta tietokantaa näytteen harjoittamiseen. Kuten alla
| id | etunimi | sukunimi | elokuvan_tunnus |
|---|---|---|---|
| 1 | Aatami | Seppä | 1 |
| 2 | Ravi | Kumar | 2 |
| 3 | Susan | Davidson | 5 |
| 4 | Jenny | Adrianna | 8 |
| 5 | Lee | Lemu | 10 |
| id | otsikko | luokka |
|---|---|---|
| 1 | ASSASSIN'S CREED: EMBERS | Animaatiot |
| 2 | Real Steel (2012) | Animaatiot |
| 3 | Alvin ja pikkuoravat | Animaatiot |
| 4 | Tin Tinin seikkailut | Animaatiot |
| 5 | Turvallinen (2012) | Toiminta |
| 6 | Turvallinen talo (2012) | Toiminta |
| 7 | GIA | 18+ |
| 8 | Määräaika 2009 | 18+ |
| 9 | Likainen kuva | 18+ |
| 10 | Marley ja minä | romanssi |
Liitostyypit
Cross JOIN
Cross JOIN on yksinkertaisin JOIN-muoto, joka yhdistää tietokantataulukon jokaisen rivin toisen tietokantataulukon kaikkiin riveihin.
Toisin sanoen se antaa meille yhdistelmät ensimmäisen taulukon jokaisesta rivistä kaikkien toisen taulukon tietueiden kanssa.
Oletetaan, että haluamme saada kaikki jäsentietueet kaikkia elokuvatietueita vastaan, voimme käyttää alla näkyvää käsikirjoitusta saadaksemme halutut tulokset.
SELECT * FROM `movies` CROSS JOIN `members`
Suoritetaan yllä oleva komentosarja MySQL työpöytä antaa meille seuraavat tulokset.
| id | title | id | first_name | last_name | movie_id | |
|---|---|---|---|---|---|---|
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 1 | Adam | Smith | 1 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 2 | Ravi | Kumar | 2 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 3 | Susan | Davidson | 5 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 4 | Jenny | Adrianna | 8 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 6 | Lee | Pong | 10 |
| 2 | Real Steel(2012) | Animations | 1 | Adam | Smith | 1 |
| 2 | Real Steel(2012) | Animations | 2 | Ravi | Kumar | 2 |
| 2 | Real Steel(2012) | Animations | 3 | Susan | Davidson | 5 |
| 2 | Real Steel(2012) | Animations | 4 | Jenny | Adrianna | 8 |
| 2 | Real Steel(2012) | Animations | 6 | Lee | Pong | 10 |
| 3 | Alvin and the Chipmunks | Animations | 1 | Adam | Smith | 1 |
| 3 | Alvin and the Chipmunks | Animations | 2 | Ravi | Kumar | 2 |
| 3 | Alvin and the Chipmunks | Animations | 3 | Susan | Davidson | 5 |
| 3 | Alvin and the Chipmunks | Animations | 4 | Jenny | Adrianna | 8 |
| 3 | Alvin and the Chipmunks | Animations | 6 | Lee | Pong | 10 |
| 4 | The Adventures of Tin Tin | Animations | 1 | Adam | Smith | 1 |
| 4 | The Adventures of Tin Tin | Animations | 2 | Ravi | Kumar | 2 |
| 4 | The Adventures of Tin Tin | Animations | 3 | Susan | Davidson | 5 |
| 4 | The Adventures of Tin Tin | Animations | 4 | Jenny | Adrianna | 8 |
| 4 | The Adventures of Tin Tin | Animations | 6 | Lee | Pong | 10 |
| 5 | Safe (2012) | Action | 1 | Adam | Smith | 1 |
| 5 | Safe (2012) | Action | 2 | Ravi | Kumar | 2 |
| 5 | Safe (2012) | Action | 3 | Susan | Davidson | 5 |
| 5 | Safe (2012) | Action | 4 | Jenny | Adrianna | 8 |
| 5 | Safe (2012) | Action | 6 | Lee | Pong | 10 |
| 6 | Safe House(2012) | Action | 1 | Adam | Smith | 1 |
| 6 | Safe House(2012) | Action | 2 | Ravi | Kumar | 2 |
| 6 | Safe House(2012) | Action | 3 | Susan | Davidson | 5 |
| 6 | Safe House(2012) | Action | 4 | Jenny | Adrianna | 8 |
| 6 | Safe House(2012) | Action | 6 | Lee | Pong | 10 |
| 7 | GIA | 18+ | 1 | Adam | Smith | 1 |
| 7 | GIA | 18+ | 2 | Ravi | Kumar | 2 |
| 7 | GIA | 18+ | 3 | Susan | Davidson | 5 |
| 7 | GIA | 18+ | 4 | Jenny | Adrianna | 8 |
| 7 | GIA | 18+ | 6 | Lee | Pong | 10 |
| 8 | Deadline(2009) | 18+ | 1 | Adam | Smith | 1 |
| 8 | Deadline(2009) | 18+ | 2 | Ravi | Kumar | 2 |
| 8 | Deadline(2009) | 18+ | 3 | Susan | Davidson | 5 |
| 8 | Deadline(2009) | 18+ | 4 | Jenny | Adrianna | 8 |
| 8 | Deadline(2009) | 18+ | 6 | Lee | Pong | 10 |
| 9 | The Dirty Picture | 18+ | 1 | Adam | Smith | 1 |
| 9 | The Dirty Picture | 18+ | 2 | Ravi | Kumar | 2 |
| 9 | The Dirty Picture | 18+ | 3 | Susan | Davidson | 5 |
| 9 | The Dirty Picture | 18+ | 4 | Jenny | Adrianna | 8 |
| 9 | The Dirty Picture | 18+ | 6 | Lee | Pong | 10 |
| 10 | Marley and me | Romance | 1 | Adam | Smith | 1 |
| 10 | Marley and me | Romance | 2 | Ravi | Kumar | 2 |
| 10 | Marley and me | Romance | 3 | Susan | Davidson | 5 |
| 10 | Marley and me | Romance | 4 | Jenny | Adrianna | 8 |
| 10 | Marley and me | Romance | 6 | Lee | Pong | 10 |
INNER JOIN
Sisäistä JOIN-komentoa käytetään palauttamaan rivit molemmista taulukoista, jotka täyttävät annetun ehdon.
Oletetaan, että haluat saada luettelon jäsenistä, jotka ovat vuokranneet elokuvia, sekä heidän vuokraamiensa elokuvien nimet. Voit käyttää tähän yksinkertaisesti INNER JOIN -toimintoa, joka palauttaa molemmista taulukoista rivit, jotka täyttävät tietyt ehdot.
SELECT members.`first_name` , members.`last_name` , movies.`title` FROM members ,movies WHERE movies.`id` = members.`movie_id`
Suorittamalla yllä oleva komentosarja anna
| first_name | last_name | title |
|---|---|---|
| Adam | Smith | ASSASSIN'S CREED: EMBERS |
| Ravi | Kumar | Real Steel(2012) |
| Susan | Davidson | Safe (2012) |
| Jenny | Adrianna | Deadline(2009) |
| Lee | Pong | Marley and me |
Huomaa, että yllä oleva tulosskripti voidaan kirjoittaa myös seuraavasti saavuttaaksesi samat tulokset.
SELECT A.`first_name` , A.`last_name` , B.`title` FROM `members`AS A INNER JOIN `movies` AS B ON B.`id` = A.`movie_id`
Ulkoiset liitokset
MySQL Ulommat JOINit palauttavat kaikki vastaavat tietueet molemmista taulukoista.
Se voi havaita tietueita, joilla ei ole ottelua yhdistetyssä taulukossa. Se palaa NULL yhdistetyn taulukon tietueiden arvot, jos vastaavuutta ei löydy.
Kuulostaa hämmentävältä? Katsotaanpa esimerkkiä -
LEFT LIITY
Oletetaan nyt, että haluat saada kaikkien elokuvien nimet sekä niiden jäsenten nimet, jotka ovat vuokranneet ne. On selvää, ettei kukaan ole vuokrannut joitakin elokuvia. Voimme yksinkertaisesti käyttää LEFT LIITY tarkoitusta varten.
LEFT JOIN palauttaa kaikki rivit vasemmalla olevasta taulukosta, vaikka oikealla olevasta taulukosta ei löytyisikään vastaavia rivejä. Jos oikealla olevasta taulukosta ei löydy osumia, palautetaan NULL.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B ON B.`movie_id` = A.`id`
Suoritetaan yllä oleva komentosarja MySQL työpenkki antaa. Voit nähdä, että palautetussa tuloksessa, joka on lueteltu alla, että elokuvien, joita ei ole vuokrattu, jäsennimikentillä on NULL-arvoja. Tämä tarkoittaa, että yksikään vastaava jäsen ei löytänyt jäsentaulukkoa kyseiselle elokuvalle.
| title | first_name | last_name |
|---|---|---|
| ASSASSIN'S CREED: EMBERS | Adam | Smith |
| Real Steel(2012) | Ravi | Kumar |
| Safe (2012) | Susan | Davidson |
| Deadline(2009) | Jenny | Adrianna |
| Marley and me | Lee | Pong |
| Alvin and the Chipmunks | NULL | NULL |
| The Adventures of Tin Tin | NULL | NULL |
| Safe House(2012) | NULL | NULL |
| GIA | NULL | NULL |
| The Dirty Picture | NULL | NULL |
OIKEA LIITTYMINEN
RIGHT JOIN on selvästi vastoin LEFT JOIN. RIGHT JOIN palauttaa kaikki sarakkeet oikealla olevasta taulukosta, vaikka vastaavia rivejä ei löytyisikään vasemmanpuoleisesta taulukosta. Jos vasenta taulukkoa ei löydy, palautetaan NULL.
Oletetaan esimerkissämme, että sinun on hankittava jäsenten ja heidän vuokraamiensa elokuvien nimet. Nyt meillä on uusi jäsen, joka ei ole vielä vuokrannut yhtään elokuvaa
SELECT A.`first_name` , A.`last_name`, B.`title` FROM `members` AS A RIGHT JOIN `movies` AS B ON B.`id` = A.`movie_id`
Suoritetaan yllä oleva komentosarja MySQL työpöytä antaa seuraavat tulokset.
| first_name | last_name | title |
|---|---|---|
| Adam | Smith | ASSASSIN'S CREED: EMBERS |
| Ravi | Kumar | Real Steel(2012) |
| Susan | Davidson | Safe (2012) |
| Jenny | Adrianna | Deadline(2009) |
| Lee | Pong | Marley and me |
| NULL | NULL | Alvin and the Chipmunks |
| NULL | NULL | The Adventures of Tin Tin |
| NULL | NULL | Safe House(2012) |
| NULL | NULL | GIA |
| NULL | NULL | The Dirty Picture |
"ON" ja "USING" lausekkeet
Yllä olevissa JOIN-kyselyesimerkeissä olemme käyttäneet ON-lausetta sovittamaan tietueita taulukoiden välillä.
USING-lausetta voidaan käyttää myös samaan tarkoitukseen. Ero kanssa KÄYTTÖ on se molemmissa taulukoissa on oltava samat nimet vastaaville sarakkeille.
Toistaiseksi "elokuvat" -taulukossa käytimme sen ensisijaista avainta nimellä "id". Viittasimme samaan "jäsenet" -taulukossa nimellä "movie_id".
Nimetään "elokuvat"-taulukot uudelleen "id"-kenttään, jotta niillä on nimi "movie_id". Teemme tämän saadaksemme identtiset kenttien nimet.
ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
Seuraavaksi käytetään KÄYTTÖ-toimintoa yllä olevan LEFT JOIN -esimerkin kanssa.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B USING ( `movie_id` )
Sen lisäksi, että käytät ON ja KÄYTTÖ JOIN:ien kanssa voit käyttää monia muita MySQL lausekkeet kuten Ryhmittele, MISSÄ ja jopa toimii mm SUMMA, AVG, Jne
Miksi meidän pitäisi käyttää liitoksia?
Nyt saatat miettiä, miksi käytämme JOINeja, kun voimme tehdä saman tehtävän suorittamalla kyselyitä. Varsinkin jos sinulla on kokemusta tietokantaohjelmoinnista, tiedät, että voimme suorittaa kyselyitä yksitellen, käytä kunkin tulostetta peräkkäisissä kyselyissä. Tietysti se on mahdollista. Mutta käyttämällä JOIN-koodeja, voit tehdä työn käyttämällä vain yhtä kyselyä millä tahansa hakuparametreilla. Toisaalta MySQL voi saavuttaa parempaa suorituskykyä JOIN:illa, koska se voi käyttää indeksointia. Yksinkertaisesti yhden JOIN-kyselyn käyttö useiden kyselyjen suorittamisen sijaan vähentää palvelimen ylimääräisiä kustannuksia. Useiden kyselyjen käyttäminen sen sijaan johtaa enemmän tiedonsiirtoon välillä MySQL ja sovellukset (ohjelmistot). Lisäksi se vaatii enemmän tietojen käsittelyä myös sovelluksen lopussa.
On selvää, että voimme saavuttaa paremmin MySQL ja sovellusten suorituskyky JOIN:ien avulla.
Yhteenveto
- JOINS mahdollistaa tietojen yhdistämisen useammasta kuin yhdestä taulukosta yhdeksi tulosjoukoksi.
- JOINSilla on parempi suorituskyky verrattuna alikyselyihin
- INNER JOINS palauttaa vain rivit, jotka täyttävät annetut kriteerit.
- OUTER JOINS voi myös palauttaa rivejä, joilta ei löydy osumia. Vastaamattomat rivit palautetaan avainsanalla NULL.
- Tärkeimpiä JOIN-tyyppejä ovat Inner, Left Outer, Right Outer, Cross JOINS jne.
- Usein käytetty lause JOIN-operaatioissa on "ON". KÄYTTÖ-lause edellyttää, että vastaavilla sarakkeilla on sama nimi.
- JOINSia voidaan käyttää myös muissa lausekkeissa, kuten GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS jne.
