MySQL Sammanfoga: Inre, Yttre, Vänster, Höger, Kors

Vad är JOINS?

Joins hjälper till att hämta data från två eller flera databastabeller.

Tabellerna är ömsesidigt relaterade med hjälp av primära och främmande nycklar.

Obs: JOIN är det mest missförstådda ämnet bland SQL-utbildade. För enkelhetens skull och för att underlätta förståelsen kommer vi att använda en ny databas för att öva på exempel. Som visas nedan.

Varje exempel nedan använder dessa två tabeller. movie_id kolumn i medlemmar pekar på id kolumn i filmer — relationen som varje JOIN matchar.

medlemmar

filmer

Varför ska vi använda JOINS?

Innan vi tittar på varje JOIN-typ är det värt att veta varför en JOIN är att föredra framför att köra flera frågor.

Nu kanske du tänker, varför vi använder JOINs när vi kan göra samma uppgift som kör frågor. Speciellt om du har lite erfarenhet av databasprogrammering vet du att vi kan köra frågor en efter en, använd utdata från varje i på varandra följande frågor. Naturligtvis är det möjligt. Men med JOINs kan du få jobbet gjort genom att bara använda en fråga med valfri sökparametrar. Å andra sidan MySQL kan uppnå bättre prestanda med JOINs eftersom den kan använda indexering. Att helt enkelt använda en enda JOIN-fråga istället för att köra flera frågor minskar serverns overhead. Använder istället flera frågor som leder till fler dataöverföringar mellan MySQL och applikationer (mjukvara). Vidare kräver det mer datamanipulationer i applikationsänden också.

Det är klart att vi kan uppnå bättre MySQL och applikationsprestanda genom användning av JOINs.

Typer av JOINS

MySQL stöder flera JOIN-typer, där var och en besvarar en annan fråga om samma två tabeller. Tabellen nedan jämför dem; varje typ demonstreras sedan med en fråga och dess utdata.

KRÄSS GÅ MED

Cross JOIN är en enklaste form av JOINs som matchar varje rad från en databastabell till alla rader i en annan.

Med andra ord ger det oss kombinationer av varje rad i den första tabellen med alla poster i den andra tabellen.

Anta att vi vill få alla medlemsrekord mot alla filmrekord, vi kan använda skriptet som visas nedan för att få våra önskade resultat.

SELECT * FROM `movies` CROSS JOIN `members`

Exekvera skriptet ovan i MySQL arbetsbänk ger oss följande resultat.

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

En CROSS JOIN returnerar alla möjliga parningar, vilket sällan är vad man vill ha. En INNER JOIN begränsar resultatet till de par som faktiskt är relaterade.

Den inre JOIN används för att returnera rader från båda tabellerna som uppfyller det givna villkoret.

Anta att du vill få en lista över medlemmar som har hyrt filmer tillsammans med titlar på filmer som de hyrt. Du kan helt enkelt använda en INNER JOIN för det, som returnerar rader från båda tabellerna som uppfyller givna villkor.

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

Exekvera ovanstående skript ge

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

Observera att resultatskriptet ovan också kan skrivas enligt följande för att uppnå samma resultat.

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members` AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Yttre JOINs

En INNER JOIN tar tyst bort rader som inte har någon partner. När dessa omatchade rader är viktiga är en OUTER JOIN rätt val.

MySQL Outer JOINs returnerar alla matchande poster från båda tabellerna.

Det kan upptäcka poster som inte har någon match i sammanfogad tabell. Den återkommer NULL värden för poster i sammanfogad tabell om ingen matchning hittas.

Låter det förvirrande? Låt oss titta på ett exempel –

VÄNSTER GÅ

Anta att du nu vill få titlar på alla filmer tillsammans med namn på medlemmar som har hyrt dem. Det är tydligt att vissa filmer inte har hyrts av någon. Vi kan helt enkelt använda VÄNSTER GÅ för syftet.

LEFT JOIN returnerar alla rader från tabellen till vänster även om inga matchande rader har hittats i tabellen till höger. Om inga matchningar har hittats i tabellen till höger, returneras 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`

Exekvera skriptet ovan i MySQL workbench ger. Du kan se att i det returnerade resultatet som listas nedan, att för filmer som inte hyrs, har medlemsnamnsfälten NULL-värden. Det betyder att ingen matchande medlem hittades i medlemstabellen för just den filmen.

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



Note: Null is returned for non-matching rows on right

HÖGER GÅ MED

RIGHT JOIN är uppenbarligen motsatsen till LEFT JOIN. RIGHT JOIN returnerar alla kolumner från tabellen till höger även om inga matchande rader har hittats i tabellen till vänster. Om inga matchningar har hittats i tabellen till vänster, returneras NULL.

I vårt exempel, låt oss anta att du behöver få namn på medlemmar och filmer som hyrs av dem. Nu har vi en ny medlem som inte har hyrt någon film än

SELECT A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Exekvera skriptet ovan i MySQL arbetsbänk ger följande resultat.

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



Note: Null is returned for non-matching rows on left

"ON" och "USING" klausulerna

Varje fråga hittills har matchade rader med en ON-klausul. MySQL erbjuder ett kortare alternativ när de matchande kolumnerna delar samma namn.

I ovanstående JOIN-frågeexempel har vi använt ON-satsen för att matcha posterna mellan tabeller.

USING-satsen kan också användas för samma ändamål. Skillnaden med ANVÄNDER SIG AV är det måste ha identiska namn för matchade kolumner i båda tabellerna.

I tabellen "filmer" har vi hittills använt dess primärnyckel med namnet "id". Vi hänvisade till samma i tabellen "medlemmar" med namnet "film_id".

Låt oss byta namn på "movies"-tabellernas "id"-fält för att få namnet "movie_id". Vi gör detta för att ha identiska matchade fältnamn.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Låt oss sedan använda USING med ovanstående LEFT JOIN exempel.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

Bortsett från att använda ON och ANVÄNDER med JOINs du kan använda många andra MySQL klausuler som GRUPP AV, VAR och till och med fungerar som SUMMA, AVGEtc.

Vanliga frågor