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

almix — 10 июня 2011 (обновлял 24 августа 2011 в 15:26)
Комментарии (2) Категории: MySQL версия для печати

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 не является однозначным, приходится доуточнять в каком контексте оно используется через указание имени таблицы.

И так, внутреннее объединение можно задать следующими способами:

Код - способы объявления внутреннего объединения таблиц

1
2

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> SELECT * FROM nomenclature JOIN nomenclature AS t2;
+----+-----------+----+-----------+

| id | name      | id | name      |
+----+-----------+----+-----------+
|  1 | Книга     |  1 | Книга     |
|  2 | Табуретка |  1 | Книга     |
|  3 | Карандаш  |  1 | Книга     |
|  1 | Книга     |  2 | Табуретка |
|  2 | Табуретка |  2 | Табуретка |
|  3 | Карандаш  |  2 | Табуретка |
|  1 | Книга     |  3 | Карандаш  |
|  2 | Табуретка |  3 | Карандаш  |
|  3 | Карандаш  |  3 | Карандаш  |
+----+-----------+----+-----------+

9 rows in set (0.00 sec)

MySQL не накладывает ограничений на использование разных типов объединений в одном запросе, поэтому можно формировать довольно сложные конструкции:

Код - Пример сложного объединения таблиц

mysql> SELECT * FROM nomenclature AS t1 JOIN nomenclature AS t2 LEFT JOIN nomenclature AS t3 ON t1.id = t3.id AND t2.id = t1.id;
+----+-----------+----+-----------+------+-----------+

| id | name      | id | name      | id   | name      |
+----+-----------+----+-----------+------+-----------+

|  1 | Книга     |  1 | Книга     |    1 | Книга     |
|  2 | Табуретка |  1 | Книга     | NULL | NULL      |
|  3 | Карандаш  |  1 | Книга     | NULL | NULL      |
|  1 | Книга     |  2 | Табуретка | NULL | NULL      |
|  2 | Табуретка |  2 | Табуретка |    2 | Табуретка |
|  3 | Карандаш  |  2 | Табуретка | NULL | NULL      |
|  1 | Книга     |  3 | Карандаш  | NULL | NULL      |
|  2 | Табуретка |  3 | Карандаш  | NULL | NULL      |
|  3 | Карандаш  |  3 | Карандаш  |    3 | Карандаш  |
+----+-----------+----+-----------+------+-----------+

9 rows in set (0.00 sec)

Помимо выборок использовать объединения можно также и в запросах 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", хотя и не является её точным или полным переводом.

Автор - Антон Прибора

Источник: anton-pribora.ru/articles/mysql/mysql-join/

almix
Разработчик Loco, автор статей по веб-разработке на Yii, CodeIgniter, MODx и прочих инструментах. Создатель Team Sense.

Вы можете почитать все статьи от almix'а.

Другие статьи по этой теме:

Комментарии (2) Подпишитесь на

RSS комментариев к этой статье.

2 комментариев

#317

vladymyr говорит:

March 2, 2012 at 11:41 am

можно ли объединить 2 таблицы разных БД на одном хосте?

#318

Саша Loco говорит:

March 2, 2012 at 03:04 pm

Да, если обе БД на одном сервере находятся:

SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a inner join databaseB.dbo.TableB b ON a.userID=b.userID

(здесь жирным - имя_БД.пользователь.имя_таблицы)

или пусть один пользователь будет и у него GRANT привилегии к обоим таблицам, тогда так:

$query = "SELECT a.field1, b.field1 from database1.table1 as a, database2.table1 as b WHERE database1.table1.field2='' AND ..... ";

Пн	Вт	Ср	Чт	Пт	Сб	Вс
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

КОДИНГ

ДИЗАЙН

CMS и CMF

SEO И ПРОДВИЖЕНИЕ

ЖИЗНЬ

СОФТ