Головна MySQL Принцип оператора JOIN в картинках
Принцип оператора JOIN в картинках

Принцип оператора JOINПри розробці веб-проектів з використанням бази даних нам часто потрібно в запитах об'єднувати таблиці бази, щоб отримати необхідні дані.

В статті розглянуті принципи об'єднання таблиць і, для наочності, вони зображені на кругових діаграмах

Попередження: в статті в умові об'єднання таблиць використовується як ON, так і USING, для різноманітності. Нагадую, що якщо стовпчики, по яких відбувається об'єднання, мають однакові імена, то необхідно використовувати USING (`ім'я_стовпчика`), в іншому випадку використовується ON `TableA`.`ім'я_стовпчика_з_TableA` = `TableB`.`ім'я_стовпчика_з_TableB`

Отже, припустімо, що в нас є два столи. Стіл А (TableA) зліва, та стіл Б (TableB) справа. Ми заселимо кожен чотирма персонажами, імена яких можуть бути присутні на обох столах.

TableA TableB
id name id name
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

В СУБД MySQL існують наступні оператори об'єднання:

  • CROSS JOIN, він же INNER JOIN, він же JOIN
  • Аналоги FULL OUTER JOIN для MySQL
  • LEFT JOIN
  • RIGHT JOIN
  • NATURAL JOIN
  • STRAIGHT JOIN
  • Якщо не вказувати USING або ON в об'єднанні (Декартова вибірка)

INNER JOIN

— INNER JOIN здійснює вибірку записів, які лише існують в TableA і TableB одночасно.
— CROSS JOIN — це еквівалент INNER JOIN.
— INNER JOIN можна замінити умовою об'єднання в WHERE.

Запит:

SELECT * FROM `TableA` 2.INNER JOIN `TableB` 3.ON `TableA`.`name` = `TableB`.`name`

Ідентичний запит:

SELECT * FROM `TableA`,`TableB`
WHERE `TableA`.`name` = `TableB`.`name`

Результат:

TableA TableB
id name id name
1 Pirate 2 Pirate
3 Ninja 4 Ninja
Диаграмма INNER (CROSS) JOIN

LEFT JOIN

LEFT OUTER JOIN (LEFT JOIN) вказує, що ліва таблиця, керівна (в нашому випадку TableA) і здійснює по ній повну вибірку, здійснюючи пошук відповідних записів в таблиці TableB. Якщо відповідностей не знайдено, то СУБД поверне порожній показник - NULL. Вказівка OUTER - не обов'язково.

Запит:

SELECT * FROM `TableA`
LEFT JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`

Результат:

TableA TableB
id name id name
1 Pirate 2 Pirate
2 Monkey NULL NULL
3 Ninja 4 Ninja
4 Spaghetti NULL NULL
Диаграмма LEFT JOIN

Щоб здійснити вибірку записів з таблиці TableA, яких не існує в таблиці TableB, ми виконуємо LEFT JOIN, але після цього з результату виключаємо записи, які не хочемо бачити, шляхом вказівки, що TableB.id являється нулем (вказуючи, що запис відсутній у таблиці TableB).

Запит:

SELECT * FROM `TableA`
LEFT JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`
WHERE `TableB`.`id` IS NULL

Результат:

TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
Диаграмма LEFT JOIN (Записи существуют только в левой таблице)

RIGHT JOIN

RIGHT JOIN виконує ті ж функції, що й LEFT JOIN, за винятком того, що права таблиця буде прочитана першою. Таким чином, якщо в запитах з попереднього розділу LEFT замінити на RIGHT, то таблиця результатів, грубо кажучи, відлобразиться по вертикалі. Тобто, в результаті замість значень TableA будуть записи TableB і навпаки.

NATURAL JOIN

Суть цієї конструкції в тому, що СУБД сама вибирає, по яких стовпчиках порівнювати та об'єднувати таблиці. А вибір цей падає на стовпчики з однаковими іменами. В цьому криється засада — СУБД може вибрати зовсім не ті стовпчики для об'єднання і запит буде працювати зовсім не так як ви розраховували.

Запит:

SELECT * FROM `TableA`
NATURAL JOIN `TableB`

В цьому випадку СУБД вибирає для об'єднання таблиць стовпчики id і name, так як вони присутні в обох таблицях і перетворює початковий запит в запит наступного виду:

SELECT * FROM `TableA`
INNER JOIN `TableB`
USING (`id`, `name`)

Але так як у нас немає записів з однаковим id і name одночасно в обох таблицях, то запит поверне порожній результат.
Якщо ж зробити ліву таблицю керівною і змінити запит:

SELECT `TableA`.*, `TableB`.* FROM `TableA`
NATURAL LEFT JOIN `TableB`

Такий запит приводиться СУБД до наступного:

SELECT `TableA`.*, `TableB`.* FROM `TableA`
LEFT JOIN `TableB`
USING (`id`, `name`)

То результат буде таким:

TableA TableB
id name id name
1 Pirate NULL NULL
2 Monkey NULL NULL
3 Ninja NULL NULL
4 Spaghetti NULL NULL

Відбувається це так: так як ліва таблиця керівна, то вона читається першою і повністю вибирається, незалежно від правої таблиці; коли починається пошук відповідних записів в правій таблиці, то СУБД не знаходить жодного запису, який був би ідентичним name і id одночасно, тому повертаються порожні показники.

Для детального розуміння роботи NATURAL JOIN змінимо name в першому записі в таблиці TableB на Pirate.

UPDATE `TableB` SET `name`='Pirate' WHERE `id`=1

Таким чином у нас вийшло:

TableA TableB
id name id name
1 Pirate 1 Pirate
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja


А тепер виконаємо ті ж запити з NATURAL JOIN, що використані вище.

Запит:

SELECT * FROM `TableA`
NATURAL JOIN `TableB`

Результат:

id name
1 Pirate

Так як тепер запис з одинаковим id і name присутній в обох таблицях, то він і буде виведений.

Запит:

SELECT `TableA`.*, `TableB`.* FROM `TableA`
NATURAL LEFT JOIN `TableB`

Повертає результат:

TableA TableB
id name id name
1 Pirate 1 Pirate
2 Monkey NULL NULL
3 Ninja NULL NULL
4 Spaghetti NULL NULL

Таким чином, СУБД сама вибирає по яких стовпчиках і яким способом об'єднувати таблиці. З одного боку це доволі зручно, з іншого — несе нерозбериху: де гарантія того, що стовпчики з однаковими іменами в в таблицях будуть саме ключовими і призначені для об'єднання? NATURAL JOIN погіршує читабельність коду, так як розробник не зможе за запитом визначити, як об'єднуються таблиці. Тому, звертаючи увагу на такі фактори, NATURAL JOIN використовувати не рекомендується.

STRAIGHT JOIN

STRAIGHT JOIN виконує ті ж функції, що й звичайний INNER JOIN, за винятком того що ліва таблиця читається раніше правої.

Запит:

SELECT * FROM `TableA`
STRAIGHT JOIN `TableB` USING(`name`)

Поверне результат:

TableA TableB
name id id
Pirate 1 1
Pirate 1 2
Ninja 3 4

Запит:

SELECT * FROM `TableB`
STRAIGHT JOIN `TableA` USING(`name`)

Поверне результат:

TableA TableB
name id id
Pirate 1 1
Pirate 2 1
Ninja 4 3

Декартова вибірка

Якщо при об'єднанні таблиць не вказати умову об'єднання через ON або USING, то СУБД здійснить так звану Декартову вибірку, коли значенню однієї таблиці прирівнюється кожне значення іншої. Таким чином, СУБД, в нашому випадку, повертає 4x4 = 16 рядків.

Запит:

SELECT * FROM `TableA`
JOIN `TableB`

Результат:

TableA TableB
id name id name
1 Pirate 1 Rutabaga
2 Monkey 1 Rutabaga
3 Ninja 1 Rutabaga
4 Spaghetti 1 Rutabaga
1 Pirate 2 Pirate
2 Monkey 2 Pirate
3 Ninja 2 Pirate
4 Spaghetti 2 Pirate
1 Pirate 3 Darth Vader
2 Monkey 3 Darth Vader
3 Ninja 3 Darth Vader
4 Spaghetti 3 Darth Vader
1 Pirate 4 Ninja
2 Monkey 4 Ninja
3 Ninja 4 Ninja
4 Spaghetti 4 Ninja
 

Додати коментар


Захисний код
Оновити

© 2008-2012 PHPist