Головна 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
 

Коментарі  

 
0 #3 Fredrick 21.06.2017 23:12
I leave a leave a response when I especially enjoy
a post on a site or if I have something to contribute to the discussion. It is
triggered by the fire communicated foot pain in the arch the article I
read. And after this post Принцип оператора JOIN в картинках.
I was actually excited enough to drop a thought :-P I do have a couple of questions for you
if you do not mind. Could it be only me or do some of these comments appear as if they are written by brain dead people?
:-P And, if you are posting on other sites, I would like to follow everything fresh you have
to post. Would you list all of your public pages
like your linkedin profile, Facebook page or twitter feed?
Цитувати
 
 
0 #2 Ethel 07.06.2017 11:28
I drop a comment when I especially enjoy a post
on a website or I have something to add to the conversation. It is
caused by the passion displayed in the article I read.
And on this post Принцип оператора JOIN в картинках.
I was excited enough to drop a leave a responsea
response :) I do have a few questions for you if
it's okay. Is it just me or does it look as if like some of these responses appear as
if they are coming from brain dead people? :-P And, if you are writing on additional places, I'd like to keep up with you.
Would you list all of all your public sites like your twitter feed,
Facebook page or linkedin profile?

Also visit my homepage ... https://terinaaudette.wordpress.com/
Цитувати
 
 
0 #1 Mozelle 20.09.2014 16:17
Greetings! Very helpful advice within this post! It is the little changes which will
make the largest changes. Thanks ffor sharing!

Look at my blog; Clash of Clans Hack (Mozelle)
Цитувати
 

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


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

© 2008-2013 PHPist