JOIN, в переводе на великий и могучий, означает “объединять”, то есть собирать из нескольких кусочков единое целое. В базе данных MySQL такими “кусочками” служат столбцы таблиц, которые можно объединять при выборке.
Объединения позволяют извлекать данные из нескольких таблиц без создания временных таблиц и за один запрос.
Таблицы “товары” и “описания”
Таблица с наименованием товаров (я назову её nomenclature), будет хранить номер товара (id) и краткое название (name).
Код - Описание таблицы nomenclature
mysql> SELECT * FROM nomenclature;
+----+-----------+
| id | name |
+----+-----------+
| 1 | Книга |
| 2 | Табуретка |
| 3 | Карандаш |
+----+-----------+
3 rows in set (0.00 sec)Содержание таблицы с описанием товаров (пусть будет description):
Код - Содержимое таблицы description
mysql> SELECT * FROM description;
+----+---------------------+
| id | description |
+----+---------------------+
| 1 | Замечательная книга |
| 3 | Красный карандаш |
| 5 | Зелёная машинка |
+----+---------------------+
3 rows in set (0.00 sec)Исходные данные определены, дабавлю ещё Несколько слов о структуре базы. Таблица nomenclature содержит перечень всех товаров, которые есть в базе. Таблица описаний description, напротив, содержит лишь неполный перечень описаний для товаров, которые необязательно присутсвуют в базе. Чтобы однозначно привязать описание к товару, в таблицах присутвует столбец id, который содержит уникальный номер товара. В обеих таблицах id является первичным ключём, что соотвествует связи один-к-одному.
Выборка
Таблицы определены и заполнены, настало время делать выборку. Но если приглядеться к данным, которые находятся в таблицах, то можно заметить, что они не в полной мере соответствуют друг другу. Так в таблице nomenclature присутсвует товар под номером 2 (Табуретка), для которого нет описания. И в таблице description присутсвует описание для товара номер 5 (Зелёная машинка), которого нет в таблице номенклатур .
В зависимости от требований к результату, MySQL позволяет производить три разных типа объединения:
- INNER JOIN (CROSS JOIN) – внутреннее (перекрёстное) объединение
- LEFT JOIN – левостороннее внешнее объединение
- RIGHT JOIN – правостороннее внешнее объединение
INNER JOIN (CROSS JOIN) – внутреннее (перекрёстное) объединение
Этот тип объединения позволяет извлекать строки, которые обязательно присутсвуют во всех объединяемых таблицах.
В простейшем случае (без указания условий отбора), выборка вернёт т.н. декартово произведение, в котором каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы:
-- Код - Внутреннее объединение таблиц (декатрово произведение)
mysql> SELECT * FROM nomenclature INNER JOIN description;
+----+-----------+----+---------------------+
| id | name | id | description |
+----+-----------+----+---------------------+
| 1 | Книга | 1 | Замечательная книга |
| 2 | Табуретка | 1 | Замечательная книга |
| 3 | Карандаш | 1 | Замечательная книга |
| 1 | Книга | 3 | Красный карандаш |
| 2 | Табуретка | 3 | Красный карандаш |
| 3 | Карандаш | 3 | Красный карандаш |
| 1 | Книга | 5 | Зелёная машинка |
| 2 | Табуретка | 5 | Зелёная машинка |
| 3 | Карандаш | 5 | Зелёная машинка |
+----+-----------+----+---------------------+
9 rows in set (0.00 sec)Как правило, декартово произведение таблиц требуется нечасто, чаще требуется выбрать только те записи, которые сопоставлены друг другу. Сделать это можно, если задать условие отбора, используя <>ON или <>USING.
-- Код - внутреннее объединение
mysql> SELECT * FROM nomenclature INNER JOIN description using(id);
+----+----------+---------------------+
| id | name | description |
+----+----------+---------------------+
| 1 | Книга | Замечательная книга |
| 3 | Карандаш | Красный карандаш |
+----+----------+---------------------+
2 rows in set (0.01 sec)Запрос вернул только две записи, поскольку именно столько строк имеют одинаковые идентификаторы в обеих таблицах.
Использование USING обусловлено тем, что в таблицах ключевой столбец имеет одно и тоже имя – id. В противном случае, надо было бы использовать ON.
Помимо конструкции INNER JOIN внутреннее объединение можно объявить так же через CROSS JOIN, JOIN и запятую в объявлении FROM. Следующие четыре запроса вернут одинаковый результат:
-- Код - Разные формы объявления внутреннего объединения SELECT * FROM nomenclature INNER JOIN description; SELECT * FROM nomenclature CROSS JOIN description; SELECT * FROM nomenclature JOIN description; SELECT * FROM nomenclature, description;
Если объединять таблицы через запятую, то нельзя использовать конструкции ON и USING, поэтому условие может быть задано только в конструкции WHERE. Например, это может выглядеть так:
Код - пример использования внутреннего объединения
mysql> SELECT * FROM nomenclature, description WHERE nomenclature.id = description.id;
+----+----------+----+---------------------+
| id | name | id | description |
+----+----------+----+---------------------+
| 1 | Книга | 1 | Замечательная книга |
| 3 | Карандаш | 3 | Красный карандаш |
+----+----------+----+---------------------+
2 rows in set (0.00 sec)Поскольку поле 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 – Левостороннее внешнее объединенея
Левосторонние объединения позволяют извлекать данные из таблицы, дополняя их по возможности данными из другой таблицы.
К примеру, чтобы получить полный список наименований товаров вместе с их описанием, нужно выполнить следующий запрос:
Код - внешнее левостороннее объединение
mysql> SELECT * FROM nomenclature LEFT JOIN description USING(id);
+----+-----------+---------------------+
| id | name | description |
+----+-----------+---------------------+
| 1 | Книга | Замечательная книга |
| 2 | Табуретка | NULL |
| 3 | Карандаш | Красный карандаш |
+----+-----------+---------------------+
3 rows in set (0.01 sec)Поскольку для наименования Табуретка в таблице описаний нет подходящей записи, то в поле description подставился NULL. Это справедливо для всех записей, у которых нет подходящей пары.
Если дополнить предыдущий запрос условием на проверку несуществования описания, то можно получить список записей, которые не имеют пары в таблице описаний:
Код - выборка наименований без описаний
mysql> SELECT id, name FROM nomenclature LEFT JOIN description
USING(id) WHERE description IS NULL;
+----+-----------+
| id | name |
+----+-----------+
| 2 | Табуретка |
+----+-----------+
1 row in set (0.00 sec)
По сути это и есть основное назначение внешних запросов – показывать расхождение данных двух таблиц.
Кроме того, при таком объединении обязательным является условие, которое задаётся через ON или USING. Без него запрос будет выдавать ошибку.
RIGHT JOIN – Правостороннее внешнее объединение
Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от констркуции JOIN, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией.
mysql> SELECT * FROM nomenclature RIGHT JOIN description USING(id); +----+---------------------+----------+ | id | description | name | +----+---------------------+----------+ | 1 | Замечательная книга | Книга | | 3 | Красный карандаш | Карандаш | | 5 | Зелёная машинка | NULL | +----+---------------------+----------+ 3 rows in set (0.00 sec)
Как видно, теперь уже поле 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
Так, следующие три запроса проделывают одинаковую работу:
-- Код - Многотаблицные обновления
mysql> UPDATE nomenclature AS t1, nomenclature AS t2 SET t1.id = t2.id WHERE t1.id = t2.id;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> UPDATE nomenclature AS t1 JOIN nomenclature AS t2 SET t1.id = t2.id WHERE t1.id = t2.id;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> UPDATE nomenclature AS t1 JOIN nomenclature AS t2 USING(id) SET t1.id = t2.id;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
-- Код - Многотабличные удаления
mysql> DELETE t1 FROM nomenclature AS t1 JOIN nomenclature AS t2 USING(id) WHERE t2.id > 10;
Query OK, 0 rows affected (0.02 sec)Следует помнить, что при использовании многотабличных запросов на удаление или обновление данных, нельзя включать в запрос конструкции ORDER BY и LIMIT. Впрочем, это ограничение очень эффективно обходится при помощи временных таблиц, просто, надо это учитывать при модификации однотабличных запросов.
Примеры использования многотабличных запросов
Приведу несколько примеров из своей практики, которые реально используются.
-- Код - Пример #1 многотабличного запроса
SELECT SQL_CALC_FOUND_ROWS
dgs.dogovor_id,
dgs.dogovor_name,
dgs.abonent_name,
dgs.abonent_type,
dgs.address_fiz,
dgs.date_conclusion,
dgs.date_annulment,
dgs.threshold,
ubc.usum
FROM
billing_dogovors dgs
LEFT JOIN
billing_users_balance ubc
ON
ubc.udate = CURDATE()
AND
dgs.dogovor_id = ubc.dogovor_id
WHERE
dgs.dogovor_name LIKE "%123%"
ORDER BY
dgs.dogovor_name ASC
LIMIT
0, 58В данном случае идёт выборка первых 58 клиентов из таблицы договоров с привязкой баланса на текущий день, у которых в имени договора содержится “123” и сортировкой по имени (номеру) договора. Поскольку список договоров может не совпадать со списком балансов, то используется левостороннее объединение. Помимо этого используется SQL_CALC_FOUND_ROWS для подсчёта общего количества найденных строк, чтобы организовать страничную навигацию.
-- Код - Пример #2 реального многотабличного запроса
SELECT SQL_CALC_FOUND_ROWS
pft.udate,
dgs.dogovor_name,
pft.usum,
ptt.type_name
FROM
billing_profit pft
LEFT JOIN
billing_dogovors dgs
USING( dogovor_id )
LEFT JOIN
billing_profit_types ptt
ON
pft.profit_type = ptt.type_id
WHERE
pft.udate > CURDATE() - INTERVAL 7 DAY
ORDER BY
pft.udate DESC,
dgs.dogovor_name ASC
LIMIT
0, 30;Данный запрос выводит список платежей с указанием типа платежа и номера договора с сортировкой по дате и номеру договора. Так же предустморен постраничный вывод списка.
-- Код - Пример #3 реального моноготабличного запроса
SELECT SQL_CALC_FOUND_ROWS
ips.ip,
ips.segment_id,
ips.gray_ip,
ips.ip_mac,
ips.ip_status,
ips.ip_type,
ips.blocked_reason,
ips.blocked_time,
ips.comment,
rts.router_name,
dgs.dogovor_name,
ipt.type_name,
ubc.usum
FROM
billing_ips ips
LEFT JOIN
billing_routers rts
ON
ips.segment_id = rts.router_id
LEFT JOIN
t
ON
ips.ip = t.ip
LEFT JOIN
billing_ip_types ipt
ON
ips.ip_type = ipt.type_id,
billing_dogovors dgs
LEFT JOIN
billing_users_balance ubc
ON
ubc.udate = CURDATE()
AND
dgs.dogovor_id = ubc.dogovor_id
WHERE
INET_NTOA(ips.ip) LIKE "%123%"
AND
dgs.dogovor_name LIKE "%123%"
AND
dgs.dogovor_id = t.dogovor_id
ORDER BY ips.ip ASC
LIMIT
0, 80В этом примере используется одно внутреннее объединение для привязки ip-адресов к договорам, и три внешних левосторонних для получения дополнительной информации. Несмотря на внушительный размер, запрос выполняется достаточно быстро, поскольку объединения идут по первичным ключам. Так, как результатом должен быть список из договоров и привязанных к ним ip-адресов, то используется внутреннее объединение.
Использовать более экзотические конструкции на практике приходится нечасто, поскольку с ростом количества объединяемых таблиц резко падает производительность запросов. Сложные задачи, которые требуют серьёзных вычислений, такие, как, например, подсчёт баллансов всех клиентов, решаются с помощью временных таблиц и довольно простых запросов.
К чему всё это?
Данное описание лишь первый шаг в подготовке к сдаче второго экзамена для получения сертификата MySQL Developer.
Статья родилась по мотивам 12 главы “MySQL 5.0 Certification Study Guide”, хотя и не является её точным или полным переводом.
Источник: сайт
