MySQL З'єднання: Внутрішнє, Зовнішнє, Ліве, Праве, Перехресне

Що таке JOINS?

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

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

Примітка: JOIN – це найнезрозуміліша тема серед фахівців з SQL. Для простоти та зручності розуміння ми використовуватимемо нову базу даних для практичного прикладу. Як показано нижче.

У кожному прикладі нижче використовуються ці дві таблиці. movie_id колонка в членів вказує на id колонка в кіно — зв'язок, якому відповідає кожен JOIN.

членів

кіно

Чому нам слід використовувати JOIN?

Перш ніж розглядати кожен тип JOIN, варто знати, чому JOIN кращий за виконання кількох запитів.

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

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

Типи об'єднань (JOIN)

MySQL підтримує кілька типів 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 Зовнішні 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 також можна використовувати з тією ж метою. Різниця с ВИКОРИСТАННЯ є це повинні мати ідентичні назви для відповідних стовпців в обох таблицях.

У таблиці «фільми» досі ми використовували її первинний ключ з назвою «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 речення як GROUP BYДЕ і навіть такі функції, як SUM, AVG, І т.д.

Поширені запитання