August 27, 2008 at 3:04 pm
Hi all,
I have a basic question. Its not actually a question just want to know which is the right thing to do and why
For eg.
I have a join as below
select .... from table1 t1 inner join table2 t2 ON t1.col = t2.col and t1.col4 = 'sdsd' and t2.col5 = 'sdfsd'
OR
select .... from table1 t1 inner join table2 t2 ON t1.col = t2.col WHERE t1.col4 = 'sff' and t2.col5 = 'sdfsf '
Which is better?
I tend to normally seperate my ON and where clause but people tend to use everything in ON. But i am not sure if tht boosts performance. Wht do u guys feel?
Thanks
Kayal
August 27, 2008 at 3:14 pm
Just for readability I prefer the WHERE, but it should also be mentioned that the ON, according to Inside Microsoft SQL server 2005: T-SQL Querying is applied before the WHERE, so it could potentially change the results returned particularly when doing OUTER joins.
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
August 27, 2008 at 3:45 pm
It definately changes the results in outer joins. If the query was:
select .... from table1 t1 left outer join table2 t2 ON t1.col = t2.col and t1.col4 = 'sdsd' and t2.col5 = 'sdfsd'
the t2.col5 = 'sdfsd' would cause the same number of rows to be in the resultset but there would more likely be NULL values for columns in your SELECT clause from t2 where there wasn't a matching record to t1.
Putting that same condition in the WHERE clause will actually counter act the LEFT OUTER JOIN, and would reduce rows in the resultset to only records from t1 that did have a matching record in t2 that had 'sdfsd'
August 27, 2008 at 5:28 pm
For Inner JOINs there is no logical difference and the Query Optimizer is free to take advantage of that fact. So, usually there is no difference and if there is, it can be argued that this is a deficiency (bug) of the query optimizer.
Outer JOIN's are a different story, but only because the ON conditions have a different logical meaning than the WHERE conditions (the ON clauses are outer-join conditions whereas the WHERE clauses are inner-join type conditions).
Since it should not matter for Inner JOINs, best practice is to seperate them according to their meaning: conditions that are truly meant to constrain/define the Join should be in the ON, and conditions that are meant to filter the result set should be in the WHERE.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 7:41 am
I agree with Barry. Separate by function and by need. Makes it much easier to modify/debug later.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply