Что такое СОЕДИНЕНИЯ?
Примечание: JOIN — это наиболее неправильно понятая тема среди SQL-уровней. Для простоты и простоты понимания мы будем использовать новую базу данных для отработки образца. Как показано ниже
| Я бы | Имя | фамилия | movie_id |
|---|---|---|---|
| 1 | Адам | кузнец | 1 |
| 2 | Ravi | Кумар | 2 |
| 3 | Сьюзен | Davidson | 5 |
| 4 | Дженни | Адрианна | 8 |
| 6 | подветренный | Pong | 10 |
| Я бы | заглавие | категория |
|---|---|---|
| 1 | Кредо убийцы: EMBERS | Анимации |
| 2 | Real Steel (2012) | Анимации |
| 3 | Элвин и бурундуки | Анимации |
| 4 | Приключения Тин Тин | Анимации |
| 5 | Сейф (2012) | действие |
| 6 | Безопасный дом (2012) | действие |
| 7 | GIA | 18+ |
| 8 | Срок исполнения 2009 | 18+ |
| 9 | Грязная картина | 18+ |
| 10 | Марли и я | романс |
Типы соединений
Cross JOIN
Cross JOIN — это самая простая форма JOIN, которая сопоставляет каждую строку из одной таблицы базы данных со всеми строками другой.
Другими словами, это дает нам комбинации каждой строки первой таблицы со всеми записями во второй таблице.
Предположим, что мы хотим получить все записи о всех фильмах, мы можем использовать сценарий, показанный ниже, чтобы получить желаемые результаты.
SELECT * FROM `movies` CROSS JOIN `members`
Выполнение вышеуказанного скрипта в MySQL Workbench дает нам следующие результаты.
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
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Внутреннее 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 возвращает все строки из таблицы слева, даже если в таблице справа не найдено подходящих строк. Если в таблице справа совпадений не найдено, возвращается 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 верстаке дает. Вы можете видеть, что в возвращенном результате, который указан ниже, что для фильмов, которые не арендованы, поля имени элемента имеют значения NULL. Это означает, что для данного фильма не найдено ни одного подходящего участника.
Note: Null is returned for non-matching rows on right
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 дает следующие результаты.
Note: Null is returned for non-matching rows on left
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 также может быть использовано для той же цели. Разница с 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 и USING с JOIN, вы можете использовать многие другие предложения MySQL, такие как GROUP BY, WHERE, и даже такие функции, как SUM , AVG и т. Д.
Почему мы должны использовать соединения?
Теперь вы можете подумать, почему мы используем JOIN, когда можем выполнять ту же задачу, выполняя запросы. Особенно, если у вас есть некоторый опыт программирования баз данных, вы знаете, что мы можем запускать запросы один за другим, использовать вывод каждого в последовательных запросах. Конечно, это возможно. Но, используя JOIN, вы можете выполнить работу, используя только один запрос с любыми параметрами поиска. С другой стороны, MySQL может достичь лучшей производительности с помощью JOIN, поскольку он может использовать индексирование. Просто использование одного запроса JOIN вместо выполнения нескольких запросов сокращает нагрузку на сервер. Вместо этого используется несколько запросов, что приводит к большей передаче данных между MySQL и приложениями (программными) Далее это требует больше манипуляций с данными и в конце приложения.
Понятно, что мы можем добиться лучшей производительности MySQL и приложений с помощью JOIN.
Резюме
- СОЕДИНЕНИЯ позволяют нам объединять данные из более чем одной таблицы в один набор результатов.
- СОЕДИНЕНИЯ имеют лучшую производительность по сравнению с подзапросами
- INNER JOINS возвращают только те строки, которые соответствуют заданным критериям.
- ВНЕШНИЕ СОЕДИНЕНИЯ также могут возвращать строки, в которых совпадений не найдено. Несоответствующие строки возвращаются с ключевым словом NULL.
- Основные типы JOIN включают в себя Inner, Left Outer, Right Outer, Cross JOINS и т. Д.
- В операциях JOIN часто используется предложение «ON». Предложение «USING» требует, чтобы совпадающие столбцы имели одно и то же имя.
- СОЕДИНЕНИЯ могут также использоваться в других разделах, таких как GROUP BY, WHERE, SUB-запросы, агрегированные функции и т. Д.