Есть ли способ переписать операторы EXCEPT в операторы NOT IN в SQL?

Question

Я несколько недель изучаю SQL и только что выполнил задание по домашнему заданию об использовании IN и NOT IN. Мне удалось получить правильный ответ, однако я использовал предложение EXCEPT, которое нам пока не разрешено использовать. Из того, что я могу сказать, EXCEPT и NOT IN - очень похожие операторы в SQL, но я не могу понять, в чем разница. Вот общий формат моего запроса:

SELECT *
FROM table
WHERE x IN (
    SELECT x
    /* ... some subquery*/
    EXCEPT
    SELECT x
    /* ... some other subquery*/ 
)

Есть ли способ переписать этот общий запрос без использования оператора EXCEPT? Чем EXCEPT и NOT IN отличаются друг от друга в целом?

Редактировать: Этот другой пост, кажется, содержит некоторую полезную информацию, но, похоже, он сосредоточен на СУЩЕСТВУЮЩЕМ, а не на В, что имеет разные цели, не так ли?

Answer 1

Это может помочь вам понять разницу между исключением и не в

Оператор EXCEPT возвращает все отдельные строки из левой таблицы, которая не существует в правой таблице.

С другой стороны, "NOT IN" вернет все строки из левой таблицы, которых нет в правой таблице, но не удалит дублирующиеся строки из результата.

Answer 2

SQL является декларативным языком. В результате есть много конструкций, которые могут переводиться в одно и то же дерево реляционной алгебры / план выполнения. Например, вы можете указать внутреннее соединение, используя INNER JOIN или CROSS JOIN + WHERE или, + WHERE или LEFT / RIGHT JOIN + WHERE.

EXCEPT / INTERSECT - операторы установки, а IN/NOT IN - предикаты. Рассмотрим ваш пример,

SELECT *
FROM table
WHERE x IN (
    SELECT x
    /* ... some subquery*/
    EXCEPT
    SELECT x
    /* ... some other subquery*/ 
)

Это можно записать с помощью IN/NOT IN, например:

SELECT *
FROM table as t1
WHERE t1.x IN (
    SELECT t2.x
    FROM t2
    WHERE t2.x NOT IN (
                      SELECT t3.x
                      FROM t3
    )
)

Теперь с точки зрения синтаксиса они означают одно и то же, но семантически они могут не давать одинаковых результатов. Например, если столбец X имеет значение NULL, то NOT IN будет давать разные результаты.

Наконец, для простого случая, если вы посмотрите на план выполнения в SQL Server Management Studio для этих двух запросов, вы обнаружите, что они используют сходные стратегии планов / объединений. Это связано с тем, что SQL Server преобразует синтаксис в реляционное дерево, оптимизированное для создания плана выполнения. После этого перевода окончательный план может быть идентичным для разных запросов.