Mysql outer join error

1. Доступные статьи

2. MySQL

3. Поговорим о JOIN

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. Следующие четыре запроса вернут одинаковый результат:

Код - Разные формы объявления внутреннего объединения

1
2
3
4

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 многотабличного запроса

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

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 реального многотабличного запроса

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

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 реального моноготабличного запроса

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

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», хотя и не является её точным или полным переводом.

Хорошая статья, мне понравилась. Оставлю отзыв!

Многотабличные запросы

В предыдущих статьях описывалась работа только с одной таблицей базы данных. В реальности же очень часто приходится делать выборку из нескольких таблиц, каким-то образом объединяя их. В данной статье вы узнаете основные способы соединения таблиц.

Общая структура многотабличного запроса

SELECT поля_таблиц
FROM таблица_1 
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2 
    ON условие_соединения
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n 
    ON условие_соединения]

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

SELECT поля_таблиц
FROM таблица_1 
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2[ JOIN таблица_n] 
    ON условие_соединения [AND условие_соединения]

В большинстве случаев условием соединения является равенство столбцов таблиц (таблица_1.поле = таблица_2.поле), однако точно так же можно использовать и другие операторы сравнения.

Соединение бывает внутренним (INNER) или внешним (OUTER), при этом внешнее соединение делится на левое (LEFT), правое (RIGHT) и полное (FULL).

INNER JOIN

По умолчанию, если не указаны какие-либо параметры, JOIN выполняется как INNER JOIN, то есть как внутреннее (перекрёстное) соединение таблиц.

Например, объединим таблицы покупок (Payments) и членов семьи (FamilyMembers) таким образом, чтобы дополнить каждую покупку данными о том, кто её совершил.

Данные в таблице Payments:

Данные в таблице FamilyMembers:

Для того, чтобы решить поставленную задачу выполним запрос, который объединяет поля строки из одной таблицы с полями другой, если выполняется условие, что покупатель товара (family_member) совпадает с идентификатором члена семьи (member_id):

SELECT *
FROM Payments 
    JOIN FamilyMembers ON family_member = member_id;

В результате вы можете видеть, что каждая строка из таблицы Payments дополнилась данными о члене семьи, который совершил покупку. Обратите внимание на поля family_member и member_id — они одинаковы, что и было отражено в запросе.

Использование WHERE для соединения таблиц

Для внутреннего соединения таблиц также можно использовать оператор WHERE. Например, вышеприведённый запрос, написанный с помощью INNER JOIN, будет выглядеть так:

SELECT *
FROM Payments, FamilyMembers
WHERE family_member = member_id;

OUTER JOIN

Внешнее соединение может быть трёх типов: левое (LEFT), правое (RIGHT) и полное (FULL). По умолчанию оно является полным.

Главным отличием внешнего соединения от внутреннего является то, что оно обязательно возвращает все строки одной (LEFT, RIGHT) или двух таблиц (FULL).

Внешнее левое соединение (LEFT OUTER JOIN)

Соединение, которое возвращает все значения из левой таблицы, соединённые с соответствующими значениями из правой таблицы если они удовлетворяют условию соединения, или заменяет их на NULL в обратном случае.

Для примера получим из базы данных расписание звонков объединённых с соответствующими занятиями в расписании занятий:

SELECT *
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

Данные в таблице Timepair (расписание звонков):

Данные в таблице Schedule (расписание занятий):

В выборку попали все строки из левой таблицы, дополненные данными о занятиях. Примечательно, что в конце таблицы есть строки с полями, заполненными NULL. Это те строки, для которых не нашлось соответствующих занятий, однако они присутствуют в левой таблице, поэтому тоже были выведены.

Внешнее правое соединение (RIGHT OUTER JOIN)

Соединение, которое возвращает все значения из правой таблицы, соединённые с соответствующими значениями из левой таблицы если они удовлетворяют условию соединения, или заменяет их на NULL в обратном случае.

Внешнее полное соединение (FULL OUTER JOIN)

Соединение, которое выполняет внутреннее соединение записей и дополняет их левым внешним соединением и правым внешним соединением.

Алгоритм работы полного соединения:

1. Формируется таблица на основе внутреннего соединения (INNER JOIN).
2. В таблицу добавляются значения не вошедшие в результат формирования из левой таблицы (LEFT OUTER JOIN).
3. В таблицу добавляются значения не вошедшие в результат формирования из правой таблицы (RIGHT OUTER JOIN).

SELECT * 
FROM левая_таблица 
LEFT JOIN правая_таблица 
    ON правая_таблица.ключ = левая_таблица.ключ

UNION ALL

SELECT * 
FROM левая_таблица 
RIGHT JOIN правая_таблица 
    ON правая_таблица.ключ = левая_таблица.ключ
WHERE левая_таблица.key IS NULL

Базовые запросы для разных вариантов объединения таблиц

Схема	Запрос с JOIN
	Получение всех данных из левой таблицы, соединённых с соответствующими данными из правой: MySQL SELECT поля_таблиц FROM левая_таблица LEFT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ
	Получение всех данных из правой таблицы, соединённых с соответствующими данными из левой: MySQL SELECT поля_таблиц FROM левая_таблица RIGHT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ
	Получение данных, относящихся только к левой таблице: MySQL SELECT поля_таблиц FROM левая_таблица LEFT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE правая_таблица.ключ IS NULL
	Получение данных, относящихся только к правой таблице: MySQL SELECT поля_таблиц FROM левая_таблица RIGHT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE левая_таблица.ключ IS NULL
	Получение данных, относящихся как к левой, так и к правой таблице: MySQL SELECT поля_таблиц FROM левая_таблица INNER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ
	Получение всех данных, относящихся к левой и правой таблицам, а также их внутреннему соединению: MySQL SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ
	Получение данных, не относящихся к левой и правой таблицам одновременно (обратное INNER JOIN): MySQL SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE левая_таблица.ключ IS NULL OR правая_таблица.ключ IS NULL