В чем разница между NOT EXISTS и NOT IN против LEFT JOIN, ГДЕ НУЛЬ?

Мне кажется, что вы можете сделать то же самое в SQL-запросе, используя NOT EXISTS, NOT IN или LEFT JOIN WHERE NULL. Например:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)

SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)

SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL

Я не уверен, правильно ли я понял весь синтаксис, но это общие приемы, которые я видел. Почему я решил бы использовать один над другим? Отличается ли производительность...? Какой из них самый быстрый / самый эффективный? (Если это зависит от реализации, когда я буду использовать каждый из них?)

Источник

5 ответов

Решение

В двух словах:

NOT IN немного отличается: он никогда не совпадает, если есть только один NULL в списке.

  • В MySQL, NOT EXISTS немного менее эффективен

  • В SQL Server, LEFT JOIN / IS NULL менее эффективен

  • В PostgreSQL, NOT IN менее эффективен

  • В OracleВсе три метода одинаковы.

Источник

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

Для простых ситуаций, подобных тем, о которых вы спрашиваете, различий не должно быть или почти не должно быть, поскольку все они будут выполняться как объединения. В более сложных запросах база данных может быть не в состоянии сделать соединение из not in а также not exists queryes. В этом случае запросы станут намного медленнее. С другой стороны, объединение может также работать плохо, если нет индекса, который можно использовать, так что если вы используете объединение, это еще не значит, что вы в безопасности. Вам нужно будет изучить план выполнения запроса, чтобы определить, могут ли быть проблемы с производительностью.

Источник

Предполагая, что вы избегаете пустых значений, они являются всеми способами написания анти-объединения с использованием стандартного SQL.

Очевидным упущением является эквивалент использования EXCEPT:

SELECT a FROM table1
EXCEPT
SELECT a FROM table2

Обратите внимание, что в Oracle вам нужно использовать MINUS оператор (возможно, лучшее имя):

SELECT a FROM table1
MINUS
SELECT a FROM table2

Говоря о проприетарном синтаксисе, могут быть и нестандартные эквиваленты, которые стоит изучить в зависимости от используемого вами продукта, например OUTER APPLY в SQL Server (что-то вроде):

SELECT t1.a
  FROM table1 t1
       OUTER APPLY 
       (
        SELECT t2.a
          FROM table2 t2
         WHERE t2.a = t1.a
       ) AS dt1
 WHERE dt1.a IS NULL;
Источник

С точки зрения производительности всегда избегайте использования обратных ключевых слов, таких как NOT IN, NOT EXISTS, ... Потому что для проверки обратных элементов СУБД необходимо просмотреть все доступные и отбросить обратный выбор.

Источник

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

Источник
Другие вопросы по тегам