Наглядное объяснение принципа объединения таблиц в 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 производит выборку записей, которые только существуют в 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 |
|
|
*Не доступно в 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 |
|
|
В 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 |
|
|
В 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 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 |
|
|
Чтобы произвести выборку записей из таблицы 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 |
|
|
RIGHT JOIN выполняет те же самые функции, что и LEFT JOIN, за исключением того, что правая таблица будет прочитана первой. Таким образом, если в запросах из предыдущей главы LEFT заменить на RIGHT, то таблица результатов, грубо говоря, отразится по вертикали. То есть, в результате вместо значений TableA будут записи TableB и наоборот.
Суть этой конструкции в том, что база сама выбирает, по каким столбцам сравнивать и объединять таблицы. А выбор этот падает на столбцы с одинаковыми именами. В этом кроется засада &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 присутствует в обеих таблицах, то она и будет выведена.
Запрос:
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 выполняет те же функции, что и обычный 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
This is gay =]
Спасибо большое авторам! Мне очень помогла эта статья!
Перебрал кучу статей по JOIN`ам (предпочитаю одну и ту же статью не перечитывать, а каждый раз читать новую по этой же теме). Здесь самая классная идея по объяснению разницы JOIN`ов - пересекающимися кругами и/или окружностями.
Огромное спасибо автору и переводчику. Забью статью в закладки, поэкспериментирую чуть по-позже.
Молодцы, хорошая статья, действительно всё понятно.
Статья просто супер, все наглядно и это большой плюс!!!
спасибо за статью! она помогла мне разобраться в этом вопросе.
Супер, спасибо
теперь все ясно, огромное спасибо
Круть! Автор, так держать!
|