MySQL Учебное пособие по соединениям: ВНУТРЕННЕЕ, ВНЕШНЕЕ, ЛЕВОЕ, ПРАВОЕ, КРЕСТОВОЕ
Маркус Аллен
Что такое СОЕДИНЕНИЯ?
Объединения помогают извлекать данные из двух или более таблиц базы данных.
Таблицы связаны друг с другом с помощью первичных и внешних ключей.
Примечание. JOIN — самая непонятая тема среди любителей SQL. Для простоты и удобства понимания мы будем использовать новую базу данных для практической выборки. Как показано ниже
| id | имя | Фамилия | Movie_id |
|---|---|---|---|
| 1 | Адам | Smith | 1 |
| 2 | Ravi | Кумаром | 2 |
| 3 | Сьюзен | Davidson | 5 |
| 4 | Jenny | Адрианна | 8 |
| 5 | подветренный | Pong | 10 |
| id | название | Каталог |
|---|---|---|
| 1 | ASSASSIN'S CREED: УГЛИ | Развлечения |
| 2 | Настоящая сталь (2012) | Развлечения |
| 3 | Элвин и бурундуки | Развлечения |
| 4 | Приключения олова олова | Развлечения |
| 5 | Сейф (2012) | Экшн |
| 6 | Безопасный дом (2012) | Экшн |
| 7 | GIA | 18+ |
| 8 | Срок 2009 | 18+ |
| 9 | Грязная картина | 18+ |
| 10 | Марли и я | Romance |
Типы объединений
Перекрестное ПРИСОЕДИНЕНИЕ
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 |
INNER 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 |
ПРАВО ПРИСОЕДИНИТЬСЯ
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 |
Предложения «ON» и «USING»
В приведенных выше примерах запросов JOIN мы использовали предложение ON для сопоставления записей между таблицами.
Предложение USING также можно использовать для той же цели. Разница с С ПОМОЩЬЮ это должны иметь одинаковые имена для совпадающих столбцов в обеих таблицах.
В таблице «movies» до сих пор мы использовали ее первичный ключ с именем «id». Мы ссылались на то же самое в таблице «members» с именем «movie_id».
Давайте переименуем поле «id» таблицы «movies», чтобы оно имело имя «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 можно добиться лучшей производительности с JOINs, поскольку он может использовать индексирование. Простое использование одного запроса JOIN вместо выполнения нескольких запросов снижает нагрузку на сервер. Вместо этого используйте несколько запросов, что приводит к увеличению передачи данных между MySQL и приложения (программное обеспечение). Кроме того, это также требует дополнительных манипуляций с данными в конце приложения.
Понятно, что мы можем добиться большего MySQL и производительность приложений за счет использования JOIN.
Резюме
- JOINS позволяют нам объединять данные из нескольких таблиц в один набор результатов.
- JOINS имеют лучшую производительность по сравнению с подзапросами.
- INNER JOINS возвращает только те строки, которые соответствуют заданным критериям.
- OUTER JOINS также может возвращать строки, для которых совпадений не обнаружено. Несовпадающие строки возвращаются с ключевым словом NULL.
- Основные типы JOIN включают Inner, Left Outer, Right Outer, Cross JOINS и т. д.
- Часто используемое предложение в операциях JOIN — «ON». Предложение «USING» требует, чтобы соответствующие столбцы имели одно и то же имя.
- JOINS также можно использовать в других предложениях, таких как GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS и т. д.
