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

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

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

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

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

Ответ 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

вернется:

Рекурсивная ссылка также разрешена в выражении 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 разработчики должны просто забыть его запретить.

Ответ 2

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

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

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

Ответ 3

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

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

Ответ 4

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

ИСКЛЮЧЕНИЕ может быть переписано с помощью NOT EXISTS. (ЗА ИСКЛЮЧЕНИЕМ ВСЕ можно переписать с помощью ROW_NUMBER и NOT EXISTS.)

Получите это от здесь

Ответ 5

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