在与JOIN与大型行集在与JOIN与大型行集(IN vs. JOIN with large rows

我想选择表中的行，其中主键是另一个表。我不知道我是否应该使用JOIN或SQL Server 2005中的IN操作符是有一个大的数据集（即百万行）这两个SQL查询之间的任何显著的性能差异？

SELECT *
FROM a
WHERE a.c IN (SELECT d FROM b)

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

Answer 1:

更新：

这篇文章在我的博客总结我的回答，我的评论到另一个答案两者，显示实际执行计划：

在与JOIN与EXISTS

SELECT  *
FROM    a
WHERE   a.c IN (SELECT d FROM b)

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

这些查询是不等价的。它们可以产生不同的结果，如果你的表b不保存键（IE的值bd不是唯一的）。

第一个查询相对应的是以下情况：

SELECT  a.*
FROM    a
JOIN    (
        SELECT  DISTINCT d
        FROM    b
        ) bo
ON      a.c = bo.d

如果bd是UNIQUE和标注，（用UNIQUE INDEX或UNIQUE CONSTRAINT ），则这些查询是相同的，将最有可能使用相同的计划，因为SQL Server是足够聪明，考虑到这一点。

SQL Server可以采用下面的方法来运行此查询之一：

如果有关于索引ac ， d是UNIQUE和b相比是相对较小的a ，则该条件被传播到子查询和平原INNER JOIN使用时（用b领先）
如果有关于索引bd和d不UNIQUE ，则条件也传播和LEFT SEMI JOIN被使用。它也可以用于上面的条件。
如果有两个指标bd和ac ，他们都很大，那么MERGE SEMI JOIN使用
如果有任何表没有索引，那么哈希表是建立在b和HASH SEMI JOIN使用。

这些方法都没有重新评估每一次整个子查询。

见我的博客这个条目上如何工作的更多详细信息：

计数缺少的行：SQL服务器

有所有链接RDBMS “四大号第

Answer 2:

都不是。使用ANSI-92连接：

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生成重复，但速度一样快如果不是更快

Answer 3:

中评估（与从B再运行SELECT），用于在每一行，而该连接是优化使用索引和其他整齐的呼叫技巧...

在大多数情况下，虽然，优化器将有可能能够构建一个JOIN了相关子查询，并用相同的执行计划最终反正。

编辑：请阅读下面的意见作进一步......关于这个答案的有效性，实际答案OP的问题讨论。 =）

Answer 4:

从经验中谈到的表49000000行，我会建议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桌。

Answer 5:

除了去和实际测试它在自己的测试数据的一个大的大片，我会说使用连接。我总是在大多数情况下使用它们相比，IN子有更好的表现，你有更多的自定义选项就如何加入，什么是选择，什么不是，等等。

Answer 6:

他们用不同的结果不同的查询。随着IN查询您将获得1行从表中的“A”每当谓语一致。随着INNER JOIN查询，你会得到A * B行，只要连接条件匹配。因此，与在的{1,2,3}值和B {1,2,2,3}，你会得到从JOIN 1,2,2,3和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

前两个计划是相同的。最后的计划是一个嵌套的循环，这种差异是预期的，因为正如我所提到上述连接具有不同的语义。

Answer 7:

从MSDN文档的子查询基本面：

许多包含子查询的Transact-SQL语句可以交替配制成联接。其他问题只能用子查询构成。在Transact-SQL，通常包括一个子查询，并且没有一个语义上等效版本的声明之间不存在性能差异。然而，在某些情况下，存在必须进行检查，一个连接性能更好。否则，该嵌套查询必须为外部查询的每个结果进行处理，以确保消除重复的。在这种情况下，加入的方法会产生更好的效果。

在您所提供的示例中，嵌套查询只需要处理的每个外部查询结果的一个时间，所以应该不存在性能差异。检查两个查询执行计划应该确认这一点。

注：虽然这个问题本身没有指定SQL Server 2005中，我回答了基于问题的标签这样的假设。其他的数据库引擎（甚至不同的SQL Server版本）可能不以同样的方式进行优化。

Answer 8:

观察这两种类型的执行计划，并得出结论的。除非通过在“IN”语句子查询返回的记录数量非常少，则在方案几乎可以肯定慢。

Answer 9:

我会用一个连接，赌这将是一个很大赫克比快。这假定有限定，当然主键，从而让索引速度东西极大。

Answer 10:

它通常认为，一个连接会比IN子查询效率更高; 然而，SQL * Server优化通常会导致没有明显的性能差异。即便如此，它可能是最好使用连接条件，让您的标准相一致的代码。另外，如果您的数据和代码以往任何时候都需要在未来进行迁移，数据库引擎可能不会（使用连接而不是IN子使得MySQL中的巨大差异为例）是如此宽容。