Ознайомлення з операторами 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 | Petro     | Shevchenko  |
|       1 | 	   2 | Ivan      | Goncharenko |
|       2 | 	   3 | Petro     | 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 Petro  | Shevchenko  | Petro     |
+-------------------+-------------+-----------+
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 слід враховувати, що процес об’єднання таблиць може бути ресурсомістким, тому слід поєднувати лише таблиці, дані з яких дійсно необхідні. Чим більше таблиць ви поєднуєте, тим більше буде знижуватись продуктивність.

Дякуємо за увагу! Сподіваємось, що даний урок приніс вам практичну користь.