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

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

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

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

Такая замена полезна не сама по себе, а как способ выразить одну операцию средствами, доступными в конкретной СУБД. Однако она требует точного воспроизведения семантики исходного соединения.

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

LEFT JOIN сохраняет все строки своей левой стороны. Поэтому первый LEFT JOIN сохраняет все строки левого источника, включая совпавшие. Второй LEFT JOIN, если поменять таблицы местами, сохраняет все строки правого источника, включая те же совпавшие пары.

При объединении через UNION ALL совпавшая строка окажется в обеих ветвях. Простая замена изменит количество строк и может исказить отчёты, суммы и подсчёты.

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

Корректная схема состоит из двух частей: первый LEFT JOIN возвращает все строки слева, а вторая ветвь возвращает только строки справа, для которых не найдено совпадение слева.

SELECT l.id, l.value, r.value
FROM left_src AS l
LEFT JOIN right_src AS r ON r.id = l.id
UNION ALL
SELECT l.id, l.value, r.value
FROM right_src AS r
LEFT JOIN left_src AS l ON l.id = r.id
WHERE l.id IS NULL;

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

UNION ALL здесь принципиален: он не удаляет дубли и не выполняет лишнюю дедупликацию. Если заменить его на UNION, совпавшие строки могут случайно скрыть ошибку переписывания, а реальные одинаковые строки результата будут удалены.

Для многозначного совпадения правило также сохраняется: каждая совпавшая пара должна появиться один раз, а строки без пары — один раз с NULL-значениями на противоположной стороне. При неуникальных ключах нельзя добавлять произвольный DISTINCT, потому что он может уничтожить корректные пары.

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

При сверке каталогов товаров слева находились товары из внутренней системы, справа — товары поставщика. Аналитик заменил FULL OUTER JOIN двумя симметричными LEFT JOIN и объединил их через UNION ALL. Для товара, присутствующего в обоих каталогах, отчёт показал две строки, из-за чего число совпадений и агрегированные показатели удвоились.

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

Был выбран второй вариант. Для источников с гарантированно ненулевым ключом применили проверку этого ключа, а для nullable-ключей — проверку существования сопоставленной строки. Результат сохранил кардинальность FULL OUTER JOIN и корректность агрегатов.

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

Можно ли заменить UNION ALL на UNION, чтобы устранить дубли?

Нет, это не является корректным исправлением. UNION удаляет полные дубли результата, но не понимает, какие строки являются повтором из-за ошибочной схемы, а какие действительно должны присутствовать несколько раз. При неуникальном ключе соединения это может удалить разные корректные пары.

Почему проверка отсутствия строки по nullable-ключу может быть ошибочной?

Если проверять l.id IS NULL, а допустимая совпавшая строка действительно имеет id = NULL, проверка не отличит её от строки, созданной LEFT JOIN для отсутствующего совпадения. Нужно проверять ненулевой признак существования строки или использовать условие, эквивалентное проверке наличия самой строки.

Что изменится, если ключ соединения не уникален в одной или обеих таблицах?

Каждая строка слева соединяется с каждой подходящей строкой справа. Поэтому совпадение по ключу с двумя строками слева и тремя справа даст шесть пар. Корректная эмуляция FULL OUTER JOIN должна сохранить все такие пары, а фильтр второй ветви должен исключать только правосторонние строки, для которых вообще нет совпадения, а не строки с каким-либо конкретным номером пары.