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

WITH left_src(id, value) AS (
    VALUES (1, 'A'), (2, 'B')
), right_src(id, value) AS (
    VALUES (2, 'B2'), (3, 'C')
)
SELECT l.id AS left_id, l.value AS left_value,
       r.id AS right_id, r.value AS right_value
FROM left_src l
FULL OUTER JOIN right_src r ON r.id = l.id;

Краткий ответ

FULL OUTER JOIN сохраняет все строки обеих таблиц: совпавшие объединяются в одну строку, а строки без пары также остаются в результате. Для строки без совпадения поля отсутствующей стороны заполняются NULL: результатом будут пары (1, NULL), (2, 2) и (NULL, 3).

Это отличается от INNER JOIN, который оставил бы только идентификатор 2, и от односторонних внешних соединений, которые сохраняют строки только одной указанной стороны.

Исторический контекст

Реляционные запросы изначально удобно выражали пересечение связанных данных через INNER JOIN, но практические отчёты часто требуют сверки двух независимых наборов. Например, нужно сопоставить выгрузку платёжной системы с внутренним реестром и показать не только совпадения, но и расхождения.

FULL OUTER JOIN решает эту задачу одним оператором: он объединяет семантику сохранения строк левой и правой таблицы, не требуя заранее писать два отдельных запроса для поиска односторонних расхождений.

Постановка проблемы

В примере идентификатор 2 есть в обоих источниках, поэтому строки соединяются. Идентификатор 1 есть только слева, а 3 — только справа; если использовать INNER JOIN, оба расхождения исчезнут.

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

Подробное решение

Соединение проверяет условие r.id = l.id. Для каждой пары с истинным условием создаётся объединённая строка. Затем FULL OUTER JOIN добавляет каждую левую строку, для которой пары не нашлось, и каждую правую строку, для которой пары не нашлось; недостающие атрибуты расширяются NULL-значениями.

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

left_id | left_value | right_id | right_value
--------+------------+----------+------------
1       | A          | NULL     | NULL
2       | B          | 2        | B2
NULL    | NULL       | 3        | C

Если нужно классифицировать результат сверки, обычно проверяют, какая сторона отсутствует:

SELECT CASE
         WHEN l.id IS NULL THEN 'только справа'
         WHEN r.id IS NULL THEN 'только слева'
         ELSE 'в обоих источниках'
       END AS match_type,
       l.id AS left_id, r.id AS right_id
FROM left_src l
FULL OUTER JOIN right_src r ON r.id = l.id;

Ключевой нюанс — условие соединения должно корректно определять соответствие. Если ключ составной, все его значимые части должны участвовать в ON; иначе разные сущности могут ошибочно сопоставиться. Если ключ допускает NULL, обычное сравнение = не считает два NULL равными, поэтому для такого ключа может потребоваться явное NULL-безопасное сравнение, поддерживаемое конкретной СУБД.

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

Ситуация из практики

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

Вариант с двумя отдельными запросами для LEFT JOIN в разные стороны, объединёнными через UNION ALL, позволял явно контролировать логику, но усложнял запрос и повышал риск несовместимой обработки ключей. Вариант с FULL OUTER JOIN выразил сверку непосредственно и сохранил оба типа расхождений.

Выбрали FULL OUTER JOIN, добавили классификацию по IS NULL и предварительно проверили уникальность бизнес-ключа. Это дало один набор результатов: совпадения для контроля и односторонние записи для обработки исключений.

Что кандидаты часто упускают

1. Как отличить строку, отсутствующую справа, от строки, у которой справа действительно хранится NULL?

   Нельзя безусловно проверять r.value IS NULL: совпавшая строка может существовать, но содержать NULL в value. Надёжнее проверять столбец, который объявлен обязательным для каждой реальной строки, например r.id IS NULL, если id не допускает NULL. При отсутствии такого столбца полезно добавить технический признак существования строки или использовать гарантированно непустой ключ.

2. Можно ли заменить FULL OUTER JOIN объединением двух LEFT JOIN через UNION ALL?

   Можно воспроизвести близкую семантику, но требуется аккуратно исключить повторное включение совпавших строк из второй части. Обычно первая часть возвращает все строки слева, а вторая — только правые строки без пары; ошибка в условии исключения приводит к дублям или потере данных. Прямой FULL OUTER JOIN обычно яснее, хотя конкретная СУБД может по-разному оптимизировать оба варианта.

3. Почему проверка WHERE l.id IS NULL OR r.id IS NULL выбирает только расхождения?

   После полного внешнего соединения у совпавшей строки присутствуют обе стороны, поэтому оба идентификатора не равны NULL. У строки только слева r.id становится NULL, а у строки только справа — l.id; дизъюнкция оставляет оба вида односторонних записей. Условие корректно только при использовании столбцов, которые не бывают NULL у существующей строки.