Знакомство с операторами INNER, LEFT, RIGHT, SELF и CROSS JOIN в MySQL

 

 

В этом практическом уроке мы узнаем о различных вариантах объединения данных из двух (или более) таблиц в запросе SELECT при работе с MySQL, а именно: INNER JOIN, LEFT JOIN, RIGHT JOIN, SELF JOIN и CROSS JOIN.

 

Введение в JOIN – запрос объединения результатов из нескольких таблиц в MySQL

Слово «Join» переводится как «присоединять, связывать» и т.д. Именно такую задачу этот оператор и выполняет при работе с базами данных MySQL: берет данные из нескольких таблиц и объединяет в один исходный результат.

Присоединение осуществляется с помощью общих для нескольких таблиц столбцов. Эти столбцы называются столбцами внешнего ключа (Foreign Key). Присоединение полезно, когда информация разбросана по разным таблицам и ее нужно собрать вместе. Например, у вас есть таблица users с данными клиентов, а в другой таблице orders вы храните данные по заказам клиентов. Объединив эти две таблицы, вы будете видеть более широкую картину: и данные клиента, и детали заказов этого клиента в одном месте.

Объединение с помощью оператора JOIN — это метод связывания данных между одной (самообъединение – self-join) или несколькими таблицами на основе значений общего столбца между таблицами. Это позволяет получать данные за один SQL-запрос без создания временных таблиц.

Используя JOIN, можно объединять как две таблицы, так и гораздо больше.

Оператор присоединения используется в операторе SELECT, и следует после запроса FROM.

После ключевого слова ON указывается условие присоединения. Это условие указывает, как две (или более) таблицы будут сравниваться.

Если объединять таблицы через запятую, тогда нельзя использовать конструкции ON и USING, такое условие может быть задано только в конструкции WHERE.

В MySQL используются следующие типы подключений:

• Внутреннее присоединение (INNER JOIN)
• Присоединение слева внешнее (LEFT JOIN)
• Присоединение внешнее справа (RIGHT JOIN)
• Перекрестное объединение (CROSS JOIN)

 

Например, рассмотрим вариант, когда у вас есть 2 таблицы: users и orders. Общими для двух таблиц являются столбцы user_id. Ниже рассмотрим примеры использования различных типов присоединения.

 

Пример использования INNER JOIN в MySQL

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

Если в условии объединения используется оператор равенства (=), а названия столбцов в обеих таблицах, используемых для сопоставления, одинаковы, вместо ON можно использовать условие USING. Вот базовый синтаксис внутреннего JOIN, объединяющий наши две таблицы:

SELECT
  order_id
FROM
  orders
INNER JOIN users ON {условие};

 

Детали запроса:

• Выбираем нужные данные оператором SELECT (в данном случае order_id – номер заказа)
• Главная таблица указывается в условии FROM (orders).
• Потом указываем таблицу, которая будет объединена с основной таблицей в условии INNER JOIN (users).
• Указываем условия объединения после ключевых слов ON. Условие присоединения определяет правило соответствия строк между основной таблицей и другой таблицей (поиск общих данных). Это условие известно как предикат объединения. В данном примере мы используем общее поле user_id – идентификатор клиента.

 

Поскольку обе таблицы используют тот же столбец (user_id), вы можете использовать конструкцию USING вместо ON:

SELECT * FROM orders INNER JOIN users USING(user_id);

Результатом будет общая таблица с данными из двух таблиц. Что-то типа этого:

+---------+----------+-----------+-------------+
| user_id | order_id | firstname | lastname    |
+---------+----------+-----------+-------------+
|       2 | 	   1 | Petr      | Shevchenko  |
|       1 | 	   2 | Ivan      | Goncharenko |
|       2 | 	   3 | Petr      | Shevchenko  |
|       3 | 	   4 | Stepan    | Melnik      |
+---------+----------+-----------+-------------+
4 rows in set (0.00 sec)

 

Пример использования LEFT JOIN в MySQL

LEFT JOIN позволяет запрашивать данные из двух или более таблиц.

Подобно внутреннему объединению, для левого присоединения также требуется предикат соединения (условие). При объединении двух таблиц с помощью LEFT JOIN используется понятие левой и правой таблицы. В данном примере users — левая (первая) таблица, а orders — правая таблица. При LEFT JOIN данные отбираются, начиная с левой таблицы.

Каждая строка левой таблицы сравнивается с каждой строкой правой таблицы.

Если значения в двух строках удовлетворяют условию объединения, LEFT JOIN создает новую строку, столбцы которой содержат все столбцы строк обеих таблиц, и включает ее в набор результатов.

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

Другими словами, присоединение слева выбирает все данные из левой таблицы независимо от того, есть соответствующие строки в правой таблице или нет. Если соответствующие строки из правой таблицы не найдены, присоединение слева использует NULL для столбцов строки из правой таблицы в наборе результатов.

Вот более сложный синтаксис с использованием LEFT JOIN, объединяющий две таблицы:

SELECT
  orders.order_id,
  orders.created_at,
  users.lastname
FROM
  users
LEFT JOIN orders ON {условие}
WHERE {условие};

 

Детали запроса:

• Поскольку в разных таблицах могут быть столбцы с одинаковыми названиями, при определении столбцов для выборки мы указали их полное имя, включая имя таблицы, например «orders.order_id«
• Левое объединение также поддерживает условие USING, если столбец, используемый для сопоставления в обеих таблицах, одинаков
• Запрос LEFT JOIN очень полезен, если вы хотите найти строки в таблице, не имеющие соответствующей строки в другой таблице
• После ключевого слова ON мы можем использовать более сложные условия, например для фильтрации и/или сортировки результатов

 

Чтобы сократить код запроса, мы можем предыдущий пример записать с использованием псевдонимов (alias) таблиц, например:

SELECT
  o.order_id,
  o.created_at,
  u.lastname
FROM
  orders AS o
LEFT JOIN users AS u ON o.user_id = u.user_id
WHERE u.firstname = 'Ivan';

 

 

Пример использования RIGHT JOIN в MySQL

Запись присоединения справа подобна записи левого присоединения, за исключением того, что обработка таблиц обратная – сначала данные выбираются из правой таблицы и сравниваются с левой таблицей. Если строка правой таблицы не имеет соответствующих строк в левой таблице, столбец левой таблицы будет иметь значение NULL в конечном наборе результатов.

Вот пример запроса с использованием RIGHT JOIN, объединяющий наши две таблицы:

SELECT
  o.order_id,
  o.created_at,
  u.lastname
FROM
  users AS u
RIGHT JOIN orders AS o ON o.user_id = u.user_id;

 

Детали запроса:

• users — это левая таблица, а orders — правая таблица
• Важно подчеркнуть, что операторы RIGHT JOIN и LEFT JOIN функционально эквивалентны, и они могут заменять друг друга, если изменять порядок таблиц
• Если {условие} использует оператор равенства (=), а объединенные столбцы обеих таблиц имеют одинаковые названия, вы также можете использовать синтаксис USING(название столбца) вместо ON
• Обратите внимание, что запрос RIGHT OUTER JOIN является синонимом RIGHT JOIN. Поэтому вы можете использовать их как взаимозаменяемые
• Чтобы найти строки в правой таблице, не имеющие соответствующих строк в левой таблице, вы можете использовать условие WHERE с оператором IS NULL

 

Пример использования Self Join (самообъединения) в MySQL

Оператор Self JOIN — это оператор MySQL, который объединяет или обрабатывает таблицу в базе данных с самой собой. По сути, функция JOIN объединяет две или более таблиц в SQL, но здесь Self Join — это обычное объединение, позволяющее объединять записи в одной таблице на основе определенного условия.

Самообъединение позволяет подключить таблицу к самой себе. Поскольку MySQL не имеет специального синтаксиса самообъединения, вам нужно выполнять Self Join с помощью обычного объединения, такого как LEFT или INNER JOIN.

Поскольку вы ссылаетесь на ту же таблицу в одном запросе, вам нужно использовать псевдонимы таблицы, чтобы назначить таблице временное имя, когда вы ссылаетесь на нее во второй раз.

Чтобы выполнить самообъединение, выполните следующие действия:

• Назначьте каждому экземпляру таблицы уникальный псевдоним, чтобы различать их
• Укажите условие объединения: определите, как должны сравниваться строки из каждого экземпляра таблицы
• Выберите нужные столбцы: укажите столбцы, которые вы хотите включить в окончательный набор результатов
• На практике самообъединение используется для запроса иерархических данных, таких как отображение организационной структуры или сравнение строк в одной таблице

 

Пример базового синтаксиса запроса Self Join в MySQL:

SELECT
  CONCAT(u.lastname, ' ', o.firstname) AS Customer,
  u.lastname,
  o.firstname
FROM
  users u,
  users o
WHERE u.user_id = o.user_id;

 

Детали запроса:

• Здесь рассмотрен пример, когда в своей базе данных мы имеем таблицу под названием users, которой присвоены два псевдонима u и o. Выражение WHERE определяет условное выражение для фильтрации результатов.
• В MySQL Self-Join словно делает «селфи» самой себя и выдает результаты, рассматривая одну таблицу как две с помощью псевдонимов, чтобы избежать повторения названия таблицы. Иначе это привело бы к ошибке.

 

Результатом предыдущего запроса будет что-то типа этого:

+-------------------+-------------+-----------+
| Customer          | lastname    | firstname |
+-------------------+-------------+-----------+
| Goncharenko Ivan  | Goncharenko | Ivan      |
| Shevchenko Petr   | Shevchenko  | Petr      |
+-------------------+-------------+-----------+
2 rows in set (0.00 sec)

 

 

Пример использования CROSS JOIN в MySQL

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

Предположим, что первая таблица имеет a строк, а вторая таблица имеет b строк. Тогда перекрестное объединение CROSS JOIN вернет a * b строк. Набор результатов будет включать все строки из обеих таблиц, где каждая строка представляет собой комбинацию каждой строки в первой таблице с каждой строкой во второй таблице.

Ниже показан синтаксис перекрестного соединения CROSS JOIN:

SELECT      {название столбца(ов)}
FROM        {таблица 1}
CROSS JOIN  {таблица 2};

 

Детали запроса:

• Как правило, использование таких запросов требуется нечасто. Но бывают и исключения. К примеру, когда вам нужно получить все возможные комбинации записей данных в таблицах. Предположим, что у вас есть два столбца: размер и цвет, и вам нужен набор результатов для отображения всех возможных парных комбинаций этих столбцов
• Обратите внимание, что в отличие от присоединения INNER JOIN, LEFT JOIN и RIGHT JOIN, функция CROSS JOIN не имеет предиката соединения. Другими словами, она не использует условие ON или USING.

 

 

Вот и все на сегодня!

В данном практическом уроке вы ознакомились с разными операторами присоединения JOIN в MySQL, включая внутреннее присоединение (INNER JOIN), присоединение слева (LEFT JOIN), присоединение справа (RIGHT JOIN), самоприсоединение (Self Join) и перекрестное присоединение (CROSS JOIN), используемые для получения данных с одной, из двух или более таблиц. При использовании оператора JOIN следует учитывать, что процесс объединения таблиц может быть ресурсоемким, поэтому следует присоединять только таблицы, данные из которых действительно необходимы. Чем больше таблиц вы присоединяете, тем больше будет снижаться производительность.

Спасибо за внимание! Надеемся, что данный урок принес вам практическую пользу.