问题描述
我有一个连接两个表的相对简单的查询.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.
显然，在使用 LEFT OUTER JOIN 的情况下，条件的位置确实会对返回的结果产生影响 - 当然，这些情况将被排除在我的建议之外.
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.
马克
这篇关于连接条件中的 SQL 过滤条件或更有效的 where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！