SQL FULL OUTER JOIN: все строки обеих таблиц

Урок 5.5 · Время чтения: ~6 мин

FULL OUTER JOIN в SQL возвращает все строки обеих таблиц: совпадающие строки объединяются, а строки без совпадения с любой стороны сохраняются с NULL в столбцах другой таблицы. В этом уроке вы изучите синтаксис, узнаете, какие СУБД его поддерживают, и научитесь получать тот же результат в MySQL и MariaDB.

LEFT JOIN сохраняет строки без совпадения из одной таблицы, RIGHT JOIN - из другой. FULL OUTER JOIN сохраняет и те, и другие. Это инструмент для сравнения двух списков: что есть в обоих, что только в первом и что только во втором.

Что возвращает FULL OUTER JOIN

Результат состоит из трёх частей:

1. Пары совпадающих строк, как в INNER JOIN.
2. Строки левой таблицы без совпадения, с NULL в столбцах правой таблицы.
3. Строки правой таблицы без совпадения, с NULL в столбцах левой таблицы.

Базовый синтаксис

SELECT left_table.column1, right_table.column2
FROM left_table
FULL OUTER JOIN right_table ON left_table.key_column = right_table.key_column;

FULL JOIN и FULL OUTER JOIN - одно и то же.

Пример: актёры и клиенты

Кто в базе Sakila актёр, кто клиент, а кто и то и другое? В PostgreSQL или SQL Server на этот вопрос сразу отвечает полное соединение по имени:

-- PostgreSQL, SQL Server
SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name;

Каждый актёр появляется один раз, каждый клиент - один раз, а человек, который является и тем и другим, - в одной строке с заполненными обеими сторонами.

FULL OUTER JOIN в MySQL и MariaDB

В MySQL и MariaDB нет FULL OUTER JOIN, но тот же результат можно собрать из двух запросов, объединённых через UNION ALL:

1. LEFT JOIN возвращает совпадения и строки, которые есть только в левой таблице.
2. RIGHT JOIN с WHERE left_key IS NULL добавляет только строки, которые есть только в правой таблице.

SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
LEFT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
UNION ALL
SELECT a.first_name, a.last_name, c.first_name, c.last_name
FROM actor AS a
RIGHT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL;

Результат: 798 строк - 200 актёров плюс 599 клиентов минус один человек, который является и тем и другим и показан в одной объединённой строке (JENNIFER DAVIS).

Этот приём иногда записывают через UNION вместо UNION ALL и без условия WHERE. Так работает, только если в результате нет законно повторяющихся строк: UNION удаляет все дубликаты, к тому же он медленнее. Вариант с UNION ALL точен. UNION и UNION ALL вы изучите в уроке 5.10.

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

Полное соединение полезно везде, где два источника должны содержать одни и те же элементы:

• сверка платежей в базе данных с банковской выпиской;
• сравнение каталога товаров со складскими остатками;
• поиск записей, которые после миграции есть только в одной из двух систем.

Чтобы оставить только расхождения, отфильтруйте строки, где одна из сторон равна NULL:

-- PostgreSQL, SQL Server: только актёры или только клиенты
SELECT a.actor_id, c.customer_id
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL OR c.customer_id IS NULL;

Ключевые выводы:

• FULL OUTER JOIN возвращает совпадающие строки и строки без совпадения из обеих таблиц.
• Столбцы недостающей стороны равны NULL.
• PostgreSQL, SQL Server, Oracle и SQLite 3.39+ его поддерживают; MySQL и MariaDB - нет.
• В MySQL и MariaDB: LEFT JOIN UNION ALL RIGHT JOIN ... WHERE left_key IS NULL.
• Полное соединение - естественный инструмент для сравнения двух списков.

Часто задаваемые вопросы

Поддерживает ли MySQL FULL OUTER JOIN?

Нет. MySQL и MariaDB его не поддерживают. Объедините LEFT JOIN и RIGHT JOIN через UNION ALL, оставив во втором запросе только строки без совпадения (WHERE left_key IS NULL).

Чем FULL OUTER JOIN отличается от CROSS JOIN?

FULL OUTER JOIN связывает строки по условию и сохраняет строки без совпадения из обеих таблиц. У CROSS JOIN нет условия: он возвращает все комбинации строк, поэтому его результат обычно гораздо больше.

Почему для замены FULL OUTER JOIN нужен UNION ALL, а не UNION?

UNION удаляет повторяющиеся строки, в том числе те, что законно повторяются в данных, и тратит лишнее время на поиск дубликатов. UNION ALL с фильтром IS NULL во втором запросе возвращает ровно те строки, что и полное соединение.