Антисоединение через NOT EXISTS заменяют операцией EXCEPT: что происходит с дубликатами строк левой выборки?

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

EXCEPT по умолчанию устраняет дубликаты, поэтому он неэквивалентен антисоединению через NOT EXISTS, если левая выборка содержит повторяющиеся строки. NOT EXISTS сохраняет каждую строку слева, для которой не найдено совпадение справа.

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

EXCEPT относится к реляционным операциям над множествами и выражает разность двух результатов. В SQL эта операция обычно имеет семантику множества, хотя сами таблицы и промежуточные результаты могут содержать дубликаты.

NOT EXISTS появился как проверка существования связанной строки. Его семантика основана на построчной проверке внешнего запроса, поэтому он не схлопывает одинаковые строки внешнего результата.

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

Результаты могут отличаться, когда левая часть содержит несколько одинаковых строк без соответствий справа. Механическая замена NOT EXISTS на EXCEPT в такой ситуации уменьшит число строк и может исказить отчёт, подсчёты или дальнейшую агрегацию.

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

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

Рассмотрим минимальный пример:

WITH left_src(id) AS (
    VALUES (1), (1), (2)
), right_src(id) AS (
    VALUES (2)
)
SELECT l.id
FROM left_src AS l
WHERE NOT EXISTS (
    SELECT 1 FROM right_src AS r WHERE r.id = l.id
)

EXCEPT

SELECT id
FROM right_src;

В первой части антисоединение вернёт две строки со значением 1: каждая строка left_src проверяется отдельно. Однако сам пример с EXCEPT нельзя читать как объединённую замену двух запросов: для сравнения эквивалентных форм следует сопоставить результат NOT EXISTS с результатом SELECT id FROM left_src EXCEPT SELECT id FROM right_src.

Такой EXCEPT вернёт одну строку 1, потому что результат сначала рассматривается как множество. Если нужна мультимножественная разность, существует EXCEPT ALL, но она тоже не всегда равна NOT EXISTS: EXCEPT ALL вычитает количество совпадений справа, тогда как NOT EXISTS либо сохраняет все экземпляры конкретной левой строки, либо отбрасывает их все, если совпадение найдено.

Условия также могут различаться из-за NULL. При условии r.id = l.id пара NULL не считается совпадением, поэтому строка слева сохранится через NOT EXISTS. При EXCEPT значения NULL обычно сопоставляются как равные элементы множества. Поэтому выбор между операциями должен учитывать и кратность, и семантику сравнения.

На практике NOT EXISTS предпочтителен, когда нужно сохранить кардинальность внешнего результата и проверять существование связанной строки. EXCEPT удобнее, когда требуется именно разность наборов с устранением дубликатов.

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

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

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

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

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

1. Вопрос: Достаточно ли заменить EXCEPT на EXCEPT ALL, чтобы получить полную эквивалентность NOT EXISTS?

   Ответ: Нет. EXCEPT ALL работает с количеством одинаковых строк: из числа строк слева вычитает число строк справа. NOT EXISTS проверяет наличие хотя бы одного совпадения для каждой строки слева. Если справа есть хотя бы одна совпадающая строка, все соответствующие левые строки отбрасываются, а не уменьшаются на количество совпадений.

2. Вопрос: Почему одинаковые строки особенно опасны при последующей агрегации?

   Ответ: Устранение дубликатов меняет входную кардинальность агрегата. Например, COUNT(*), сумма или количество событий после EXCEPT могут стать меньше, хотя логика фильтрации по наличию соответствия формально кажется той же. Ошибка проявляется не только в выводе строк, но и в итоговых метриках.

3. Вопрос: Как NULL влияет на выбор между NOT EXISTS и EXCEPT?

   Ответ: В NOT EXISTS поведение определяется условием корреляции. При обычном = сравнение NULL с NULL даёт UNKNOWN, поэтому совпадение не считается найденным. В EXCEPT сравнение строк для целей операции над множествами обычно трактует соответствующие NULL как равные, поэтому строка с NULL может быть исключена. Если это различие существенно, его нужно явно учитывать в условии или не заменять одну форму другой.