Сравните EXCEPT и EXCEPT ALL: как различается кратность строк в результате?
EXCEPT возвращает множество строк, присутствующих в левом запросе и отсутствующих в правом, поэтому повторяющиеся строки в результате устраняются. EXCEPT ALL сохраняет кратность: если строка встречается слева чаще, чем справа, она попадёт в результат с разностью этих количеств.
В реляционной модели отношение является множеством, поэтому дубликаты строк не имеют отдельного смысла. Однако SQL часто работает с табличными результатами как с мультимножествами: сохранение повторов позволяет не выполнять неявное удаление дубликатов и корректно поддерживает агрегирование.
Операции EXCEPT и EXCEPT ALL отражают этот компромисс. Первая соответствует множественной разности, а вторая позволяет явно учитывать количество одинаковых строк.
Если в двух наборах есть одинаковые строки, простое удаление всех совпадений может исказить результат. Например, три появления строки слева и одно появление справа означают, что при мультимножественной семантике должны остаться два появления, а не ноль.
Неверный выбор операции особенно опасен при сравнении снимков данных, расчёте расхождений загрузки или анализе потоков событий. Кроме того, поддержка EXCEPT ALL различается между СУБД, поэтому переносимость запроса нужно проверять отдельно.
Пусть строка r встречается в левом результате m раз, а в правом — n раз. Для EXCEPT ALL её кратность равна max(m − n, 0). Для обычного EXCEPT результат содержит либо одну копию r, если m > 0 и n = 0, либо ни одной копии в остальных случаях.
Минимальный пример:
В первом случае дубликаты результата удаляются. Во втором они вычитаются поштучно. Например, при трёх строках A слева и одной строке A справа EXCEPT вернёт одну A, а EXCEPT ALL — две.
Операции требуют совместимости результатов: одинакового числа столбцов и совместимых типов в соответствующих позициях. Имена столбцов результата обычно берутся из левого запроса, но это не меняет правила сравнения строк.
Значения NULL при сопоставлении строк в операциях над множествами обрабатываются по правилам сравнения строк для этой операции, а не как обычное условие NULL = NULL; в поддерживаемых СУБД одинаковые позиции с NULL обычно считаются неразличимыми для удаления дубликатов и вычитания. Детали синтаксиса и наличие EXCEPT ALL следует сверять с конкретной СУБД.
Команда сравнивает два набора событий после повторной загрузки. В эталонном наборе событие встречается трижды, а в загруженном — один раз. Вариант с EXCEPT покажет только факт наличия или отсутствия значения и скроет две недостающие копии. Ручная агрегация с группировкой восстановит кратность, но усложнит запрос и потребует согласованной обработки NULL и всех столбцов.
Если СУБД поддерживает EXCEPT ALL, выбирают его: он непосредственно выражает требуемую мультимножественную разность и сохраняет смысл повторов. Если такой операции нет, применяют предварительную агрегацию по всем столбцам и сравнивают количества; это переносимее концептуально, но сложнее, чувствительнее к типам данных и может быть дороже по ресурсам.
Почему EXCEPT нельзя считать построчным удалением каждого совпадения?
Потому что обычный EXCEPT сначала рассматривает результаты как множества. Неважно, встретилась строка слева один раз или сто раз: если она отсутствует справа, в результате будет одна копия. Построчное вычитание с сохранением количества выполняет именно EXCEPT ALL.
Что произойдёт, если строка встречается справа чаще, чем слева?
В EXCEPT ALL отрицательная кратность невозможна: используется ноль. Например, при одной копии строки слева и трёх справа результат не содержит эту строку. В обычном EXCEPT итог также пуст для этой строки.
Можно ли заменить EXCEPT ALL обычным EXCEPT, если важен только факт наличия расхождения?
Да, если требуется лишь определить, существует ли строка, присутствующая слева и отсутствующая справа. Но такая замена теряет информацию о количестве недостающих копий. Поэтому для контроля полноты загрузки, инвентаризации или событийной обработки она может скрыть существенную ошибку.