MySQL немного о 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 не является однозначным, приходится доуточнять в каком контексте оно используется через указание имени таблицы.
И так, внутреннее объединение можно задать следующими способами:
Код – способы объявления внутреннего объединения таблиц SELECT * FROM Таблица1, Таблица2[, Таблица3, …] [WHERE Условие1 [Условие2 …] SELECT * FROM Таблица1 [INNER | CROSS] JOIN Таблица2 [(ON Условие1 [Условие2 …]) | (USING(Поле))]
Результатом будет декартово произведение всех таблиц, на которое можно накладывать условия выборки, используя ON, USING и WHERE.
LEFT JOIN – Левостороннее внешнее объединенея
Левосторонние объединения позволяют извлекать данные из таблицы, дополняя их по возможности данными из другой таблицы.
К примеру, чтобы получить полный список наименований товаров вместе с их описанием, нужно выполнить следующий запрос:
Поскольку для наименования Табуретка в таблице описаний нет подходящей записи, то в поле description подставился NULL. Это справедливо для всех записей, у которых нет подходящей пары.
Если дополнить предыдущий запрос условием на проверку несуществования описания, то можно получить список записей, которые не имеют пары в таблице описаний:
По сути это и есть основное назначение внешних запросов – показывать расхождение данных двух таблиц.
Кроме того, при таком объединении обязательным является условие, которое задаётся через ON или USING. Без него запрос будет выдавать ошибку.
RIGHT JOIN – Правостороннее внешнее объединение
Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от констркуции JOIN, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией.
Как видно, теперь уже поле name содержит нулевые значения. Также поменялся и порядок расположения столбцов.
Однако, во всех случаях использования правосторонних объединений, запрос можно переписать, используя левостороннее объединение, просто поменяв таблицы местами, и наоборот. Следующие два запроса равнозначны:
Код – запросы на выборку
| 1 2 |
SELECT * FROM nomenclature LEFT JOIN description USING(id); SELECT * FROM description RIGHT JOIN nomenclature USING(id); |
Многотабличные запросы
Испольузя JOIN, можно объединять не только две таблицы, как было описано выше, но и гораздо больше. В MySQL 5.0 на сегодняшний день можно объединить вплоть до 61 таблицы. Помимо объединений разных таблиц, MySQL позволяет объединять таблицу саму с собой. Однако, в любом случае необходимо следить за именами столбцов и таблиц, если они будут неоднозначны, то запрос не будет выполнен.
Так, если таблицу просто объединить саму на себя, то возникнет конфликт имён и запрос не выполнится.
| Код – Объединение таблицы саму на себя |
| mysql> SELECT * FROM nomenclature JOIN nomenclature; ERROR 1066 (42000): Not unique table/alias: ‘nomenclature’ |
Обойти конфликт имён позволяет использование синонимов (alias) для имён таблиц и столбцов. В следующем примере внутреннее объединение будет работать успешнее:
Код – Объединение таблицы саму на себя
MySQL не накладывает ограничений на использование разных типов объединений в одном запросе, поэтому можно формировать довольно сложные конструкции:
Помимо выборок использовать объединения можно также и в запросах UPDATE и DELETE
Так, следующие три запроса проделывают одинаковую работу:
Следует помнить, что при использовании многотабличных запросов на удаление или обновление данных, нельзя включать в запрос конструкции ORDER BY и LIMIT. Впрочем, это ограничение очень эффективно обходится при помощи временных таблиц, просто, надо это учитывать при модификации однотабличных запросов.
Примеры использования многотабличных запросов
Приведу несколько примеров из своей практики, которые реально используются.
В данном случае идёт выборка первых 58 клиентов из таблицы договоров с привязкой баланса на текущий день, у которых в имени договора содержится “123” и сортировкой по имени (номеру) договора. Поскольку список договоров может не совпадать со списком балансов, то используется левостороннее объединение. Помимо этого используется SQL_CALC_FOUND_ROWS для подсчёта общего количества найденных строк, чтобы организовать страничную навигацию.
Данный запрос выводит список платежей с указанием типа платежа и номера договора с сортировкой по дате и номеру договора. Так же предустморен постраничный вывод списка.
В этом примере используется одно внутреннее объединение для привязки ip-адресов к договорам, и три внешних левосторонних для получения дополнительной информации. Несмотря на внушительный размер, запрос выполняется достаточно быстро, поскольку объединения идут по первичным ключам. Так, как результатом должен быть список из договоров и привязанных к ним ip-адресов, то используется внутреннее объединение.
Использовать более экзотические конструкции на практике приходится нечасто, поскольку с ростом количества объединяемых таблиц резко падает производительность запросов. Сложные задачи, которые требуют серьёзных вычислений, такие, как, например, подсчёт баллансов всех клиентов, решаются с помощью временных таблиц и довольно простых запросов.
К чему всё это?
Данное описание лишь первый шаг в подготовке к сдаче второго экзамена для получения сертификата MySQL Developer.
Статья родилась по мотивам 12 главы “MySQL 5.0 Certification Study Guide”, хотя и не является её точным или полным переводом.