MySQL Spojení: Vnitřní, Vnější, Levý, Pravý, Křížový

Co jsou JOINS?

Spojení pomáhají načíst data ze dvou nebo více databázových tabulek.

Tabulky jsou vzájemně propojeny pomocí primárního a cizího klíče.

Poznámka: JOIN je mezi znalci SQL nejvíce nepochopené téma. Pro jednoduchost a snadnější pochopení použijeme k procvičování novou databázi. Jak je uvedeno níže.

Každý níže uvedený příklad používá tyto dvě tabulky. movie_id sloupec v Členové ukazuje na id sloupec v filmy — vztah, na který se vztahuje každý JOIN.

Členové

filmy

Proč bychom měli používat JOINy?

Než se podíváme na jednotlivé typy JOIN, je vhodné vědět, proč je JOIN výhodnější než spuštění několika dotazů.

Možná si teď pomyslíte, proč používáme JOINy, když můžeme dělat stejnou úlohu spouštěním dotazů. Zejména pokud máte nějaké zkušenosti s programováním databází, víte, že můžeme spouštět dotazy jeden po druhém, použijte výstup každého v po sobě jdoucích dotazech. To je samozřejmě možné. Ale pomocí JOINů můžete práci provést pomocí pouze jednoho dotazu s libovolnými parametry vyhledávání. Na druhou stranu MySQL může dosáhnout lepšího výkonu s JOINy, protože může používat indexování. Pouhé použití jediného dotazu JOIN namísto spuštění více dotazů snižuje režii serveru. Místo toho použití více dotazů, které mezi sebou vede více datových přenosů MySQL a aplikace (software). Dále to vyžaduje více manipulací s daty na konci aplikace.

Je jasné, že můžeme dosáhnout lepších výsledků MySQL a výkon aplikací pomocí JOINů.

Typy spojení JOIN

MySQL podporuje několik typů JOIN, z nichž každý odpovídá na jinou otázku týkající se stejných dvou tabulek. Níže uvedená tabulka je porovnává; každý typ je poté demonstrován pomocí dotazu a jeho výstupu.

KRÍŽNÍ PŘIPOJENÍ

Cross JOIN je nejjednodušší forma JOINů, která porovnává každý řádek z jedné databázové tabulky se všemi řádky jiné tabulky.

Jinými slovy, dává nám kombinace každého řádku první tabulky se všemi záznamy ve druhé tabulce.

Předpokládejme, že chceme získat všechny členské záznamy proti všem filmovým záznamům, můžeme použít níže uvedený skript k dosažení požadovaných výsledků.

SELECT * FROM `movies` CROSS JOIN `members`

Spuštění výše uvedeného skriptu v MySQL ponk nám dává následující výsledky.

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

CROSS JOIN vrací všechny možné páry, což je zřídka to, co chcete. INNER JOIN zúží výsledek na páry, které spolu skutečně souvisejí.

Vnitřní JOIN slouží k vrácení řádků z obou tabulek, které splňují danou podmínku.

Předpokládejme, že chcete získat seznam členů, kteří si půjčili filmy, spolu s názvy filmů, které si půjčili. Můžete k tomu jednoduše použít INNER JOIN, který vrátí řádky z obou tabulek, které splňují zadané podmínky.

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

Provedení výše uvedeného skriptu give

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

Všimněte si, že výše uvedený skript výsledků lze také napsat následovně, abyste dosáhli stejných výsledků.

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

Vnější JOINy

VNITŘNÍ JOIN tiše odstraňuje řádky, které nemají partnera. Pokud na těchto neshodných řádcích záleží, je vnější JOIN tou správnou volbou.

MySQL Vnější spojení JOIN vrací všechny shodné záznamy z obou tabulek.

Dokáže detekovat záznamy, které se ve spojené tabulce neshodují. Vrací se NULL hodnoty pro záznamy spojené tabulky, pokud není nalezena žádná shoda.

Zní to matoucí? Podívejme se na příklad –

LEVÉ SPOJENÍ

Předpokládejme nyní, že chcete získat názvy všech filmů společně se jmény členů, kteří si je vypůjčili. Je jasné, že některé filmy si nikdo nepůjčuje. Můžeme jednoduše použít LEVÉ SPOJENÍ za účelem.

LEFT JOIN vrátí všechny řádky z tabulky vlevo, i když nebyly nalezeny žádné odpovídající řádky v tabulce vpravo. Pokud nebyly nalezeny žádné shody v tabulce vpravo, vrátí se 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`

Spuštění výše uvedeného skriptu v MySQL workbench dává. Z níže uvedeného vráceného výsledku můžete vidět, že u filmů, které nejsou půjčeny, mají pole s názvem člena hodnoty NULL. To znamená, že pro daný film nebyl nalezen žádný odpovídající člen v tabulce členů.

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

SPRÁVNÉ PŘIPOJENÍ SE

RIGHT JOIN je zjevně opakem LEFT JOIN. RIGHT JOIN vrátí všechny sloupce z tabulky vpravo, i když nebyly nalezeny žádné odpovídající řádky v tabulce vlevo. Pokud nebyly v tabulce nalevo nalezeny žádné shody, je vrácena hodnota NULL.

V našem příkladu předpokládejme, že potřebujete získat jména členů a filmy, které si půjčují. Nyní máme nového člena, který si zatím nepůjčil žádný film

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

Spuštění výše uvedeného skriptu v MySQL workbench dává následující výsledky.

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

Klauzule „ON“ a „USING“.

Každý dosavadní dotaz nalezl řádky s klauzulí ON. MySQL nabízí kratší alternativu, pokud odpovídající sloupce sdílejí název.

Ve výše uvedených příkladech dotazů JOIN jsme použili klauzuli ON k porovnání záznamů mezi tabulkami.

Ke stejnému účelu lze použít i klauzuli USING. Rozdíl s POUŽITÍ je to musí mít stejná jména pro odpovídající sloupce v obou tabulkách.

V tabulce „filmy“ jsme doposud používali její primární klíč s názvem „id“. Na totéž jsme odkazovali v tabulce „členové“ s názvem „id_filmu“.

Přejmenujme tabulky „filmy“ pole „id“ na název „movie_id“. Děláme to proto, abychom měli stejné názvy polí.

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

Dále použijeme USING s výše uvedeným příkladem LEFT JOIN.

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

Kromě použití ON a POUŽÍVÁNÍ s JOINy můžete použít mnoho dalších MySQL klauzule jako SKUPINA VYTVOŘENÁ, KDE a dokonce funguje jako SOUČET, AVG, Etc.

Nejčastější dotazy