Не в vs не существует

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

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

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

когда подзапрос возвращает даже один null, NOT IN не будет соответствовать ни одному строки.

причину этого можно найти, посмотрев на детали того, что Не в действии на самом деле означает.

скажем, для иллюстрации, что есть 4 строки в таблица называется t, есть столбец с именем 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 равен нулю, где ID = 4. Следовательно, что != сравнение возвращает значение Unknown. Логическая таблица истинности для и состояний неизвестно, что и правда, неизвестно, неизвестно, а ложь-ложью. Есть нет значения, которое может быть AND'D с неизвестным, чтобы получить результат TRUE

следовательно, если какая-либо строка этого подзапроса возвращает NULL, то весь NOT IN оператор будет возвращать 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. Все вовлеченные таблицы имеют уникальный индекс на соответствующем .

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

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

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

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

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

Я был с помощью

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

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

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

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

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

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

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

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

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

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

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

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

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

в качестве примера, когда существует лучше

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