MySQL Bli med: Indre, Ytre, Venstre, Høyre, Kryss

Hva er JOINS?

Sammenføyninger hjelper til med å hente data fra to eller flere databasetabeller.

Tabellene er gjensidig relatert ved bruk av primær- og fremmednøkler.

Merk: JOIN er det mest misforståtte emnet blant SQL-utdannede. For enkelhets skyld og forståelsesmessig bruker vi en ny database som et eksempel på en praksis. Som vist nedenfor.

Alle eksemplene nedenfor bruker disse to tabellene. movie_id kolonne i medlemmer peker på id kolonne i filmer — forholdet hver JOIN samsvarer med.

medlemmer

filmer

Hvorfor bør vi bruke JOINS?

Før vi ser på hver JOIN-type, er det verdt å vite hvorfor en JOIN er foretrukket fremfor å kjøre flere spørringer.

Nå tenker du kanskje hvorfor vi bruker JOINs når vi kan utføre den samme oppgaven som kjører spørringer. Spesielt hvis du har litt erfaring med databaseprogrammering vet du at vi kan kjøre spørringer én etter én, bruk utdata fra hver i påfølgende spørringer. Selvfølgelig er det mulig. Men ved å bruke JOINs, kan du få arbeidet gjort ved å bruke bare ett søk med alle søkeparametere. På den annen side MySQL kan oppnå bedre ytelse med JOINs da den kan bruke indeksering. Bare bruk av enkelt JOIN-spørring i stedet for å kjøre flere spørringer reduserer serveroverhead. Bruker flere spørringer i stedet som fører til flere dataoverføringer mellom MySQL og applikasjoner (programvare). Videre krever det mer datamanipulasjoner i applikasjonsenden også.

Det er klart at vi kan oppnå bedre MySQL og applikasjonsytelser ved bruk av JOINs.

Typer av JOINS

MySQL støtter flere JOIN-typer, som hver besvarer et annet spørsmål om de samme to tabellene. Tabellen nedenfor sammenligner dem; hver type demonstreres deretter med en spørring og dens utdata.

KRYSS BLI MED

Cross JOIN er en enkleste form for JOINs som matcher hver rad fra en databasetabell til alle rader i en annen.

Med andre ord gir det oss kombinasjoner av hver rad i første tabell med alle poster i andre tabell.

Anta at vi ønsker å få alle medlemsrekordene mot alle filmrekordene, kan vi bruke skriptet vist nedenfor for å få de ønskede resultatene.

SELECT * FROM `movies` CROSS JOIN `members`

Utfører skriptet ovenfor i MySQL arbeidsbenk gir oss følgende resultater.

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 returnerer alle mulige par, noe som sjelden er det du ønsker. En INNER JOIN snevrer resultatet inn til parene som faktisk er relaterte.

Den indre JOIN brukes til å returnere rader fra begge tabellene som tilfredsstiller den gitte betingelsen.

Anta at du ønsker å få en liste over medlemmer som har leid filmer, sammen med titler på filmer de har leid. Du kan ganske enkelt bruke en INNER JOIN til det, som returnerer rader fra begge tabellene som oppfyller de gitte betingelsene.

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

Utførelse av skriptet ovenfor gi

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

Merk at resultatskriptet ovenfor også kan skrives som følger for å oppnå de samme resultatene.

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

Ytre JOINs

En INNER JOIN fjerner rader som ikke har noen partner i stillhet. Når disse ikke-matchede radene er viktige, er en OUTER JOIN det riktige valget.

MySQL Ytre JOIN-er returnerer alle samsvarende poster fra begge tabellene.

Den kan oppdage poster som ikke samsvarer i sammenføyd tabell. Den kommer tilbake NULL verdier for poster for sammenføyd tabell hvis ingen samsvar er funnet.

Høres forvirrende ut? La oss se på et eksempel –

VENSTRE BLI MED

Anta at du nå ønsker å få titler på alle filmer sammen med navn på medlemmer som har leid dem. Det er tydelig at noen filmer ikke har blitt leid av noen. Vi kan rett og slett bruke VENSTRE BLI MED for formålet.

LEFT JOIN returnerer alle radene fra tabellen til venstre selv om ingen samsvarende rader er funnet i tabellen til høyre. Der det ikke er funnet treff i tabellen til høyre, returneres 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`

Utfører skriptet ovenfor i MySQL arbeidsbenken gir. Du kan se at i det returnerte resultatet, som er oppført nedenfor, har medlemsnavnfeltene NULL-verdier for filmer som ikke er leid. Det betyr at ingen samsvarende medlemmer fantes i medlemstabellen for den aktuelle 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

RIKTIG MEDLEM

RIGHT JOIN er åpenbart det motsatte av LEFT JOIN. RIGHT JOIN returnerer alle kolonnene fra tabellen til høyre selv om ingen samsvarende rader er funnet i tabellen til venstre. Der det ikke er funnet treff i tabellen til venstre, returneres NULL.

I vårt eksempel, la oss anta at du trenger å få navn på medlemmer og filmer leid av dem. Nå har vi et nytt medlem som ikke har leid noen film ennå

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

Utfører skriptet ovenfor i MySQL arbeidsbenk gir følgende resultater.

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" og "USING" klausuler

Alle spørringer så langt har samsvarende rader med en ON-klausul. MySQL tilbyr et kortere alternativ når de samsvarende kolonnene deler navn.

I JOIN-spørringseksemplene ovenfor har vi brukt ON-klausulen for å matche postene mellom tabellene.

USING-klausulen kan også brukes til samme formål. Forskjellen med VED HJELP AV er det må ha identiske navn for samsvarende kolonner i begge tabellene.

I "filmer"-tabellen har vi så langt brukt primærnøkkelen med navnet "id". Vi refererte til det samme i "members"-tabellen med navnet "movie_id".

La oss gi nytt navn til «movies»-tabeller «id»-feltet for å få navnet «movie_id». Vi gjør dette for å ha identiske samsvarende feltnavn.

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

La oss deretter bruke USING med eksempelet ovenfor LEFT JOIN.

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

Bortsett fra å bruke ON og BRUK med JOINs du kan bruke mange andre MySQL klausuler som GRUPPE AV, HVOR og til og med funksjoner som SUM, AVGOsv

Spørsmål og svar