Апрель 7 2019

MySQL немного о JOIN’ах

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 позволяет производить три разных типа объединения:

  1. INNER JOIN (CROSS JOIN) – внутреннее (перекрёстное) объединение
  2. LEFT JOIN – левостороннее внешнее объединение
  3. 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”, хотя и не является её точным или полным переводом.

Источник: сайт


Метки:
Copyright 2019. All rights reserved.

Опубликовано Апрель 7, 2019 master в категории "SQL