JOIN
JOIN - означает "объединять", то есть собирать из нескольких кусочков единое целое. В базе данных MySQL такими "кусочками" служат столбцы таблиц, которые можно объединять при выборке.
Объединения позволяют извлекать данные из нескольких таблиц без создания временных таблиц и за один запрос.
Таблицы "товары" и "описания"
Таблица с наименованием товаров (я назову её nomenclature
), будет хранить номер товара (id
) и краткое название (name
).
Содержание таблицы с описанием товаров (пусть будет description
):
Исходные данные определены, добавлю ещё Несколько слов о структуре базы. Таблица nomenclature
содержит перечень всех товаров, которые есть в базе. Таблица описаний description
, напротив, содержит лишь неполный перечень описаний для товаров, которые необязательно присутствуют в базе. Чтобы однозначно привязать описание к товару, в таблицах присутствует столбец id
, который содержит уникальный номер товара. В обеих таблицах id
является первичным ключом, что соответствует связи один-к-одному.
Выборка
Таблицы определены и заполнены, настало время делать выборку. Но если приглядеться к данным, которые находятся в таблицах, то можно заметить, что они не в полной мере соответствуют друг другу. Так в таблице nomenclature
присутствует товар под номером 2 (Табуретка), для которого нет описания. И в таблице description
присутствует описание для товара номер 5 (Зелёная машинка), которого нет в таблице номенклатур .
В зависимости от требований к результату, MySQL позволяет производить три разных типа объединения:
INNER JOIN
(CROSS JOIN
) - внутреннее (перекрёстное) объединениеLEFT JOIN
- левостороннее внешнее объединениеRIGHT JOIN
- правостороннее внешнее объединение
INNER JOIN (CROSS JOIN) - внутреннее (перекрёстное) объединение
Этот тип объединения позволяет извлекать строки, которые обязательно присутствуют во всех объединяемых таблицах.
В простейшем случае (без указания условий отбора), выборка вернёт т.н. декартово произведение, в котором каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы:
Как правило, декартово произведение таблиц требуется нечасто, чаще требуется выбрать только те записи, которые сопоставлены друг другу. Сделать это можно, если задать условие отбора, используя ON
или USING
.
Запрос вернул только две записи, поскольку именно столько строк имеют одинаковые идентификаторы в обеих таблицах.
Использование USING
обусловлено тем, что в таблицах ключевой столбец имеет одно и тоже имя - id
. В противном случае, надо было бы использовать ON
.
Помимо конструкции INNER JOIN
внутреннее объединение можно объявить так же через CROSS JOIN
, JOIN
и запятую в объявлении FROM
. Следующие четыре запроса вернут одинаковый результат:
Код - Разные формы объявления внутреннего объединения |
Если объединять таблицы через запятую, то нельзя использовать конструкции ON
и USING
, поэтому условие может быть задано только в конструкции WHERE
. Например, это может выглядеть так:
Поскольку поле id
не является однозначным, приходится доуточнять в каком контексте оно используется через указание имени таблицы.
И так, внутреннее объединение можно задать следующими способами:
Результатом будет декартово произведение всех таблиц, на которое можно накладывать условия выборки, используя ON
, USING
и WHERE
.
LEFT JOIN - Левостороннее внешнее объединение
Левосторонние объединения позволяют извлекать данные из таблицы, дополняя их по возможности данными из другой таблицы.
К примеру, чтобы получить полный список наименований товаров вместе с их описанием, нужно выполнить следующий запрос:
Поскольку для наименования Табуретка
в таблице описаний нет подходящей записи, то в поле description
подставился NULL
. Это справедливо для всех записей, у которых нет подходящей пары.
Если дополнить предыдущий запрос условием на проверку несуществования описания, то можно получить список записей, которые не имеют пары в таблице описаний:
По сути это и есть основное назначение внешних запросов - показывать расхождение данных двух таблиц.
Кроме того, при таком объединении обязательным является условие, которое задаётся через ON
или USING
. Без него запрос будет выдавать ошибку.
RIGHT JOIN - Правостороннее внешнее объединение
Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от конструкции JOIN
, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией.
Как видно, теперь уже поле name
содержит нулевые значения. Также поменялся и порядок расположения столбцов.
Однако, во всех случаях использования правосторонних объединений, запрос можно переписать, используя левостороннее объединение, просто поменяв таблицы местами, и наоборот. Следующие два запроса равнозначны:
Многотабличные запросы
Используя JOIN, можно объединять не только две таблицы, как было описано выше, но и гораздо больше. В MySQL 5.0 на сегодняшний день можно объединить вплоть до 61 таблицы. Помимо объединений разных таблиц, MySQL позволяет объединять таблицу саму с собой. Однако, в любом случае необходимо следить за именами столбцов и таблиц, если они будут неоднозначны, то запрос не будет выполнен.
Так, если таблицу просто объединить саму на себя, то возникнет конфликт имён и запрос не выполнится.
Обойти конфликт имён позволяет использование синонимов (alias
) для имён таблиц и столбцов. В следующем примере внутреннее объединение будет работать успешнее:
MySQL не накладывает ограничений на использование разных типов объединений в одном запросе, поэтому можно формировать довольно сложные конструкции:
Помимо выборок использовать объединения можно также и в запросах UPDATE
и DELETE
Так, следующие три запроса проделывают одинаковую работу:
Таким же образом работают и многтабличные удаления
Следует помнить, что при использовании многотабличных запросов на удаление или обновление данных, нельзя включать в запрос конструкции ORDER BY
и LIMIT
. Впрочем, это ограничение очень эффективно обходится при помощи временных таблиц, просто, надо это учитывать при модификации однотабличных запросов.
Last updated