MySQL JOINS Tutorial: VNITŘNÍ, VNĚJŠÍ, VLEVO, VPRAVO, KŘÍŽ

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 nejvíce nepochopené téma mezi SQL leanery. Pro jednoduchost a srozumitelnost použijeme k procvičení ukázku z nové databáze. Jak je ukázáno níže

id	jméno	příjmení	movie_id
1	Adam	kovář	1
2	Ravi	Kumar	2
3	Susan	Davidson	5
4	Janička	Adrianna	8
5	Závětří	Pong	10

id	titul	kategorie
1	ASSASSIN'S CREED: EMBERS	Animace
2	Skutečná ocel (2012)	Animace
3	Alvin a Chipmunkové	Animace
4	Dobrodružství cínového cínu	Animace
5	Bezpečný (2012)	Akce
6	Safe House (2012)	Akce
7	GIA	18+
8	Termín 2009	18+
9	Špinavý obrázek	18+
10	Marley a já	Romantika

Typy spojení

Křížek JOIN

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

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 vypůjčili filmy, spolu s názvy filmů, které si půjčili. K tomu lze jednoduše použít INNER JOIN, který vrací řádky z obou tabulek vyhovující daným podmínkám.

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

MySQL Outer JOINs vrátí všechny odpovídající 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 zmateně? 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á. Můžete vidět, že ve vráceném výsledku, který je uveden níže, u filmů, které nejsou pronajaty, mají pole jména členů hodnoty NULL. To znamená, že žádný odpovídající člen nenašel tabulku členů pro tento konkrétní film.

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“.

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 si POUŽÍVÁNÍ s JOINy můžete použít mnoho dalších MySQL klauzule jako SESKUPIT PODLE, KDE a dokonce funguje jako SOUČET, AVG, Etc.

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

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ů.

Shrnutí

• JOINS nám umožňují kombinovat data z více než jedné tabulky do jedné sady výsledků.
• JOINS mají lepší výkon ve srovnání s dílčími dotazy
• INNER JOINS vrátí pouze řádky, které splňují daná kritéria.
• OUTER JOINS mohou také vrátit řádky, kde nebyly nalezeny žádné shody. Neshodné řádky jsou vráceny s klíčovým slovem NULL.
• Mezi hlavní typy JOIN patří Inner, Left Outer, Right Outer, Cross JOINS atd.
• Často používaná klauzule v operacích JOIN je „ON“. Klauzule „USING“ vyžaduje, aby odpovídající sloupce měly stejný název.
• JOINS lze také použít v jiných klauzulích, jako je GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS atd.