Как найти изменённые строки между версиями таблицы в SQL с NULL

Ночная загрузка принесла новую таблицу клиентов. Нужно понять, кому обновить email и тариф, кого добавить и кто исчез из источника. Обычное сравнение old.email <> new.email пропускает переход от NULL к адресу: результат такого сравнения сам равен NULL, а условие WHERE оставляет только истинные значения.

Сначала нужны устойчивые ключи строк. В примере это id; он уникален в обеих версиях и не меняется при обновлении email. Если ключом выбрать email, то смена адреса будет выглядеть как удаление одного клиента и добавление другого. Поэтому идентичность сущности и сравниваемые атрибуты нельзя смешивать.

Три исхода сравнения

Начинаем с новой версии и присоединяем старую по ключу. Отсутствие старой строки означает added. При наличии обеих строк сравниваем поля через SQLite IS NOT: оператор считает NULL и NULL равными, а NULL и значение — различными. Затем вторым запросом находим ключи, которые были в старой версии и исчезли в новой. UNION ALL соединяет непересекающиеся категории изменений.

CREATE TABLE old_customers (id INTEGER PRIMARY KEY, email TEXT, tier TEXT);
CREATE TABLE new_customers (id INTEGER PRIMARY KEY, email TEXT, tier TEXT);
INSERT INTO old_customers VALUES
 (1,NULL,'basic'),(2,NULL,'basic'),(3,'a@example.org','basic'),(4,'b@example.org','vip');
INSERT INTO new_customers VALUES
 (1,NULL,'basic'),(2,'c@example.org','basic'),(3,'a@example.org','vip'),(5,NULL,'new');
SELECT n.id AS id, CASE WHEN o.id IS NULL THEN 'added' ELSE 'changed' END AS change_type
FROM new_customers AS n LEFT JOIN old_customers AS o ON o.id=n.id
WHERE o.id IS NULL OR n.email IS NOT o.email OR n.tier IS NOT o.tier
UNION ALL
SELECT o.id, 'deleted'
FROM old_customers AS o LEFT JOIN new_customers AS n ON n.id=o.id
WHERE n.id IS NULL
ORDER BY id;

Получаются изменения: клиент 2 — changed из-за нового email, клиент 3 — changed из-за тарифа, клиент 4 — deleted, клиент 5 — added. Клиент 1, у которого email был и остался NULL, в список не попадает. Это важная проверка: если бы два неизвестных адреса считались изменением, загрузка обновляла бы строку каждую ночь.

В SQLite IS NOT — краткая форма IS DISTINCT FROM. Последнюю запись удобно использовать там, где она поддерживается: смысл NULL-безопасного сравнения читается напрямую. Не заменяйте это сравнение на COALESCE(email, 'нет'), если строка «нет» может быть реальным значением: тогда разные состояния ошибочно совпадут.

Исчезновение — ещё не команда удалить

Отсутствие id в новой выгрузке может означать удаление клиента, а может — неполную выгрузку, новый фильтр источника или сбой загрузки. Сам SQL называет различие двух снимков; правило применения deleted требует отдельной договорённости. Перед реальным удалением проверьте полноту новой версии, число строк и ожидаемую дату снимка.

Если нужно увидеть, какое именно поле изменилось, добавьте отдельные флаги n.email IS NOT o.email и n.tier IS NOT o.tier для строк changed. Так редактор данных сможет обновить только нужные атрибуты и объяснить скачок числа изменений. Для десятков полей обычно строят сравнение из утверждённой схемы, а не перечисляют случайные поля, попавшиеся в выгрузке.

В примере уникальность ключа обеспечена PRIMARY KEY. Если в реальной выгрузке один id встречается дважды, JOIN размножит строку и список изменений станет ложным. Проверка уникальности ключа — предварительное условие именно для этого способа сопоставления.

Сравнивать значения лучше после одинаковой нормализации обеих версий. Если источник стал отдавать адреса в другом регистре или добавил пробелы по краям, запрос честно сообщит о тысячах текстовых изменений. Бизнесу может быть нужен регистронезависимый email, но тогда правило очистки следует применить до сравнения и сохранить исходные значения для аудита. Нельзя лечить массовые изменения случайной очисткой только новой таблицы.

Для нескольких десятков миллионов строк полезно записывать результат сопоставления в отдельную таблицу изменений с датой обоих снимков и контрольными количествами: сколько добавлено, изменено и исчезло. Эти количества легко сравнить с объёмом ночной выгрузки. Внезапное исчезновение половины клиентов обычно говорит о проблеме источника, а не о массовом удалении по воле пользователей.

На курсе «Продвинутый SQL» проверки качества данных и сложные JOIN переходят в сверку отчётов и итоговую витрину. Студент пишет запросы в браузере и проверяет их на пропусках и дублях. Откройте программу, чтобы освоить такие сравнения как часть воспроизводимой загрузки данных.

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