Когда использовать EXCEPT вместо NOT EXISTS в Transact SQL?

Question

Я только недавно узнал о существовании нового предложения "EXCEPT" в SQL Server (немного поздно, я знаю...) благодаря чтению кода, написанного коллегой. Это действительно поразило меня!

Но тогда у меня есть несколько вопросов относительно его использования: когда это рекомендуется использовать? Есть ли разница с точки зрения производительности между его использованием и сопоставленным запросом, использующим "И НЕ СУЩЕСТВУЕТ..."?

После прочтения статьи EXCEPT в BOL я подумал, что это просто сокращение для второго варианта, но был удивлен, когда переписал пару запросов, используя его (поэтому у них был синтаксис "И НЕ СУЩЕСТВУЕТ", гораздо более знакомый мне), а затем проверил планы выполнения - сюрприз! Версия EXCEPT имела более короткий план выполнения и выполнялась также быстрее. Это всегда так?

Итак, я хотел бы знать: каковы рекомендации по использованию этого мощного инструмента?

Answer 1

EXCEPT лечит NULL значения как совпадающие.

Этот запрос:

WITH    q (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  1
        ),
        p (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    q
WHERE   value NOT IN
        (
        SELECT  value
        FROM    p
        )

вернет пустой набор строк.

Этот запрос:

WITH    q (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  1
        ),
        p (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    q
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    p
        WHERE   p.value = q.value
        )

вернусь

NULL
1

, и этот:

WITH    q (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  1
        ),
        p (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    q
EXCEPT
SELECT  *
FROM    p

вернусь:

1

Рекурсивная ссылка также разрешена в EXCEPT оговорка в рекурсивном CTEхотя он ведет себя странным образом: он возвращает все, кроме последней строки предыдущего набора, а не все, кроме всего предыдущего набора:

WITH    q (value) AS
        (
        SELECT  1
        UNION ALL
        SELECT  2
        UNION ALL
        SELECT  3
        ),
        rec (value) AS
        (
        SELECT  value
        FROM    q
        UNION ALL
        SELECT  *
        FROM    (
                SELECT  value
                FROM    q
                EXCEPT
                SELECT  value
                FROM    rec
                ) q2
        )
SELECT  TOP 10 *
FROM    rec

---
1
2
3
-- original set
1
2
-- everything except the last row of the previous set, that is 3
1
3
-- everything except the last row of the previous set, that is 2
1
2
-- everything except the last row of the previous set, that is 3, etc.
1

SQL Server разработчики, должно быть, просто забыли запретить это.

Answer 2

Я сделал много анализа, кроме, не существует, не в и оставил внешнее соединение. Обычно левое внешнее соединение является самым быстрым для поиска пропущенных строк, особенно для соединения по первичному ключу. Not In может быть очень быстрым, если вы знаете, что это будет небольшой список, возвращаемый в select.

Я использую EXCEPT много, чтобы сравнить то, что возвращается при переписывании кода. Запустите старый код, сохранив результаты. Запустите новый код, сохраняющий результаты, а затем используйте его, чтобы зафиксировать все различия. Это очень быстрый и простой способ найти различия, особенно когда нужно получить все различия, включая ноль. Очень хорошо для легкого кодирования на лету.

Но каждая ситуация отличается. Я говорю каждому разработчику, которого я когда-либо обучал. Попытайся. Делайте тайминги по-разному. Попробуй, пора, сделай.

Answer 3

EXCEPT сравнивает все (парные) столбцы двух полных выборов. NOT EXISTS сравнивает две или более таблиц в соответствии с условиями, указанными в предложении WHERE в подзапросе после ключевого слова NOT EXISTS.

EXCEPT может быть переписан с использованием NOT EXISTS. (КРОМЕ ВСЕХ можно переписать, используя ROW_NUMBER и NOT EXISTS.)

Получил это отсюда

Answer 4

Если ваш запрос точно настроен, то нет никакой разницы в производительности по ч / б с использованием предложения EXCEPT и NOT EXIST/NOT IN.. в первый раз, когда я запустил EXCEPT после изменения моего коррелированного запроса на него.. Я был удивлен, потому что он вернулся с результат только через 7 секунд, в то время как коррелированный запрос возвращался через 22 секунды.. затем я использовал отдельное предложение в своем коррелированном запросе и перезапустил.. он также вернулся через 7 секунд.. так что ИСКЛЮЧИТЬ хорошо, когда вы не знаете или не знаете успейте точно настроить ваш запрос, в противном случае производительность будет одинаковой.

Answer 5

Не учитывается план выполнения SQL-сервера. Когда я сталкивался с проблемами производительности, я всегда обнаруживал, что это было совершенно произвольно (с точки зрения пользователя, я уверен, что авторы алгоритмов поймут, почему), когда один синтаксис создает лучший план выполнения, а не другой.

В этом случае что-то в сравнении параметров запроса позволяет SQL-серверу найти ярлык, который он не мог сделать из оператора прямого выбора. Я уверен, что это недостаток в алгоритме. Другими словами, вы можете логически интерполировать одно и то же, но алгоритм не выполняет этот перевод для существующего запроса. Иногда это происходит из-за того, что алгоритм, который может надежно его выяснить, может занять больше времени, чем сам запрос, или, по крайней мере, разработчик алгоритма так думал.