IN против JOIN с большими наборами строк

Обновить:

Эта статья в моем блоге обобщает как мой ответ, так и мои комментарии к другим ответам и показывает фактические планы выполнения:

• IN против JOIN и EXISTS

---

SQL Server

Эти запросы не эквивалентны. Они могут давать разные результаты, если ваша таблица bне сохранена в ключе (т.е. значения SQL Serverне являются переменными).

Эквивалент первого запроса следующий:

d

Если UNIQUEесть bи помечены как таковые (с помощью aor или INNER JOIN), то эти запросы идентичны и, скорее всего, будут использовать одинаковые планы, поскольку bдостаточно умен, чтобы принять это во внимание.

SQL Server может использовать один из следующих методов для запуска этого запроса:

• Если есть индекс on b.d, dis dи bотносительно небольшой по сравнению с этим a, тогда условие распространяется в подзапрос, а plain UNIQUEиспользуется (с bведущим)

• Если есть индекс LEFT SEMI JOINи dнет UNIQUE, то условие также распространяется и b.dиспользуется. Его также можно использовать для вышеуказанного условия.

• Если есть индекс на обоих a.cи MERGE SEMI JOINони велики, то bиспользуется

• Если в любой таблице нет индекса, то используется хэш-таблица bи HASH SEMI JOINиспользуется.

Ни один из этих методов не пересматривает весь подзапрос каждый раз.

См. Эту запись в своем блоге для более подробной информации о том, как это работает:

• Подсчет отсутствующих строк: SQL Server

Есть ссылки для всех RDBMSиз четырех.

Ни. Используйте ANSI-92 JOIN:

SELECT a.*
FROM a JOIN b a.c = b.d

Однако, это лучше всего как EXISTS

SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE a.c = b.d)

Это удалит дубликаты, которые могут быть созданы JOIN, но работает так же быстро, если не быстрее

IN оценивается (и выбор из b повторного запуска) для каждой строки в a, тогда как JOIN оптимизирован для использования индексов и других опрятных трюков подкачки ...

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

Изменить: Просьба прочитать комментарии ниже для дальнейшего ... обсуждения обоснованности этого ответа и фактического ответа на вопрос OP. знак равно

Говоря по опыту на столе с 49 000 000 строк, я бы порекомендовал LEFT OUTER JOIN. Использование IN или EXISTS. Захватили 5 минут, чтобы завершить LEFT OUTER JOIN за 1 секунду.

SELECT a.*
FROM a LEFT OUTER JOIN b ON a.c = b.d
WHERE b.d is not null -- Given b.d is a primary Key with index

Фактически в моем запросе я делаю это через 9 таблиц.

Помимо того, что я собираюсь и фактически тестирую его на большом количестве тестовых данных для себя, я бы сказал, используя JOINS. У меня всегда была лучшая производительность, использующая их в большинстве случаев по сравнению с подзапросом IN, и у вас есть намного больше возможностей настройки, насколько они связаны, что выбрано, что нет и т. Д.