Отсебятина Статьи Проекты
Облако тегов
Web CMS CSS htaccess HTML Javascript MySQL Php Безопасность Мониторы Новостная лента Оптимизация Ошибки Разработка сайта Часы Юзабилити оптимизация ошибки

Наглядное объяснение принципа объединения таблиц в MySQL

Статья от 11 октября 2007 года.    Теги: MySQL Jeff Atwood


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

В статье рассмотрены принципы объединений таблиц и, для наглядности, они изображены на круговых диаграммах (диаграммах Венна). Первоисточником статьи является статья Джеффа Этвуда, но я (Kreker, в смысле), её перевел, подрихтовал и добавил неописанные типы объединений для MySQL (изначально статья была для другой СУБД).

Предупреждение: в статье в условии объединения таблиц используется как 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 существуют следующие операторы объединения:

INNER JOIN

 — INNER JOIN производит выборку записей, которые только существуют в TableA и TableB одновременно.
 — CROSS JOIN — это эквивалент INNER JOIN.
 — INNER JOIN можно заменить условием объединения в WHERE.

Запрос:
SELECT * FROM `TableA`

INNER JOIN `TableB`

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


FULL OUTER JOIN*

*Не доступно в MySQL

FULL OUTER JOIN производит выборку всех записей из TableA и TableB, вне зависимости есть ли соответствующая запись в соседней таблице. Если таковой нет, то недостающая сторона будет содержать пустой указатель и результатом будет выводится NULL.



Запрос:
SELECT * FROM `TableA`

FULL OUTER 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
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader
Диаграмма FULL OUTER JOIN


В MySQL нечто похожее можно получить запросом:
SELECT `TableA`.*, `TableB`.* FROM `TableA`

LEFT JOIN `TableB` USING (`name`)

UNION SELECT `TableA`.*, `TableB`.* FROM `TableB`

LEFT JOIN `TableA`

USING (`name`)

WHERE `TableA`.`name` IS NULL


Результат:

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



Чтобы произвести выборку уникальных записей из двух таблиц (значения одной таблицы отсутствуют в другой), мы воспользуемся тем же FULL OUTER JOIN, указав, что NULL может быть как в результате одной таблицы, так и в результате другой.


Запрос:
SELECT * FROM `TableA`

FULL OUTER JOIN `TableB`

ON `TableA`.`name` = `TableB`.`name`

WHERE `TableA`.`id` IS NULL OR `TableB`.`id` IS NULL


Результат:

TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader
Диаграмма FULL OUTER JOIN (уникальные записи из двух таблиц)


В MySQL нечто похожее можно получить запросом:
SELECT `TableA`.*, `TableB`.* FROM

TableA LEFT JOIN `TableB`

USING (`name`)

WHERE `TableB`.`name` IS NULL

UNION SELECT `TableA`.*, `TableB`.* FROM `TableB `

LEFT JOIN `TableA` USING (`name`)

WHERE `TableA`.`name` IS NULL


Результат:

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


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

Суть этой конструкции в том, что база сама выбирает, по каким столбцам сравнивать и объединять таблицы. А выбор этот падает на столбцы с одинаковыми именами. В этом кроется засада &mdash база может выбрать совершенно не те столбцы для объединения и запрос будет работать совершенно не так, как вы предполагали.


Запрос:
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


При копировании статьи необходимо указывать ссылку на сайт, а так же имена авторов - Кича Владимир и Jeff Atwood

Ссылка на оригинал - www.codinghorror.com
Ninja  12.04.2011 в 10:15
This is gay =]
Аноним  17.04.2011 в 00:12
Спасибо большое авторам! Мне очень помогла эта статья!
Nachtvolk  06.06.2011 в 23:30
Перебрал кучу статей по JOIN`ам (предпочитаю одну и ту же статью не перечитывать, а каждый раз читать новую по этой же теме). Здесь самая классная идея по объяснению разницы JOIN`ов - пересекающимися кругами и/или окружностями.
Анонимус  07.06.2011 в 22:44
Огромное спасибо автору и переводчику. Забью статью в закладки, поэкспериментирую чуть по-позже.
Аноним  14.07.2011 в 15:41
Молодцы, хорошая статья, действительно всё понятно.
Денис  19.08.2011 в 06:25
Статья просто супер, все наглядно и это большой плюс!!!
Pirate  21.09.2011 в 18:25
спасибо за статью! она помогла мне разобраться в этом вопросе.
Аноним  26.09.2011 в 17:48
Супер, спасибо
Аноним  11.11.2011 в 13:51
теперь все ясно, огромное спасибо
insider  21.01.2012 в 04:06
Круть! Автор, так держать!
bobylin  02.03.2012 в 11:56
а как объединить две таблицы разных БД MySQL на одном хосте в том виде, в каком они есть

P.S. поправочка: ID записей могут совпадать, тогда ..?
Аноним  03.08.2012 в 08:06
слабовато.
очень много текста чтобы обяснить принцип джоина, а факт дублирования записей левой и/или правой таблиц вскольз упомянут вконце и то в неявном виде
ZeroXor  22.11.2012 в 13:07
Наверное, самое понятное объяснение объединений таблиц в MySQL.
Михаил Готовский  11.12.2012 в 12:53
Спасибо, не знал о существовании NATURAL JOIN =)
Аноним  20.01.2013 в 23:13
Наконец то разобрался с джоинами, и узнал пару новых. Спасибо.
Zupaman  19.04.2013 в 12:34
спасибо, очень доступно
Анонист  15.06.2013 в 12:24
Спасибо, подрочил
Антоним  15.06.2013 в 14:54
Чтоб не мотать вверх-вниз страницу в поисках таблицы с персонажами, присвоил ей класс tables и добавил его в css
.tables{position: fixed;
top: 200px;
right: 100px;}
Попробуй, удобно получилось!
Аноним  29.07.2013 в 19:32
Спасибо, очень помогло
S.Chervach  21.02.2014 в 08:14
Спасибо. Стырил и разместил у себя с обратной ссылкой. На всякий случай - вдруг закроете сайт, а статья ценная получилась.
Аноним  04.09.2014 в 08:31
цуйуйцуйуцу
Аноним  04.09.2014 в 08:31
ЗАЕБИСЬ
Andrei  21.04.2015 в 16:56
Наканец нашол то што искал.
Спасибо...
Оставить сообщение






Любое копирование должно сопровождаться ссылкой на сайт.
Если вам что-то не понравилось — сообщайте.
Кича Владимир
x
Мне не нравится этот сайт, удалить его