FULL OUTER JOIN в SQL: как найти совпадения и расхождения между таблицами

FULL OUTER JOIN — это соединение таблиц, которое ничего не выбрасывает.

Обычный INNER JOIN показывает только строки, где нашлась пара в обеих таблицах. LEFT JOIN сохраняет все строки из левой таблицы. RIGHT JOIN сохраняет все строки из правой таблицы.

А FULL OUTER JOIN сохраняет обе стороны сразу:

• если пара нашлась — строки соединяются;
• если строка есть только слева — правая часть заполняется NULL;
• если строка есть только справа — левая часть заполняется NULL.

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

Например:

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

Проще говоря, FULL OUTER JOIN — это SQL-инструмент для вопроса:

Покажи мне всё с обеих сторон и подсвети, где нет пары.

Простая картина: чем отличаются JOIN

Допустим, есть две таблицы:

users — пользователи.

orders — заказы.

Связь между ними такая: заказ может принадлежать пользователю через user_id.

Если использовать INNER JOIN, мы увидим только пользователей, у которых есть заказы:

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

Пользователи без заказов исчезнут. Заказы без пользователя тоже исчезнут.

Если использовать LEFT 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;

Пользователи без заказов останутся, но поля заказа будут равны NULL.

А вот FULL OUTER JOIN сохранит всех: и пользователей, и заказы.

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;

Такой запрос покажет полную картину.

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

Результат FULL OUTER JOIN можно мысленно разделить на три группы.

Первая группа — совпадения.

Это строки, где пользователь найден и заказ найден:

user_id	email	order_id	amount
1	user1@example.com	101	500
2	user2@example.com	102	1200

Вторая группа — строки только из левой таблицы.

Например, пользователь зарегистрировался, но ещё ничего не купил:

user_id	email	order_id	amount
3	user3@example.com	NULL	NULL

Третья группа — строки только из правой таблицы.

Например, заказ есть, но пользователя для него не нашлось:

user_id	email	order_id	amount
NULL	NULL	103	900

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

FULL JOIN и FULL OUTER JOIN — это одно и то же

В PostgreSQL можно писать и так:

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

И так:

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

Это одинаковые запросы.

Слово OUTER можно опустить. Оно не меняет смысл, а просто делает запись более полной и явной.

Для новичка FULL OUTER JOIN часто читается понятнее: полное внешнее соединение, то есть сохраняем внешние несовпавшие строки с обеих сторон.

Где живёт условие соединения

Условие соединения пишется в ON, как и в других видах JOIN.

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.user_id = u.id

означает: заказ считается парой для пользователя, если user_id заказа равен id пользователя.

Если условие выполнилось, строки склеились.

Если нет — строка всё равно останется в результате, но недостающая сторона будет заполнена NULL.

Главный сценарий: сверка данных

Самое полезное применение FULL OUTER JOIN — сверка двух источников.

Представим, что у нас есть:

• orders — заказы в нашей системе;
• payments — платежи из платёжной системы.

В идеальном мире каждому оплаченному заказу соответствует один платёж. Но в реальной жизни бывают проблемы:

• заказ есть, а платежа нет;
• платёж есть, а заказа нет;
• заказ и платёж есть, но суммы отличаются.

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 'payment_without_order'
        WHEN p.id IS NULL THEN 'order_without_payment'
        WHEN o.amount <> p.amount THEN 'amount_mismatch'
        ELSE 'ok'
    END AS check_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, значит платёж есть, а заказа нет.

Если p.id IS NULL, значит заказ есть, а платежа нет.

Если обе строки нашлись, но o.amount <> p.amount, значит суммы не совпадают.

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

Почему проверять лучше по первичному ключу

После FULL OUTER JOIN в результате часто появляются NULL. Но важно понимать: NULL может означать разные вещи.

Например, поле email может быть NULL, потому что пользователь не указал почту.

А может быть NULL, потому что пользователя вообще не нашлось.

Это разные ситуации.

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

Хорошо:

WHERE u.id IS NULL

или:

WHERE o.id IS NULL

Опаснее:

WHERE u.email IS NULL

Потому что почта может быть пустой даже у существующего пользователя.

Правило простое:

чтобы понять, нашлась ли строка, проверяйте ключ, а не обычное поле.

Как найти строки только с одной стороны

Если нужно найти пользователей без заказов и заказы без пользователей, можно оставить только несовпавшие строки:

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
WHERE u.id IS NULL
   OR o.id IS NULL;

Этот запрос убирает нормальные совпадения и оставляет только проблемы.

Если u.id IS NULL, значит есть заказ без пользователя.

Если o.id IS NULL, значит есть пользователь без заказа.

Такой запрос хорошо подходит для диагностики данных.

Как посчитать количество расхождений

Иногда нужен не список строк, а короткая сводка: сколько проблем каждого типа.

В PostgreSQL удобно использовать FILTER.

SELECT
    count(*) FILTER (WHERE o.id IS NULL) AS orders_without_users,
    count(*) FILTER (WHERE p.id IS NULL) AS users_without_orders
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id
FULL OUTER JOIN payments p ON p.order_id = o.id;

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

SELECT
    count(*) FILTER (WHERE o.id IS NULL) AS payments_without_orders,
    count(*) FILTER (WHERE p.id IS NULL) AS orders_without_payments,
    count(*) FILTER (
        WHERE o.id IS NOT NULL
          AND p.id IS NOT NULL
          AND o.amount <> p.amount
    ) AS amount_mismatches
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id;

Такой результат удобно положить в мониторинг или ежедневный отчёт качества данных.

WHERE может случайно сломать FULL OUTER JOIN

Одна из самых частых ошибок — добавить условие в WHERE и незаметно выбросить часть строк.

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

Новичок может написать так:

SELECT
    u.id AS user_id,
    u.email,
    o.id AS order_id,
    o.status
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

Проблема в том, что WHERE выполняется уже после соединения.

У пользователей без заказов поля o.* будут равны NULL. Условие o.status = 'paid' для них не выполнится, и эти строки исчезнут.

То есть запрос уже не покажет всех пользователей. Он сам отрежет строки, ради которых вы, возможно, и использовали FULL OUTER JOIN.

Часто условие по правой таблице нужно переносить в ON.

SELECT
    u.id AS user_id,
    u.email,
    o.id AS order_id,
    o.status
FROM users u
FULL OUTER JOIN orders o
    ON o.user_id = u.id
   AND o.status = 'paid';

Теперь условие o.status = 'paid' влияет на то, какой заказ считается подходящей парой, но не выбрасывает пользователей без заказов из результата.

Разница тонкая, но очень важная:

• ON определяет, какие строки считаются совпавшими;
• WHERE фильтрует уже готовый результат.

NULL в условии соединения

В SQL NULL означает неизвестное значение.

Поэтому обычное сравнение через = не считает два NULL равными.

Например:

NULL = NULL

не даёт true.

Из-за этого соединение по условию:

ON a.external_id = b.external_id

не соединит строки, где в обеих таблицах external_id равен NULL.

Такие строки попадут в результат как несовпавшие.

Иногда это именно то, что нужно. Но иногда при сверке хочется считать два NULL одинаковыми.

В PostgreSQL для этого есть оператор IS NOT DISTINCT FROM.

SELECT
    a.id AS left_id,
    b.id AS right_id
FROM source_a a
FULL OUTER JOIN source_b b
    ON a.external_id IS NOT DISTINCT FROM b.external_id;

Он ведёт себя как безопасное равенство, где NULL совпадает с NULL.

Но используйте его осознанно. В бизнес-смысле два отсутствующих значения не всегда означают одно и то же.

FULL OUTER JOIN в MySQL

В PostgreSQL FULL OUTER JOIN работает из коробки.

В MySQL такого оператора нет. Если написать:

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

MySQL выдаст синтаксическую ошибку.

Поэтому FULL OUTER JOIN в MySQL обычно собирают вручную: берут LEFT JOIN, берут RIGHT JOIN и объединяют результаты через UNION.

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;

Как это работает:

• первая часть возвращает все строки слева и совпадения справа;
• вторая часть возвращает все строки справа и совпадения слева;
• UNION объединяет оба результата и убирает дубликаты.

Так получается аналог FULL OUTER JOIN.

Почему не всегда стоит использовать UNION

UNION удаляет дубликаты по всей строке.

Обычно это удобно: совпавшие строки попали и в LEFT JOIN, и в RIGHT JOIN, а UNION оставил их один раз.

Но есть тонкость.

Если в данных есть настоящие одинаковые строки, которые не являются техническими дублями, UNION тоже может их схлопнуть.

Когда это важно, используют другой вариант: LEFT JOIN плюс только несовпавшие строки из правой части через UNION ALL.

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 ALL

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
WHERE u.id IS NULL;

Здесь логика такая:

• первая часть уже взяла все совпадения и все строки слева;
• вторая часть добавляет только строки, которые есть справа, но не нашлись слева;
• UNION ALL ничего не удаляет и не пытается умничать с дубликатами.

Такой вариант часто более предсказуем для больших и сложных данных.

Важно: столбцы в обеих частях UNION должны совпадать

Когда вы эмулируете FULL OUTER JOIN через UNION, обе части запроса должны возвращать одинаковое количество столбцов в одинаковом порядке.

Правильно:

SELECT
    u.id AS user_id,
    u.email,
    o.id AS order_id
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
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

Неправильно:

SELECT
    u.id AS user_id,
    u.email
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
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

Во второй половине три столбца, а в первой два. Такой запрос не соберётся.

FULL OUTER JOIN в ClickHouse

В ClickHouse FULL JOIN поддерживается, но есть важный нюанс с отсутствующими значениями.

В классическом SQL при отсутствии пары недостающие поля становятся NULL.

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

Это может запутать сверку.

Например, вы ожидаете увидеть NULL, чтобы понять, что строки нет. А вместо этого видите 0 и думаете, что это настоящий идентификатор или настоящая сумма.

Чтобы получить поведение ближе к классическому SQL, в ClickHouse используют настройку join_use_nulls.

SET join_use_nulls = 1;

После этого отсутствующая сторона соединения будет заполняться NULL, и сверку читать проще.

Практический пример: сравнить старую и новую выгрузку

Допустим, вы переносите пользователей из старой системы в новую.

Есть две таблицы:

• old_users;
• new_users.

Нужно найти:

• кто есть только в старой системе;
• кто есть только в новой;
• у кого поменялась почта.

SELECT
    old_u.id AS old_user_id,
    old_u.email AS old_email,
    new_u.id AS new_user_id,
    new_u.email AS new_email,
    CASE
        WHEN old_u.id IS NULL THEN 'only_in_new'
        WHEN new_u.id IS NULL THEN 'only_in_old'
        WHEN old_u.email <> new_u.email THEN 'email_mismatch'
        ELSE 'ok'
    END AS check_status
FROM old_users old_u
FULL OUTER JOIN new_users new_u ON new_u.id = old_u.id
WHERE old_u.id IS NULL
   OR new_u.id IS NULL
   OR old_u.email <> new_u.email;

Такой запрос похож на ревизию склада: кладём рядом две ведомости и смотрим, где не совпало.

Практический пример: сверить остатки склада

Есть таблица warehouse_stock с остатками склада и таблица catalog_products с товарами в каталоге.

Нужно найти товары, которые есть на складе, но отсутствуют в каталоге, и товары, которые есть в каталоге, но отсутствуют на складе.

SELECT
    c.sku AS catalog_sku,
    c.name AS product_name,
    w.sku AS warehouse_sku,
    w.quantity,
    CASE
        WHEN c.sku IS NULL THEN 'stock_without_catalog_item'
        WHEN w.sku IS NULL THEN 'catalog_item_without_stock'
        ELSE 'ok'
    END AS check_status
FROM catalog_products c
FULL OUTER JOIN warehouse_stock w ON w.sku = c.sku
WHERE c.sku IS NULL
   OR w.sku IS NULL;

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

Когда FULL OUTER JOIN не нужен

FULL OUTER JOIN мощный, но не стоит использовать его везде.

Если вам нужны только совпавшие строки, используйте INNER JOIN.

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

Если вам нужны все пользователи и, при наличии, их заказы, используйте LEFT JOIN.

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

Если вам нужно найти пользователей без заказов, часто достаточно LEFT JOIN с проверкой правого ключа на NULL.

SELECT
    u.id,
    u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

FULL OUTER JOIN нужен именно тогда, когда важны обе стороны и вы не хотите потерять ни левую, ни правую таблицу.

Как думать о FULL OUTER JOIN

Удобная mental model такая.

FULL OUTER JOIN — это как сверить два списка маркером:

1. строки, которые есть в обоих списках, кладём рядом;
2. строки, которые есть только в первом списке, оставляем с пустой правой частью;
3. строки, которые есть только во втором списке, оставляем с пустой левой частью.

После этого можно:

• смотреть полный результат;
• отфильтровать только расхождения;
• посчитать количество проблем;
• подписать каждую строку статусом через CASE.

Главное

FULL OUTER JOIN возвращает все строки из обеих таблиц.

Если строки совпали по условию ON, они объединяются.

Если строки нет слева или справа, недостающие поля заполняются NULL.

Базовый пример в 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;

Главная польза FULL OUTER JOIN — сверка данных.

Например, заказы без платежей, платежи без заказов и несовпадающие суммы:

SELECT
    o.id AS order_id,
    p.id AS payment_id,
    CASE
        WHEN o.id IS NULL THEN 'payment_without_order'
        WHEN p.id IS NULL THEN 'order_without_payment'
        WHEN o.amount <> p.amount THEN 'amount_mismatch'
        ELSE 'ok'
    END AS check_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;

В PostgreSQL FULL OUTER JOIN поддерживается напрямую.

В MySQL его приходится эмулировать через LEFT JOIN, RIGHT JOIN и UNION.

В ClickHouse нужно внимательно относиться к отсутствующим значениям и настройке join_use_nulls.

Главное правило: после FULL OUTER JOIN не путайте отсутствие пары и обычное значение NULL в данных. Чтобы понять, нашлась ли строка, проверяйте надёжный ключ, обычно id.