НЕ В И НЕ СУЩЕСТВУЕТ

Если планировщик выполнения говорит, что они одинаковые, значит, они такие же. Используйте то, что сделает ваше намерение более очевидным - в данном случае второе.

По-разному..

SELECT x.col
FROM big_table x
WHERE x.key IN( SELECT key FROM really_big_table );

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

Но, в зависимости от оптимизатора СУБД, это не могло быть иначе.

Как пример, когда EXISTS лучше

SELECT x.col
FROM big_table x
WHERE EXISTS( SELECT key FROM really_big_table WHERE key = x.key);
  AND id = very_limiting_criteria

На самом деле, я считаю, что это будет самым быстрым:

SELECT ProductID, ProductName 
    FROM Northwind..Products p  
          outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null

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

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

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

Если оптимизатор говорит, что они одинаковы, учитывайте человеческий фактор. Я предпочитаю видеть НЕ СУЩЕСТВУЕТ :)

Также имейте в виду, что NOT IN не эквивалентно NOT EXISTS, когда дело доходит до null.

Этот пост очень хорошо это объясняет

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

When the subquery returns even one null, NOT IN will not match any rows.

The reason for this can be found by looking at the details of what the NOT IN operation actually means.

Let’s say, for illustration purposes that there are 4 rows in the table called t, there’s a column called ID with values 1..4

WHERE SomeValue NOT IN (SELECT AVal FROM t)

is equivalent to

WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1)
AND SomeValue != (SELECT AVal FROM t WHERE ID=2)
AND SomeValue != (SELECT AVal FROM t WHERE ID=3)
AND SomeValue != (SELECT AVal FROM t WHERE ID=4)

Let’s further say that AVal is NULL where ID = 4. Hence that != comparison returns UNKNOWN. The logical truth table for AND states that UNKNOWN and TRUE is UNKNOWN, UNKNOWN and FALSE is FALSE. There is no value that can be AND’d with UNKNOWN to produce the result TRUE

Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned

Я всегда по умолчанию использую NOT EXISTS.

Планы выполнения могут быть такими же в настоящий момент, но если любой столбец будет изменен в будущем, чтобы разрешить NULL, версия NOT IN должна будет выполнять больше работы (даже если в данных фактически нет NULL) и семантика NOT IN, если NULLs являются в любом случае вряд ли будут теми, которые вам нужны.

Если ни Products.ProductID, ни [Order Details].ProductID не разрешают NULL, NOT IN будет обрабатываться идентично следующему запросу.

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId) 

Точный план может отличаться, но для моих данных примера я получил следующее.

Довольно распространенное заблуждение состоит в том, что коррелированные подзапросы всегда «плохи» по сравнению с объединениями. Они, безусловно, могут быть такими, когда они форсируют план вложенных циклов (подзапрос, оцениваемый строка за строкой), но этот план включает логический оператор против полусоединения. Анти-полусоединения не ограничиваются вложенными циклами, но также могут использовать хэш-соединения или объединения слиянием (как в этом примере).

/*Not valid syntax but better reflects the plan*/ 
SELECT p.ProductID,
       p.ProductName
FROM   Products p
       LEFT ANTI SEMI JOIN [Order Details] od
         ON p.ProductId = od.ProductId 

Если [Order Details].ProductID поддерживает NULL, запрос становится

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL) 

Причина этого в том, что правильная семантика, если [Order Details] содержит какие-либо NULLProductId, - это не возвращать никаких результатов. См. Дополнительную катушку анти-полусоединения и счетчика строк, чтобы убедиться, что это добавлено в план.

Если Products.ProductID также будет изменен на NULL, запрос станет

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL)
       AND NOT EXISTS (SELECT *
                       FROM   (SELECT TOP 1 *
                               FROM   [Order Details]) S
                       WHERE  p.ProductID IS NULL) 

Причина в том, что NULLProducts.ProductId не должен возвращаться в результатах Кроме, если подзапрос NOT IN вообще не должен возвращать результатов (т.е. таблица [Order Details] пуста). В таком случае это должно быть. В плане для моих образцов данных это реализовано путем добавления еще одного анти-полусоединения, как показано ниже.

Эффект этого показан в сообщение в блоге, на которое уже ссылается Бакли. В этом примере количество логических чтений увеличилось с 400 до 500 000.

Кроме того, тот факт, что один NULL может уменьшить количество строк до нуля, очень затрудняет оценку количества элементов. Если SQL Server предполагает, что это произойдет, но на самом деле в данных не было строк NULL, остальная часть плана выполнения может быть катастрофически хуже, если это просто часть более крупного запроса, с несоответствующими вложенными циклами, вызывающими повторное выполнение дорогостоящего поддерева, например.

Однако это не единственный возможный план выполнения для NOT IN на колонке с поддержкой NULL. В этой статье показан еще один для запроса к базе данных AdventureWorks2008.

Для NOT IN в столбце NOT NULL или NOT EXISTS для столбца, допускающего или не допускающего значения NULL, он дает следующий план.

Когда столбец изменится на NULL, план NOT IN теперь будет выглядеть так:

Он добавляет в план дополнительный внутренний оператор соединения. Это устройство объяснено здесь. Все это необходимо для преобразования предыдущего поиска по одному коррелированному индексу на Sales.SalesOrderDetail.ProductID = <correlated_product_id> в два поиска на внешнюю строку. Дополнительный стоит на WHERE Sales.SalesOrderDetail.ProductID IS NULL.

Поскольку это находится под анти-полусоединением, если оно возвращает какие-либо строки, второй поиск не произойдет. Однако, если Sales.SalesOrderDetail не содержит NULLProductID, это удвоит количество требуемых операций поиска.

Я использовал

SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)

и обнаружил, что он дает неправильные результаты (под неправильным я имею в виду отсутствие результатов). Поскольку в TABLE2.Col1 было NULL.

При изменении запроса на

SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)

дал мне правильные результаты.

С тех пор я начал везде использовать НЕ СУЩЕСТВУЕТ.

У меня есть таблица, в которой около 120 000 записей, и мне нужно выбрать только те, которые не существуют (сопоставлены со столбцом varchar) в четырех других таблицах с количеством строк примерно 1500, 4000, 40000, 200. Все задействованные таблицы имеют уникальный индекс на соответствующей колонке Varchar.

NOT IN занял около 10 минут, NOT EXISTS - 4 секунды.

У меня есть рекурсивный запрос, в котором может быть какой-то ненастроенный раздел, который, возможно, способствовал 10 минутам, но другой вариант, занимающий 4 секунды, объясняет, по крайней мере, для меня, что NOT EXISTS намного лучше или, по крайней мере, IN и EXISTS не совсем то же самое и всегда стоит проверить, прежде чем приступать к написанию кода.

Они очень похожи, но не совсем одинаковы.

С точки зрения эффективности, я обнаружил, что оператор левое соединение равно нулю более эффективен (когда нужно выбрать большое количество строк)

Модель таблицы базы данных

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

Таблица student является родительской, а student_grade - дочерней, поскольку в ней есть столбец внешнего ключа student_id, ссылающийся на столбец первичного ключа id в таблице student.

student table содержит следующие две записи:

| id | first_name | last_name | admission_score |
|----|------------|-----------|-----------------|
| 1  | Alice      | Smith     | 8.95            |
| 2  | Bob        | Johnson   | 8.75            |

В таблице student_grade хранятся оценки, полученные учащимися:

| id | class_name | grade | student_id |
|----|------------|-------|------------|
| 1  | Math       | 10    | 1          |
| 2  | Math       | 9.5   | 1          |
| 3  | Math       | 9.75  | 1          |
| 4  | Science    | 9.5   | 1          |
| 5  | Science    | 9     | 1          |
| 6  | Science    | 9.25  | 1          |
| 7  | Math       | 8.5   | 2          |
| 8  | Math       | 9.5   | 2          |
| 9  | Math       | 9     | 2          |
| 10 | Science    | 10    | 2          |
| 11 | Science    | 9.4   | 2          |

SQL СУЩЕСТВУЕТ

Допустим, мы хотим собрать всех учеников, получивших 10 баллов по математике.

Если нас интересует только идентификатор студента, мы можем выполнить такой запрос:

SELECT
    student_grade.student_id
FROM
    student_grade
WHERE
    student_grade.grade = 10 AND
    student_grade.class_name = 'Math'
ORDER BY
    student_grade.student_id

Но приложение заинтересовано в отображении полного имени student, а не только идентификатора, поэтому нам также нужна информация из таблицы student.

Чтобы отфильтровать записи student, которые имеют 10 баллов по математике, мы можем использовать оператор EXISTS SQL, например:

SELECT
    id, first_name, last_name
FROM
    student
WHERE EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade = 10 AND
        student_grade.class_name = 'Math'
)
ORDER BY id

При выполнении запроса выше мы видим, что выбрана только строка Алисы:

| id | first_name | last_name |
|----|------------|-----------|
| 1  | Alice      | Smith     |

Внешний запрос выбирает столбцы строки student, которые мы хотим вернуть клиенту. Однако предложение WHERE использует оператор EXISTS со связанным внутренним подзапросом.

Оператор EXISTS возвращает true, если подзапрос возвращает хотя бы одну запись, и false, если строка не выбрана. Механизм базы данных не должен полностью выполнять подзапрос. Если совпадает одна запись, оператор EXISTS возвращает true, и выбирается связанная другая строка запроса.

Внутренний подзапрос коррелирован, потому что столбец student_id таблицы student_grade сопоставляется со столбцом id внешней таблицы student.

SQL НЕ СУЩЕСТВУЕТ

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

Следовательно, оператор NOT EXISTS возвращает истину, если базовый подзапрос не возвращает записи. Однако, если внутреннему подзапросу соответствует одна запись, оператор NOT EXISTS вернет false, и выполнение подзапроса может быть остановлено.

Чтобы сопоставить все записи учеников, не связанные с student_grade, со значением ниже 9, мы можем выполнить следующий SQL-запрос:

SELECT
    id, first_name, last_name
FROM
    student
WHERE NOT EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id

При выполнении запроса выше мы видим, что соответствует только запись Алисы:

| id | first_name | last_name |
|----|------------|-----------|
| 1  | Alice      | Smith     |

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