NOT IN vs. НЕ СУЩЕСТВУЕТ

Какой из этих запросов быстрее?

НЕ СУЩЕСТВУЕТ:

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

ИЛИ НЕ В:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

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

Это основано на базе данных NorthWind.

[изменить]

Просто нашел эту полезную статью: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Я думаю, что буду придерживаться НЕ СУЩЕСТВУЮЩИХ.

Ответ 1

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

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

Если ни 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 -able, запрос затем становится

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] содержит любой NULL ProductId, не возвращает никаких результатов. См. Дополнительную шкалу антисоединения и счетчика строк, чтобы подтвердить это, добавленное к плану.

Если Products.ProductID также изменено, чтобы стать NULL -able, запрос затем станет

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)

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

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

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

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

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

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

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

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

Ответ 2

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

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

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

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

Причину этого можно найти, посмотрев подробности того, что NOT IN фактически означает.

Давайте, скажем, для иллюстрации, что в строке есть 4 строки таблица с именем t, theres столбец с именем ID со значениями 1..4
WHERE SomeValue NOT IN (SELECT AVal FROM t)
эквивалентно
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)
Далее скажем, что AVal NULL, где ID = 4. Следовательно, что = сравнение возвращает UNKNOWN. Логическая таблица истинности для состояний И что UNKNOWN и TRUE UNKNOWN, UNKNOWN и FALSE FALSE. Там есть нет значения, которое может быть ANDd с UNKNOWN для получения результата TRUE

Следовательно, если какая-либо строка этого подзапроса возвращает NULL, весь NOT IN оператор будет оценивать либо FALSE, либо NULL, и никакие записи не будут вернулся

Ответ 3

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

Ответ 4

Собственно, я считаю, что это было бы самым быстрым:

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

Ответ 5

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

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

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

Ответ 6

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

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

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

Ответ 7

NOT IN и NOT EXISTS не идентичны. Посмотрите на эту ссылку для разницы между ними: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Ответ 8

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

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)

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

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

Ответ 9

Это зависит.

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

Ответ 10

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

Ответ 11

SELECT  *
FROM    Table_1 e
WHERE NOT  EXISTS
        (
        SELECT  null
        FROM    Table_2 d
        WHERE   d.id = e.sokakid
        )