MySQL CSATLAKOZÁS Tutorial: BELSŐ, KÜLSŐ, BAL, JOBB, KERESZT
Marcus Allen
Mik azok a JOINS?
A csatlakozások segítenek az adatok lekérésében két vagy több adatbázistáblából.
A táblák elsődleges és idegen kulcsok használatával kapcsolódnak egymáshoz.
Megjegyzés: A JOIN a leginkább félreértett téma az SQL karcsúsítói között. Az egyszerűség és a könnyebb érthetőség kedvéért egy új adatbázist fogunk használni a minta gyakorlására. Az alábbiak szerint
| id | keresztnév | vezetéknév | film_id |
|---|---|---|---|
| 1 | Ádám | Kovács | 1 |
| 2 | Ravi | Kumar | 2 |
| 3 | Susan | Davidson | 5 |
| 4 | Állast nősténye | Adrianna | 8 |
| 5 | Lee | Pong | 10 |
| id | cím | kategória |
|---|---|---|
| 1 | ASSASSIN'S CRED: EMBERS | Animációk |
| 2 | Real Steel (2012) | Animációk |
| 3 | Alvin és a mókusok | Animációk |
| 4 | Tin Tin kalandjai | Animációk |
| 5 | Biztonságos (2012) | Akció |
| 6 | Biztonságos ház (2012) | Akció |
| 7 | ÉN VAGYOK | 18 + |
| 8 | 2009. határidő | 18 + |
| 9 | A piszkos kép | 18 + |
| 10 | Marley és én | Románc |
Csatlakozások típusai
Cross JOIN
A Cross JOIN a JOIN-ok legegyszerűbb formája, amely az egyik adatbázistábla minden sorát egy másik adatbázistáblázat összes sorával egyezteti.
Más szavakkal, megadja nekünk az első tábla minden sorának kombinációját a második tábla összes rekordjával.
Tegyük fel, hogy az összes tagrekordot össze akarjuk állítani az összes filmrekorddal, használhatjuk az alábbi forgatókönyvet a kívánt eredmény eléréséhez.
SELECT * FROM `movies` CROSS JOIN `members`
A fenti szkript végrehajtása MySQL munkapad a következő eredményeket ad nekünk.
| 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
A belső JOIN arra szolgál, hogy mindkét táblából olyan sorokat adjon vissza, amelyek megfelelnek az adott feltételnek.
Tegyük fel, hogy szeretné megkapni azon tagok listáját, akik kölcsönzött filmeket, valamint az általuk kölcsönzött filmek címeit. Ehhez egyszerűen használhat egy INNER JOIN-t, amely mindkét táblából olyan sorokat ad vissza, amelyek megfelelnek az adott feltételeknek.
SELECT members.`first_name` , members.`last_name` , movies.`title` FROM members ,movies WHERE movies.`id` = members.`movie_id`
A fenti script végrehajtása adja meg
| 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 |
Vegye figyelembe, hogy a fenti eredmények szkriptje az alábbiak szerint is megírható ugyanazon eredmények elérése érdekében.
SELECT A.`first_name` , A.`last_name` , B.`title` FROM `members`AS A INNER JOIN `movies` AS B ON B.`id` = A.`movie_id`
Külső JOIN-ok
MySQL A külső JOIN-ok mindkét táblából minden egyező rekordot visszaadnak.
Képes felismerni azokat a rekordokat, amelyeknek nincs egyezése az egyesített táblában. Visszatér NULL az egyesített tábla rekordjainak értékei, ha nem található egyezés.
Zavarba ejtően hangzik? Nézzünk egy példát –
BAL EGYSZER
Tételezzük fel, hogy most az összes film címét szeretné megkapni azon tagok nevével együtt, akik kikölcsönözték őket. Nyilvánvaló, hogy egyes filmeket senki sem kölcsönzött. Egyszerűen használhatjuk BAL EGYSZER erre a célra.
A LEFT JOIN a bal oldali táblázat összes sorát visszaadja, még akkor is, ha a jobb oldali táblázatban nem található egyező sor. Ahol nem található egyezés a jobb oldali táblázatban, NULL-t ad vissza.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B ON B.`movie_id` = A.`id`
A fenti szkript végrehajtása MySQL A workbench adja. Látható, hogy a visszaadott eredményben, amely az alábbiakban található, a nem kölcsönzött filmeknél a tagnévmezők NULL értéket tartalmaznak. Ez azt jelenti, hogy egyetlen megfelelő tag sem talált tagokat az adott filmhez.
| 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 |
JOBB CSATLAKOZÁS
A RIGHT JOIN nyilvánvalóan a LEFT JOIN ellentéte. A RIGHT JOIN a jobb oldali táblázat összes oszlopát visszaadja, még akkor is, ha a bal oldali táblázatban nem található egyező sor. Ahol nem található egyezés a bal oldali táblázatban, NULL-t ad vissza.
Példánkban tegyük fel, hogy meg kell szereznie az általuk kölcsönzött tagok és filmek nevét. Most van egy új tagunk, aki még nem kölcsönzött filmet
SELECT A.`first_name` , A.`last_name`, B.`title` FROM `members` AS A RIGHT JOIN `movies` AS B ON B.`id` = A.`movie_id`
A fenti szkript végrehajtása MySQL munkapad a következő eredményeket adja.
| 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” és „USING” záradék
A fenti JOIN lekérdezési példákban az ON záradékot használtuk a táblák közötti rekordok egyeztetésére.
A USING záradék ugyanerre a célra is használható. A különbség a HASZNÁLATA ez azonos nevének kell lennie az egyező oszlopoknak mindkét táblában.
A „filmek” táblázatban eddig az elsődleges kulcsát használtuk „id” néven. Ugyanerre hivatkoztunk a „members” táblázatban „movie_id” néven.
Nevezzük át a „movies” táblákat „id” mezőre, hogy a „movie_id” név legyen. Ezt annak érdekében tesszük, hogy a mezőnevek azonosak legyenek.
ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
Ezután használjuk a USING parancsot a fenti LEFT JOIN példával.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B USING ( `movie_id` )
A használat mellett ON és a HASZNÁLAT JOIN-okkal sok mást is használhatsz MySQL záradékok, mint CSOPORTOSÍTÁS, HOL és még olyan funkciókat is ÖSSZEG, AVGStb
Miért használjunk csatlakozásokat?
Most elgondolkodhat azon, hogy miért használunk JOIN-okat, ha ugyanazt a feladatot lekérdezések futtatásával is elvégezhetjük. Főleg, ha van némi tapasztalata az adatbázis-programozásban, tudja, hogy egyenként is le tudjuk futtatni a lekérdezéseket, használja mindegyik kimenetét az egymást követő lekérdezésekben. Természetesen ez lehetséges. De a JOIN használatával elvégezheti a munkát, ha csak egyetlen lekérdezést használ bármilyen keresési paraméterrel. Másrészről MySQL jobb teljesítményt érhet el JOIN-okkal, mivel használhatja az indexelést. Egyszerűen egyetlen JOIN lekérdezés használata több lekérdezés futtatása helyett csökkenti a szerver többletterhelését. Ehelyett több lekérdezés használata több adatátvitelt eredményez közöttük MySQL és alkalmazások (szoftver). Ezenkívül több adatkezelést igényel az alkalmazás végén is.
Egyértelmű, hogy jobb eredményeket érhetünk el MySQL és az alkalmazások teljesítménye a JOIN használatával.
Összegzésként
- A JOINS lehetővé teszi, hogy egynél több tábla adatait egyetlen eredményhalmazba egyesítsük.
- A JOINS jobb teljesítményt nyújt az allekérdezésekhez képest
- Az INNER JOINS csak azokat a sorokat adja vissza, amelyek megfelelnek a megadott feltételeknek.
- Az OUTER JOINS olyan sorokat is visszaadhat, ahol nem található egyezés. Az egyező sorokat a NULL kulcsszó adja vissza.
- A fő JOIN típusok közé tartozik a belső, a bal külső, a jobb külső, a keresztcsatlakozás stb.
- A JOIN műveletekben gyakran használt záradék az „ON”. A „USING” záradék megköveteli, hogy a megfelelő oszlopoknak azonos nevűeknek kell lenniük.
- A JOINS más záradékokban is használható, például GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS stb.
