Внешнее соединение OUTER JOIN

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

Внешнее соединение бывает трёх типов: левое (LEFT), правое (RIGHT) и полное (FULL). Тип указывать обязательно — написать просто OUTER JOIN нельзя, это синтаксическая ошибка. А вот само слово OUTER необязательно: LEFT JOIN и LEFT OUTER JOIN означают одно и то же, и дальше используется короткая запись.

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

Возвращает все строки левой таблицы. Если для строки нашлась пара в правой таблице, строки склеиваются; если не нашлась — поля правой таблицы заполняются NULL.

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

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

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

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

В результат попали все восемь звонков — как и обещает левое соединение. Но строк получилось 43, а не 8.

Соединение не дополняет левую таблицу, а перебирает все подходящие пары строк. Один и тот же номер пары встречается в расписании занятий много раз — в разные дни и у разных классов, — и каждое совпадение даёт отдельную строку. Если ключ в правой таблице неуникален, строк в результате будет больше, чем в левой таблице.

В конце выборки есть строки, где все поля занятия заполнены NULL. Это звонки, для которых не нашлось ни одного занятия: пары нет, но строка левой таблицы обязана попасть в результат.

Строки без пары

На этих NULL строится самый частый практический приём — найти записи, у которых пары нет. Достаточно оставить только строки с пустым ключом правой таблицы:

MySQL 8.1
SELECT Timepair.id, start_pair, end_pair
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id
WHERE Schedule.number_pair IS NULL;

Остались три звонка, на которые не поставлено ни одного занятия.

Соединение, из которого оставляют только строки без пары, называют анти-джойном (ANTI JOIN). Собственного оператора у него нет: и в MySQL, и в PostgreSQL его записывают именно так — соединением и условием IS NULL.

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

Зеркальное отражение левого: в результат обязательно попадают все строки правой таблицы, а недостающие поля левой заполняются NULL.

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    RIGHT JOIN Schedule ON Schedule.number_pair = Timepair.id;

В результате 40 строк — ровно столько, сколько записей в Schedule, и ни одной строки с NULL. То есть он полностью совпал с внутренним соединением.

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

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

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

Результат полного соединения складывается из трёх частей:

• строки, вошедшие во внутреннее соединение (INNER JOIN);
• строки левой таблицы, для которых не нашлось пары;
• строки правой таблицы, для которых не нашлось пары.

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    FULL OUTER JOIN Schedule ON Schedule.number_pair = Timepair.id;

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

UNION ALL

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

Все виды соединений таблиц

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

Давайте проверим себя. В левой таблице 8 строк. Сколько строк вернёт LEFT JOIN с правой таблицей?