连接条件或where子句中的SQL筛选器条件更有效 [英] SQL Filter criteria in join criteria or where clause which is more efficient

问题描述

我有一个连接两个表的相对简单的查询. "Where"条件可以用联接条件表示，也可以用where子句表示.我想知道哪个更有效.

I have a relatively simple query joining two tables. The "Where" criteria can be expressed either in the join criteria or as a where clause. I'm wondering which is more efficient.

查询是为了查找推销员从开始到晋升的最大销售额.

Query is to find max sales for a salesman from the beginning of time until they were promoted.

案例1

select salesman.salesmanid, max(sales.quantity)
from salesman
inner join sales  on salesman.salesmanid =sales.salesmanid 
                  and sales.salesdate < salesman.promotiondate
group by salesman.salesmanid 

案例2

select salesman.salesmanid, max(sales.quantity)
from salesman
inner join sales  on salesman.salesmanid =sales.salesmanid 
where sales.salesdate < salesman.promotiondate
group by salesman.salesmanid 

请注意案例1完全没有where子句

Note Case 1 lacks a where clause altogether

RDBMS是Sql Server 2005

RDBMS is Sql Server 2005

编辑 如果连接条件或where子句的第二部分是sales.salesdate<某个固定的日期，因此实际上并没有任何将两个表连接在一起的条件会改变答案.

EDIT If the second piece of the join criteria or the where clause was sales.salesdate < some fixed date so its not actually any criteria of joining the two tables does that change the answer.

推荐答案

在这里，我不会将性能用作决定因素-坦白地说，我认为这两种情况之间确实没有可测量的性能差异.

I wouldn't use performance as the deciding factor here - and quite honestly, I don't think there's any measurable performance difference between those two cases, really.

我将始终使用案例2-为什么?因为我认为，您应该只将在两个表之间建立JOIN的实际条件放入JOIN子句中-其他所有内容都属于WHERE子句.

I would always use case #2 - why? Because in my opinion, you should only put the actual criteria that establish the JOIN between the two tables into the JOIN clause - everything else belongs in the WHERE clause.

IMO，只是要保持物品整洁并放置在其所属的地方即可.

Just a matter of keeping things clean and put things where they belong, IMO.

很明显，在某些情况下，使用左外部联接的条件的确在返回结果方面有所不同-当然，这些情况将从我的建议中排除.

Obviously, there are cases with LEFT OUTER JOINs where the placement of the criteria does make a difference in terms of what results get returned - those cases would be excluded from my recommendation, of course.

马克

这篇关于连接条件或where子句中的SQL筛选器条件更有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！