Я хочу выбрать строки в таблице, где первичный ключ находится в другой таблице. Я не уверен, должен ли я использовать JOIN или оператор IN в SQL Server 2005. Есть ли существенная разница в производительности между этими двумя SQL-запросами с большим набором данных (т.е. Миллионы строк)?
SELECT *
FROM a
WHERE a.c IN (SELECT d FROM b)
SELECT a.*
FROM a JOIN b ON a.c = b.d
Update:
В этой статье в моем блоге кратко излагаются как мой ответ, так и мои комментарии к другим ответам и показаны фактические планы выполнения:
SELECT *
FROM a
WHERE a.c IN (SELECT d FROM b)
SELECT a.*
FROM a
JOIN b
ON a.c = b.d
Эти запросы не эквивалентны. Они могут давать разные результаты, если ваша таблица b не сохраняется ключом (то есть значения b.d не являются уникальными).
Эквивалент первого запроса следующий:
SELECT a.*
FROM a
JOIN (
SELECT DISTINCT d
FROM b
) bo
ON a.c = bo.d
Если b.d есть UNIQUE и помечено как таковое (с UNIQUE INDEX или UNIQUE CONSTRAINT), то эти запросы идентичны и, скорее всего, будут использовать идентичные планы, так как SQL Server достаточно умен, чтобы принять это во внимание.
SQL Server может использовать один из следующих методов для запуска этого запроса:
Если на a.c есть индекс, d равен UNIQUE, а b относительно невелик по сравнению с a, тогда условие распространяется в подзапрос, а обычный INNER JOIN (с b ведущим)
Если на b.d есть индекс, а d не UNIQUE, то условие также распространяется и используется LEFT SEMI JOIN. Его также можно использовать для указанного выше условия.
Если есть индекс как для b.d, так и a.c, и они большие, то MERGE SEMI JOIN используется
Если в таблице нет индекса, то хеш-таблица построена на b и HASH SEMI JOIN.
Ни один из этих методов не переоценивает весь подзапрос каждый раз.
Более подробную информацию о том, как это работает, см. в этом блоге в моем блоге:
Есть ссылки для всех 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 из коррелированного подзапроса и в конечном итоге получить тот же план выполнения.
Изменить: Просьба прочитать комментарии ниже для дальнейшего... обсуждения обоснованности этого ответа и фактического ответа на вопрос ОП. =)
Говоря из опыта на столе с 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, и у вас есть намного больше возможностей настройки для того, как присоединиться, что выбрано, что нет и т.д.
Это разные запросы с разными результатами. С запросом IN вы получите 1 строку из таблицы "a" всякий раз, когда предикат совпадает. С запросом INNER JOIN вы получите строки * b всякий раз, когда условие соединения совпадает. Таким образом, со значениями в из {1,2,3} и b {1,2,2,3} вы получите 1,2,2,3 от JOIN и 1,2,3 от IN.
РЕДАКТИРОВАТЬ. Я думаю, вы можете найти здесь несколько ответов, которые дадут вам неправильное представление. Попробуйте протестировать его самостоятельно, и вы увидите, что это все прекрасные планы запросов:
create table t1 (t1id int primary key clustered)
create table t2 (t2id int identity primary key clustered
,t1id int references t1(t1id)
)
insert t1 values (1)
insert t1 values (2)
insert t1 values (3)
insert t1 values (4)
insert t1 values (5)
insert t2 values (1)
insert t2 values (2)
insert t2 values (2)
insert t2 values (3)
insert t2 values (4)
select * from t1 where t1id in (select t1id from t2)
select * from t1 where exists (select 1 from t2 where t2.t1id = t1.t1id)
select t1.* from t1 join t2 on t1.t1id = t2.t1id
Первые два плана идентичны. Последний план представляет собой вложенный цикл, это различие ожидается, потому что, как я уже упоминал выше, соединение имеет разную семантику.
От Документация MSDN о Основах Подзапроса:
Множество операторов Transact-SQL, которые включить подзапросы можно альтернативно формулируется как объединение. Другие вопросы можно задать только с помощью подзапросы. В Transact-SQL существует обычно нет разницы в производительности между выражением, которое включает подзапрос и семантически эквивалентный версия, которая этого не делает. Однако в в некоторых случаях, когда существование должно быть проверено, соединение дает лучше представление. В противном случае вложенные запрос должен обрабатываться для каждого результат внешнего запроса для обеспечения устранение дубликатов. В таком случаев, подход присоединения привел бы к лучшие результаты.
В приведенном примере вложенный запрос нужно обрабатывать только один раз для каждого внешнего результата запроса, поэтому не должно быть разницы в производительности. Проверка планов выполнения для обоих запросов должна подтвердить это.
Примечание. Хотя сам вопрос не указывал SQL Server 2005, я ответил на это предположение на основе тегов вопроса. Другие модули баз данных (даже разные версии SQL Server) могут не оптимизировать одинаково.
Соблюдайте план выполнения для обоих типов и делайте свои выводы. Если количество записей, возвращаемых подзапросом в инструкции "IN", очень мало, вариант IN почти наверняка медленнее.
Я бы использовал соединение и ставлю, что это будет намного быстрее, чем IN. Это предполагает, что, конечно, определены первичные ключи, что позволяет значительно ускорить индексирование.
В целом считалось, что соединение будет более эффективным, чем подзапрос IN; однако оптимизатор SQL * Server обычно не приводит к заметной разнице в производительности. Тем не менее, лучше всего использовать код, используя условия соединения, чтобы ваши стандарты соответствовали друг другу. Кроме того, если ваши данные и код когда-либо должны быть перенесены в будущем, механизм базы данных может не быть настолько прощающим (например, использование соединения вместо подзапроса IN делает огромную разницу в MySql).
Теория будет только доводить вас до таких вопросов. В конце дня вы захотите проверить оба запроса и посмотреть, что на самом деле работает быстрее. У меня были случаи, когда версия JOIN заняла минуту, а версия IN заняла менее секунды. У меня также были случаи, когда JOIN был быстрее.
Лично я, как правило, начинаю с версии IN, если знаю, что мне не нужны никакие поля из таблицы подзапросов. Если это начнет работать медленно, я буду оптимизировать. К счастью, для больших наборов данных переписывание запроса делает такую заметную разницу, что вы можете просто время откликать его у Query Analyzer и знать, что добиваетесь прогресса.
Удачи!
Я всегда был сторонником методологии IN. Эта ссылка содержит подробную информацию о тесте, проведенном в PostgresSQL. http://archives.postgresql.org/pgsql-performance/2005-02/msg00327.php