SQL FULL OUTER JOIN: Reconciling Data and Faking It in MySQL

FULL OUTER JOIN — это объединение, которое не теряет ничего. INNER JOIN возвращает только совпадения, LEFT JOIN бережёт левую таблицу, RIGHT JOIN — правую. А FULL OUTER JOIN сохраняет строки с обеих сторон: совпало — строки склеиваются, не совпало — недостающая половина заполняется NULL. Именно поэтому он незаменим для сверки: когда нужно найти расхождения между двумя источниками, а не их пересечение.

В PostgreSQL это родная конструкция. В MySQL оператора нет вообще, и его приходится собирать вручную. Разберём всё по порядку на схеме users / orders.

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

Представим, что у нас есть зарегистрированные пользователи и заказы. Часть заказов оформлена гостями (без user_id), а часть пользователей ещё ничего не купила.

-- PostgreSQL
SELECT
    u.id        AS user_id,
    u.email,
    o.id        AS order_id,
    o.amount
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

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

• Совпадения — у пользователя есть заказ, обе половины строки заполнены.
• Только слева — пользователь без заказов: поля o.* равны NULL.
• Только справа — заказ без пользователя (гость или битый user_id): поля u.* равны NULL.

Ключевое слово OUTER необязательно: FULL JOIN и FULL OUTER JOIN — это одно и то же. Условие соединения по-прежнему живёт в ON, и оно работает симметрично — порядок таблиц на результат не влияет (с точностью до порядка столбцов).

Сверка: ищем расхождения

Главная практическая ценность FULL OUTER JOIN — найти то, что есть только с одной стороны. Классический сценарий: сверяем платежи из биллинга с заказами в нашей БД и хотим увидеть «висяки» с обеих сторон.

-- Заказы без платежей И платежи без заказов — одним запросом
SELECT
    o.id          AS order_id,
    o.amount      AS order_amount,
    p.id          AS payment_id,
    p.amount      AS payment_amount,
    CASE
        WHEN o.id IS NULL THEN 'платёж без заказа'
        WHEN p.id IS NULL THEN 'заказ без платежа'
        WHEN o.amount <> p.amount THEN 'суммы не совпадают'
        ELSE 'ok'
    END AS status
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id
WHERE o.id IS NULL
   OR p.id IS NULL
   OR o.amount <> p.amount;

Фильтр o.id IS NULL OR p.id IS NULL оставляет только несовпавшие строки — это и есть отчёт о расхождениях. Чтобы найти строго «сироты» с обеих сторон, проверяйте на NULL именно неисчезающий столбец, например первичный ключ, а не поле, которое само по себе может быть NULL.

Ещё один частый запрос — посчитать масштаб расхождений:

SELECT
    count(*) FILTER (WHERE o.id IS NULL) AS orphan_payments,
    count(*) FILTER (WHERE p.id IS NULL) AS unpaid_orders
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id;

Эмуляция в MySQL: LEFT UNION RIGHT

В MySQL FULL OUTER JOIN не поддерживается — запрос упадёт с синтаксической ошибкой. Стандартный приём: объединить LEFT JOIN и RIGHT JOIN через UNION.

-- MySQL: эмуляция FULL OUTER JOIN
SELECT u.id AS user_id, u.email, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id

UNION

SELECT u.id AS user_id, u.email, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

Как это работает: LEFT JOIN даёт все совпадения плюс пользователей без заказов, RIGHT JOIN — те же совпадения плюс заказы без пользователей. UNION склеивает оба набора и убирает дубликаты (совпавшие строки, которые попали в обе выборки).

• Используйте UNION, а не UNION ALL: именно UNION удаляет дубли совпавших строк. UNION ALL оставит каждое совпадение дважды.
• Набор и порядок столбцов в обеих половинах должны совпадать.

У UNION есть тонкость: он дедуплицирует по всем столбцам сразу. Если в данных есть полностью идентичные «легитимные» дубликаты, UNION схлопнет и их. Когда это важно, второй запрос делают через LEFT JOIN с фильтром, исключающим уже найденные совпадения, и используют UNION ALL:

-- Альтернатива без неявной дедупликации
SELECT u.id, u.email, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id

UNION ALL

SELECT u.id, u.email, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id
WHERE u.id IS NULL;   -- берём только строки, которых нет в LEFT-части

Подводные камни

• NULL в условии соединения. JOIN ... ON a.col = b.col никогда не совпадает по NULL — NULL = NULL даёт UNKNOWN. Такие строки попадут в «несовпавшую» часть FULL JOIN. Если хотите сводить NULL к NULL, используйте ON a.col IS NOT DISTINCT FROM b.col (PostgreSQL).
• Не путайте «нет совпадения» и «значение NULL». После FULL JOIN колонка может быть NULL по двум причинам: либо строки-пары не нашлось, либо само значение в исходных данных было NULL. Различить их можно только по неуязвимому столбцу — обычно первичному ключу.
• WHERE против ON. Условие в ON фильтрует то, что считается совпадением; условие в WHERE применяется уже после соединения и легко превращает FULL JOIN в INNER. Если написать WHERE o.status = 'paid', все строки с o.* IS NULL (пользователи без заказов) исчезнут.
• ClickHouse. Здесь FULL JOIN поддерживается, но непарным колонкам по умолчанию подставляются не NULL, а значения по умолчанию для типа (0, пустая строка). Чтобы получить честные NULL, оборачивайте столбцы в Nullable или включайте join_use_nulls = 1.

FULL OUTER JOIN — это инструмент «покажи мне всё и подсвети, чего не хватает». В PostgreSQL он работает из коробки, в MySQL собирается из LEFT и RIGHT через UNION, а в ClickHouse требует внимания к NULL. Держите в голове разницу между «нет пары» и «значение NULL» — и сверка данных перестанет быть болью.