MySQL Підручник з ОБ’ЄДНАНЬ: ВНУТРІШНІЙ, ЗОВНІШНИЙ, ЛІВИЙ, ПРАВИЙ, ХРЕСТИТИЙ

Що таке JOINS?

Об’єднання допомагають отримувати дані з двох або більше таблиць бази даних.

Таблиці пов'язані між собою за допомогою первинних і зовнішніх ключів.

Примітка: JOIN є найбільш неправильно зрозумілою темою серед тих, хто вивчає SQL. Заради простоти та полегшення розуміння ми будемо використовувати нову базу даних для практичного зразка. Як показано нижче

id	ім'я	прізвище	movie_id
1	Адам	коваль	1
2	Ravi	Кумаром	2
3	Сьюзен	Девідсон	5
4	Дженні	Адріаном	8
5	Подветренний	Pong	10

id	назву	категорія
1	ASSASSIN'S CREED: EMBERS	Анімації
2	Реальна сталь (2012)	Анімації
3	Елвін і бурундуки	Анімації
4	Пригоди Тін Тина	Анімації
5	Безпечний (2012)	дію
6	Безпечний будинок (2012)	дію
7	GIA	18 +
8	Дедлайн 2009	18 +
9	Брудна картина	18 +
10	Ми з Марлі	Romance

Типи з'єднань

Хрест 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

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`

Зовнішні об'єднання

MySQL Зовнішні об'єднання повертають усі записи, що збігаються з обох таблиць.

Він може виявити записи, які не збігаються в об’єднаній таблиці. Воно повертається 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».

У наведених вище прикладах запиту JOIN ми використовували речення ON для відповідності записів між таблицями.

Речення USING також можна використовувати з тією ж метою. Різниця с ВИКОРИСТАННЯ є це повинні мати ідентичні назви для відповідних стовпців в обох таблицях.

У таблиці «фільми» досі ми використовували її первинний ключ з назвою «id». Ми посилалися на те саме в таблиці «члени» з назвою «movie_id».

Давайте перейменуємо поле «id» таблиці «кіно» на «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, І т.д.

Чому ми повинні використовувати об'єднання?

Тепер ви можете подумати, чому ми використовуємо JOIN, коли ми можемо виконувати те саме завдання, виконуючи запити. Особливо, якщо у вас є певний досвід програмування баз даних, ви знаєте, що ми можемо запускати запити один за одним, використовувати вихідні дані кожного в послідовних запитах. Звичайно, це можливо. Але використовуючи JOIN, ви можете виконувати роботу, використовуючи лише один запит із будь-якими параметрами пошуку. З іншого боку MySQL можна досягти кращої продуктивності з JOIN, оскільки він може використовувати індексування. Просте використання одного запиту JOIN замість виконання кількох запитів зменшує витрати на сервер. Замість цього використання кількох запитів призводить до збільшення кількості передачі даних між ними MySQL і програми (програмне забезпечення). Крім того, це вимагає більше маніпуляцій з даними в кінці програми.

Зрозуміло, що ми можемо досягти кращого MySQL і продуктивність програми за допомогою JOIN.

Підсумки

• JOINS дозволяють об’єднати дані з кількох таблиць в єдиний набір результатів.
• JOINs мають кращу продуктивність порівняно з підзапитами
• INNER JOINS повертає лише ті рядки, які відповідають заданим критеріям.
• OUTER JOINS також може повертати рядки, у яких не знайдено збігів. Невідповідні рядки повертаються з ключовим словом NULL.
• Основні типи JOIN включають внутрішні, ліві зовнішні, праві зовнішні, перехресні JOIN тощо.
• Часто використовуваним пунктом в операціях JOIN є «ON». Речення «USING» вимагає, щоб відповідні стовпці мали однакові назви.
• JOINS також можна використовувати в інших пунктах, таких як GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS тощо.