MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

Anonim

Что такое СОЕДИНЕНИЯ?

Объединения помогают извлекать данные из двух или более таблиц базы данных. Таблицы взаимно связаны с помощью первичного и внешнего ключей.

Примечание. JOIN - это тема, которую чаще всего неправильно понимают среди сторонников SQL. Для простоты и простоты понимания мы будем использовать новую базу данных для отработки образца. Как показано ниже

я бы имя фамилия movie_id
1 Адам Смит 1
2 Рави Кумар 2
3 Сьюзен Дэвидсон 5
4 Дженни Адрианна 8
6 Ли Понг 10
я бы заглавие категория
1 КРЕДИ АССАСИНА: ПОСТУПЛЕНИЯ Анимации
2 Настоящая сталь (2012) Анимации
3 Элвин и бурундуки Анимации
4 Приключения олова олова Анимации
5 Сейф (2012) Действие
6 Безопасный дом (2012) Действие
7 GIA 18+
8 Срок сдачи 2009 г. 18+
9 Грязная картина 18+
10 Марли и я Романтика

Типы соединений

Кросс 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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Внутренний JOIN используется для возврата строк из обеих таблиц, удовлетворяющих заданному условию.

Предположим, вы хотите получить список участников, взявших напрокат фильмы, вместе с названиями фильмов, взятых напрокат. Вы можете просто использовать для этого INNER JOIN, который возвращает строки из обеих таблиц, которые удовлетворяют заданным условиям.

SELECT members.`first_name` , members.`last_name` , movies.`title`FROM members ,moviesWHERE 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 AINNER JOIN `movies` AS BON B.`id` = A.`movie_id`

Внешние СОЕДИНЕНИЯ

MySQL Outer JOIN возвращает все совпадающие записи из обеих таблиц.

Он может обнаруживать записи, не имеющие совпадений в объединенной таблице. Он возвращает значения NULL для записей объединенной таблицы, если совпадений не найдено.

Звучит сбивающе с толку? Давайте рассмотрим пример -

LEFT JOIN

Предположим, теперь вы хотите получить названия всех фильмов вместе с именами участников, которые их взяли напрокат. Понятно, что некоторые фильмы никто не арендует. Мы можем просто использовать для этой цели LEFT JOIN .

LEFT JOIN возвращает все строки из таблицы слева, даже если совпадающие строки не были найдены в таблице справа. Если в таблице справа не найдено совпадений, возвращается NULL.

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

Выполнение вышеуказанного сценария в рабочей среде MySQL дает. Вы можете видеть, что в возвращенном результате, который указан ниже, для фильмов, которые не взяты напрокат, поля имени члена имеют значения 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 ARIGHT JOIN `movies` AS BON B.`id` = A.`movie_id`

Выполнение вышеуказанного сценария в рабочей среде MySQL дает следующие результаты.

 

 

 

 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 также можно использовать для той же цели. Разница с USING заключается в том, что для совпадающих столбцов в обеих таблицах должны быть одинаковые имена.

В таблице "фильмы" до сих пор мы использовали ее первичный ключ с именем "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 ALEFT JOIN `members` AS BUSING ( `movie_id` )

Помимо использования ON и USING с JOIN, вы можете использовать многие другие предложения MySQL, такие как GROUP BY, WHERE и даже такие функции, как SUM , AVG и т. Д.

Почему мы должны использовать объединения?

Теперь вы можете подумать, зачем мы используем JOIN, когда мы можем выполнять ту же задачу, выполняя запросы. Особенно, если у вас есть некоторый опыт программирования баз данных, вы знаете, что мы можем выполнять запросы один за другим, использовать вывод каждого в последовательных запросах. Конечно, это возможно. Но, используя JOIN, вы можете выполнить работу, используя только один запрос с любыми параметрами поиска. С другой стороны, MySQL может достичь лучшей производительности с помощью JOIN, поскольку он может использовать индексирование. Простое использование одного запроса 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 и т. Д.