NOT IN vs NOT EXISTS

Я всегда по умолчанию не существует.

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

Когда ни продуктов.Кодтовараили[детали заказа].Кодтовара "разрешить" нуль еще не будут относиться одинаково к следующего запроса.

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 

Если [Детали Заказа].Кодтовара нулевой-может тогда запрос будет

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) 

Причина этого заключается в том, что правильная семантика, если [детали заказа] содержит нуль, `Кодтовара является не возвращают никаких результатов. Ознакомиться с дополнительной анти-полусоединение и число строк катушку, чтобы убедиться в этом, добавленная в план.

Если Продукты.Кодтовара также изменился и стал равен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) 

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

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

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

Это не единственно возможный план выполнения не в на нуль-в состоянии однако колонке. Эта статья показывает еще один для запроса к базе данных adventureworks2008`.

Для не на не нуль столбца или не существует против либо значение null или номера столбца значение null дает следующий план.

Когда колонна изменится на нуль-теперь не в плане выглядит

Это добавляет дополнительное внутреннее соединение оператора плану. Этот аппарат объяснены здесь. Это все есть для преобразования предыдущим один коррелируется поиск по индексу продаж.Таблицы salesorderdetail.Кодтовара = на два направлен в наружную строку. Еще одно на где продажи.Таблицы salesorderdetail.Кодтовара является null.

Как это под анти-полусоединение, если один возвращает ни одной строки второй искать не будет происходить. Однако если продаж.Таблицы salesorderdetail не содержит нуль, `Кодтовара, это позволит удвоить количество искать необходимые операции.

Также следует помнить, что не в это не равносильно тому, что не существует, когда дело доходит до нулевой.

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

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

В тех случаях, когда подзапрос возвращает даже одним нулем, а не в не по строкам.

причина этого может быть найден, глядя на детали, что не работает на самом деле означает.

допустим, для наглядности, что есть 4 строки в таблица называется Т, есть столбец с именем ID со значениями 1..4

где значение не в (Выбрать Авал от Т)

>есть эквивалент

где значение != (Выберите Авал от Т где id=1) > и Некоторое_значение != (Выберите Авал от Т где id=2) > и Некоторое_значение != (Выберите Авал от Т где id=3) > и Некоторое_значение != (Выберите Авал от Т где id=4)

давайте еще скажем, что Аваль имеет значение null где id = 4. Следовательно, что != возвращает сравнению неизвестна. Логическая таблица истинности для и государства что неизвестные и правда неизвестно, неизвестно, а ложь-ложью. Есть никакого значения, что может быть и что с неизвестными для получения результата правда

следовательно, если какие-либо строки, что подзапрос возвращает NULL, то все не в оператор будет возвращать false или null, а записи не будет вернулся

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

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

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

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

Не в заняло около 10 минут, не существует заняло 4 сек.

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

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

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

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

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

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

и обнаружил, что она дает неверные результаты (неправильных я имею в виду не результаты). Как был нулем в table2.На col1.

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

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

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

С тех пор я начал использовать не существует каждый где.

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

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

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

Это зависит от...

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