MySQL Съединяване: Вътрешно, Външно, Ляво, Дясно, Кръстосано

Какво представляват JOINS?

Съединенията помагат за извличане на данни от две или повече таблици на база данни.

Таблиците са взаимно свързани с първични и външни ключове.

Забележка: JOIN е най-неразбраната тема сред изучаващите SQL. За по-голяма простота и по-лесно разбиране ще използваме нова база данни за пример. Както е показано по-долу

Всеки пример по-долу използва тези две таблици. movie_id колона в членове точки към id колона в кино — връзката, на която съответства всяко JOIN.

членове

кино

Защо трябва да използваме JOINS?

Преди да разгледаме всеки тип JOIN, е добре да знаем защо JOIN е за предпочитане пред изпълнението на няколко заявки.

Сега може би си мислите защо използваме JOIN, когато можем да изпълняваме същата задача, изпълнявайки заявки. Особено ако имате известен опит в програмирането на бази данни, знаете, че можем да изпълняваме заявки една по една, като използваме изхода на всяка в последователни заявки. Разбира се, това е възможно. Но използвайки JOIN, можете да свършите работата, като използвате само една заявка с всякакви параметри за търсене. От друга страна MySQL може да постигне по-добро представяне с JOIN, тъй като може да използва индексиране. Простото използване на единична JOIN заявка вместо изпълнение на множество заявки намалява натоварването на сървъра. Използването на множество заявки вместо това води до повече трансфери на данни между тях MySQL и приложения (софтуер). Освен това изисква повече манипулации на данни в края на приложението.

Ясно е, че можем да постигнем по-добро MySQL и производителност на приложения чрез използване на JOIN.

Видове JOIN-ове

MySQL поддържа няколко типа JOIN, всеки от които отговаря на различен въпрос относно едни и същи две таблици. Таблицата по-долу ги сравнява; всеки тип е демонстриран със заявка и нейния резултат.

КРЪСТОСТНА СЪЕДИНКА

Cross JOIN е най-простата форма на JOIN, която съпоставя всеки ред от една таблица на база данни с всички редове на друга.

С други думи, дава ни комбинации от всеки ред на първата таблица с всички записи във втората таблица.

Да предположим, че искаме да получим всички записи на членове срещу всички записи на филми, можем да използваме скрипта, показан по-долу, за да получим желаните резултати.

SELECT * FROM `movies` CROSS JOIN `members`

Изпълнение на горния скрипт в MySQL работна маса ни дава следните резултати.

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

ВЪВЕЖДАНЕ

CROSS JOIN връща всички възможни двойки, което рядко е това, което искате. INNER JOIN стеснява резултата до двойките, които действително са свързани.

Вътрешният JOIN се използва за връщане на редове от двете таблици, които отговарят на даденото условие.

Да предположим, че искате да получите списък с членове, които са наели филми, заедно със заглавията на филмите, наети от тях. Можете просто да използвате INNER JOIN за това, което връща редове от двете таблици, отговарящи на дадените условия.

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

Изпълнението на горния скрипт дава

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

Забележете, че горният скрипт за резултати може да бъде написан както следва, за да постигнете същите резултати.

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

Външни JOINs

ВЪТРЕШНОТО СЪЕДИНЯВАНЕ (INNER JOIN) тихо премахва редове, които нямат партньор. Когато тези несъответстващи редове са важни, външното СЪЕДИНЯВАНЕ (OUTER JOIN) е правилният избор.

MySQL Външните JOIN-ове връщат всички съвпадащи записи от двете таблици.

Може да открие записи, които нямат съвпадение в обединената таблица. Връща се NULL стойности за записи на обединена таблица, ако не бъде намерено съвпадение.

Звучи объркващо? Нека разгледаме един пример –

LEFT JOIN

Да предположим, че сега искате да получите заглавия на всички филми заедно с имената на членовете, които са ги наели. Ясно е, че някои филми не са наети от никого. Можем просто да използваме LEFT JOIN за целта.

LEFT JOIN връща всички редове от таблицата отляво, дори ако не са намерени съответстващи редове в таблицата отдясно. Когато не са намерени съвпадения в таблицата вдясно, се връща 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`

Изпълнение на горния скрипт в MySQL workbench дава. Можете да видите, че във върнатия резултат, който е посочен по-долу, за филми, които не са наети, полетата с имена на членове имат NULL стойности. Това означава, че не е намерена съответстваща таблица с членове за този конкретен филм.

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

ПРАВИЛНО ПРИСЪЕДИНЕНЕ

RIGHT JOIN очевидно е обратното на LEFT JOIN. RIGHT JOIN връща всички колони от таблицата отдясно, дори ако в таблицата отляво не са намерени съответстващи редове. Когато не са намерени съвпадения в таблицата отляво, се връща NULL.

В нашия пример нека приемем, че трябва да получите имена на членове и филми, наети от тях. Сега имаме нов член, който все още не е наел филм

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

Изпълнение на горния скрипт в MySQL workbench дава следните резултати.

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“ и „USING“.

Всяка заявка досега е съпоставяла редове с клауза ON. MySQL предлага по-кратка алтернатива, когато съответстващите колони споделят едно и също име.

В горните примери за JOIN заявки сме използвали клауза ON, за да съпоставим записите между таблиците.

Клаузата USING също може да се използва за същата цел. Разликата с ИЗПОЛЗВАНЕ Така ли трябва да има идентични имена за съответстващи колони в двете таблици.

В таблицата “films” досега използвахме нейния първичен ключ с името “id”. Позовахме се на същото в таблицата „членове“ с името „movie_id“.

Нека преименуваме полето „id“ на таблиците „filmovi“, за да има името „movie_id“. Правим това, за да имаме идентични съвпадащи имена на полета.

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

След това нека използваме USING с горния пример LEFT JOIN.

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

Освен използването ON намлява ИЗПОЛЗВАНЕ с JOIN можете да използвате много други MySQL клаузи като ГРУПИРАЙ ПО, КЪДЕТО и дори функции като SUM, AVGИ др

Въпроси и Отговори