性能：WHERE子句中的条件与INNER JOIN中的条件？ [英] Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?

问题描述

有一条经验法则在SQL中更好/更快/更高效吗

Server 2005？


a）SELECT *来自内部联合B ON B.ID = A.ID和B.Cond1 = 1 AND

B.Cond2 = 2

b ）SELECT * FROM INNER JOIN B ON B.ID = A.ID WHERE B.Cond1 = 1 AND

B.Cond2 = 2


这是一个非常简单的样本。我经常有三个或更多的内部联合的案例，每个案例都有不同的条件。逻辑上我会说b $ b表示将条件放到JOIN语句更快，因为它会减少联接数据的数量，而b）会加入所有内容并且

然后整理那些与WHERE条件不匹配的东西。

$ b $bRené

解决方案

SQL Server查询优化器可以自由地在查询计划中上下移动表达式，以实现成本优化的检索数据计划。

对于INNER JOIN，它是非常典型的移动JOIN

和WHERE子句之间的谓词，你最终会得到相同的执行计划

两个查询。


- -

Plamen Ratchev
http://www.SQLStudio.com

René写道：

>




有经验法则什么是更好/更快/ SQL中性能更高

Server 2005？

a）SELECT *来自内部联接B B.ID = A.ID和B.Cond1 = 1 AND

B.Cond2 = 2

b）SELECT *来自内部联接B B.ID = A.ID何处B.Cond1 = 1 AND

B.Cond2 = 2

这是一个非常简单的样本。我经常有三个或更多的内部联合的案例，每个案例都有不同的条件。逻辑上我会说b $ b表示将条件放到JOIN语句更快，因为它会减少联接数据的数量，而b）会加入所有内容并且

然后挑选那些与WHERE条件不匹配的东西。

$ b $bRené

如Plamen所述，来自从性能角度来看，没有

的差异。


我得出的结论是，我的首选是我只能列出
$ b ON子句中的$ b外键列，以及

WHERE子句中的所有其他过滤器。这种方法使查询易于阅读，并且查询的下半部分中的真实

过滤条件。


当然，对于外部联接，这是一个不同的故事...


-

Gert-Jan

SQL Server MVP

>取代。逻辑上我会说将条件放到JOIN语句更快，因为它减少了连接数据的数量，而b）将加入所有内容，然后整理那些与WHERE条件不匹配的条件。 <


对于INNER JOIN来说无所谓。优化器将重新安排

的东西，并在99.999％的

时间内提出相同的执行计划。


重要的是维护代码是多么容易。访问

程序员和其他使用文件系统的人，比如

，将连接条件放在ON子句中，过滤器放在WHERE
$ b $中b条款。这让他们想象成对的磁带合并按顺序完成，然后在最后一步之前完成报告。


年龄较大的SQL程序员往往不使用INNER JOIN所有的语法

，因为它让我们在WHERE子句中看到n-ary关系，而我们可以想象一种更简单的数据序列，而不是简单的

二元运算符。


这种心态有点难以解释，但它像是某个人b / b想到了一串+'' s和想到大西格玛的人当

他们做了总结。

Hi,

is there a rule of thumb what is better/faster/more performant in SQL
Server 2005?

a) SELECT * FROM A INNER JOIN B ON B.ID = A.ID AND B.Cond1 = 1 AND
B.Cond2 = 2
b) SELECT * FROM A INNER JOIN B ON B.ID = A.ID WHERE B.Cond1 = 1 AND
B.Cond2 = 2

This is a very simple sample. I often have cases with three or more
INNER JOINs each of them having different conditions. Logically I''d
say that putting the conditions to the JOIN statement is faster as it
reduces the amount of joined data whereas b) would join everything and
then sort out those not matching the WHERE conditions.

René

解决方案

The SQL Server query optimizer is free to move expressions up and down
within a query plan to achieve cost optimized plan for retrieving data.
For INNER JOIN it is very typical to move predicates between the JOIN
and WHERE clauses and you will end up with the same execution plan for
both queries.

--
Plamen Ratchev
http://www.SQLStudio.com

René wrote:

>
Hi,

is there a rule of thumb what is better/faster/more performant in SQL
Server 2005?

a) SELECT * FROM A INNER JOIN B ON B.ID = A.ID AND B.Cond1 = 1 AND
B.Cond2 = 2
b) SELECT * FROM A INNER JOIN B ON B.ID = A.ID WHERE B.Cond1 = 1 AND
B.Cond2 = 2

This is a very simple sample. I often have cases with three or more
INNER JOINs each of them having different conditions. Logically I''d
say that putting the conditions to the JOIN statement is faster as it
reduces the amount of joined data whereas b) would join everything and
then sort out those not matching the WHERE conditions.

René

As mentioned by Plamen, from a performance perspective, there is no
difference.

I have come to the conclusion that I my preference is to only list the
foreign key column(s) in the ON clause, and all other filters in the
WHERE clause. This approach makes the query easy to read, and the "real"
filtering condition at all in the lower part of the query.

Of course, for Outer Joins it is a different story...

--
Gert-Jan
SQL Server MVP

>>. Logically I''d say that putting the conditions to the JOIN statement is faster as it reduces the amount of joined data whereas b) would join everything and then sort out those not matching the WHERE conditions. <<

For INNER JOINs it does not matter. The optimizer will re-arrange
things and come up with the same execution plan in 99.999% of the
time.

What matters is how easy it is to maintain the code. ACCESS
programmers and other people who have worked with file systems like to
put the join conditions in the ON clauses and the filters in the WHERE
clause. This lets them imagine pairwise tape merges done in sequence
before the final step to get the report out.

Older SQL programmers tend not to use the INNER JOIN syntax at all
because it lets us see n-ary relationships in the WHERE clause and we
can imagine a more general approach to data than a simply sequence of
binary operators.

That mindset is a little hard to explain, but it like someone who
thinks of a chain of +''s and someone who thinks of "Big Sigma" when
they do a summation.

这篇关于性能：WHERE子句中的条件与INNER JOIN中的条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！