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

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

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

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

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

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

MySQL
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:

payment_iddatefamily_membergoodamountunit_price
12005-02-12 00:00:001112000
22005-03-23 00:00:002112100
32005-05-14 00:00:0034520
42005-07-22 00:00:00451350
52005-07-26 00:00:00472150
62005-02-20 00:00:00561100
72005-07-30 00:00:00261120
82005-09-12 00:00:0021615500
92005-09-30 00:00:005151230
102005-10-27 00:00:005151230
112005-11-28 00:00:005151250
122005-12-22 00:00:005151250
132005-08-11 00:00:0031312200
142005-10-23 00:00:00214166000
152005-02-03 00:00:001958
162005-03-11 00:00:001957
172005-03-18 00:00:002938
182005-04-20 00:00:001988
192005-05-13 00:00:001957
202005-06-11 00:00:00293150
212006-01-12 00:00:003101100
222006-03-12 00:00:0015310
232005-06-05 00:00:00181300
242005-06-20 00:00:00368150

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

member_idstatusmember_namebirthday
1fatherHeadley Quincey1960-05-13 00:00:00
2motherFlavia Quincey1963-02-16 00:00:00
3sonAndie Quincey1983-06-05 00:00:00
4daughterLela Quincey1985-06-07 00:00:00
5daughterAnnie Quincey1988-04-10 00:00:00
6fatherErnest Forrest1961-09-11 00:00:00
7motherConstance Forrest1968-09-06 00:00:00

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

MySQL
SELECT *
FROM Payments 
    JOIN FamilyMembers ON family_member = member_id;

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

payment_iddatefamily_membergoodamountunit_pricemember_idstatusmember_namebirthday
12005-02-12 00:00:0011120001fatherHeadley Quincey1960-05-13 00:00:00
22005-03-23 00:00:0021121002motherFlavia Quincey1963-02-16 00:00:00
32005-05-14 00:00:00345203sonAndie Quincey1983-06-05 00:00:00
42005-07-22 00:00:004513504daughterLela Quincey1985-06-07 00:00:00
52005-07-26 00:00:004721504daughterLela Quincey1985-06-07 00:00:00
62005-02-20 00:00:005611005daughterAnnie Quincey1988-04-10 00:00:00
72005-07-30 00:00:002611202motherFlavia Quincey1963-02-16 00:00:00
82005-09-12 00:00:00216155002motherFlavia Quincey1963-02-16 00:00:00
92005-09-30 00:00:0051512305daughterAnnie Quincey1988-04-10 00:00:00
102005-10-27 00:00:0051512305daughterAnnie Quincey1988-04-10 00:00:00
112005-11-28 00:00:0051512505daughterAnnie Quincey1988-04-10 00:00:00
122005-12-22 00:00:0051512505daughterAnnie Quincey1988-04-10 00:00:00
132005-08-11 00:00:00313122003sonAndie Quincey1983-06-05 00:00:00
142005-10-23 00:00:002141660002motherFlavia Quincey1963-02-16 00:00:00
152005-02-03 00:00:0019581fatherHeadley Quincey1960-05-13 00:00:00
162005-03-11 00:00:0019571fatherHeadley Quincey1960-05-13 00:00:00
172005-03-18 00:00:0029382motherFlavia Quincey1963-02-16 00:00:00
182005-04-20 00:00:0019881fatherHeadley Quincey1960-05-13 00:00:00
192005-05-13 00:00:0019571fatherHeadley Quincey1960-05-13 00:00:00
202005-06-11 00:00:002931502motherFlavia Quincey1963-02-16 00:00:00
212006-01-12 00:00:0031011003sonAndie Quincey1983-06-05 00:00:00
222006-03-12 00:00:00153101fatherHeadley Quincey1960-05-13 00:00:00
232005-06-05 00:00:001813001fatherHeadley Quincey1960-05-13 00:00:00
242005-06-20 00:00:003681503sonAndie Quincey1983-06-05 00:00:00

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

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

MySQL
SELECT *
FROM Payments, FamilyMembers
WHERE family_member = member_id;

OUTER JOIN

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

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

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

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

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

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

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

idstart_pairend_pair
108:30:0009:15:00
209:20:0010:05:00
310:15:0011:00:00
411:05:0011:50:00
512:50:0013:35:00
613:40:0014:25:00
714:35:0015:20:00
815:25:0016:10:00

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

iddateclassnumber_pairteachersubjectclassroom
12019-09-019111147
42019-09-02914313
72019-09-03915636
102019-09-04919939
132019-09-059131343
162019-08-30912434
22019-09-01928213
52019-09-02922434
82019-09-039213737
112019-09-0492101040
142019-09-059211147
172019-08-30928213
32019-09-01934313
62019-09-02936535
92019-09-03936838
122019-09-049331141
152019-09-05935636
182019-08-30936535
192019-08-309410147

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

timepair.idstart_pairend_pairschedule.iddateclassnumber_pairteachersubjectclassroom
108:30:0009:15:0042019-09-02914313
108:30:0009:15:0072019-09-03915636
108:30:0009:15:00162019-08-30912434
108:30:0009:15:00102019-09-04919939
108:30:0009:15:0012019-09-019111147
108:30:0009:15:00132019-09-059131343
209:20:0010:05:0022019-09-01928213
209:20:0010:05:0052019-09-02922434
209:20:0010:05:00172019-08-30928213
209:20:0010:05:0082019-09-039213737
209:20:0010:05:00112019-09-0492101040
209:20:0010:05:00142019-09-059211147
310:15:0011:00:00122019-09-049331141
310:15:0011:00:00182019-08-30936535
310:15:0011:00:00152019-09-05935636
310:15:0011:00:0092019-09-03936838
310:15:0011:00:0062019-09-02936535
310:15:0011:00:0032019-09-01934313
411:05:0011:50:00192019-08-309410147
512:50:0013:35:00NULLNULLNULLNULLNULLNULLNULL
613:40:0014:25:00NULLNULLNULLNULLNULLNULLNULL
714:35:0015:20:00NULLNULLNULLNULLNULLNULLNULL
815:25:0016:10:00NULLNULLNULLNULLNULLNULLNULL

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

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

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

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

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

  1. Формируется таблица на основе внутреннего соединения (INNER JOIN).
  2. В таблицу добавляются значения не вошедшие в результат формирования из левой таблицы (LEFT OUTER JOIN).
  3. В таблицу добавляются значения не вошедшие в результат формирования из правой таблицы (RIGHT OUTER JOIN).
Соединение FULL JOIN реализовано не во всех СУБД. Например, в MySQL оно отсутствует, однако его можно очень просто эмулировать:
MySQL
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

Задания для самопроверки

Группировка данных, оператор GROUP BY, оператор HAVING
Ограничение выборки, оператор LIMIT