August 22, 2002 at 8:04 am
I was curious to see if there was any benefit from using a select criteria in a join as opposed to a where clause or vice versa
for example
select a,b
from table1 first
join table2 second on (first.a=second.a
and
first.create_date < getdate())
or
select a,b
from table1 first
join table2 second on (first.a=second.a)
where first.create_date < getdate())
Any 'best practices' suggestions would be helpful also. thankx
August 22, 2002 at 9:27 am
I would never use the second notation, since it looks a bit obscure to me.
But then again, your question was about the performance, so it set me thinking. What would be the performance of the following statement?
select a,b
from (select * from table1 where create_date < getdate()) first
join table2 second on first.a=second.a
August 22, 2002 at 9:36 am
You'd have to check the execution plan, but the Query Processor should reduce these two to roughly the same thing. The intent in SQL Server is to get the same plan for the same query, no matter how it is written.
Steve Jones
August 23, 2002 at 4:52 am
It is hard to say. Doing it in the ON actually may speed the compile time slightly but in a two table situation the would be almost insignificant. However adding additional tables may show an improvement by somewhat forcing the Query Processor to make a follow a specific way with needing to decide. Also, when you have a left or right join you may have specific need for option 1, for instance if table1 creatdate < getdate only matters when joining to table2 then join a table3 in and doing the createdate < getdate() may not produce the correct results. However an inner join will produce the same results either way the only difference may be in the time it takes would can be unnoticeable or comletely shocking.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply