November 13, 2008 at 2:34 pm
Im trying to optimzae my coding and just wondering if its ok to do most of my filtering conditions in my joins as opposed to the where clause. Ideally eliminating the where clause altogether assuming I can. Is their any benefit of filtering data on joins vs the where clause? For example
select *
from table a
inner join tabe b
on a.id=b.id
and a.column='x'
and a.column=x
vs
select *
from table a
inner join tabe b
on a.id=b.id
where a.column='x'
and a.column=x
November 13, 2008 at 2:42 pm
The Query processor applies filters on Where and JOin's at different times in the process. When you are doing INNER JOIN's you will probably be okay, but with OUTER JOIN's where you put your criteria can change the results.
In my opinion, Filter criteria should be put in the WHERE clause, except for when you are filtering the right side of a LEFT JOIN.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2008 at 2:44 pm
Makes sense. I just figured since the join clause is evaluated before the where clause it would be a good idea to do as much filtering as possible on it.
November 13, 2008 at 5:33 pm
craig (11/13/2008)
Makes sense. I just figured since the join clause is evaluated before the where clause it would be a good idea to do as much filtering as possible on it.
For inner joins - it won't matter at all. SQL Server will build the same execution plan (in most cases). I am not aware of any specific cases where the execution plan would be different - but, I am sure there are some.
As was stated before - for outer joins you have to place the filtering in the join clause, else the statements would be equivelant to an inner join anyways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply