SQL Server Performance Forum – Threads Archive

Back to basics – JOINs

My question is about how SQL server handles the joins and the WHERE clause. Let us assume we tblA and tblB, each having a few million rows. I join both the tables on Id. SELECT a.Name
FROM tblA AS a
INNER JOIN tblB AS B
ON A.Id = B.Id Now I want to add a filter to tblA. Is there a difference in the following 2 SQLs? Option1: SELECT a.Name
FROM tblA AS a
INNER JOIN tblB AS B
ON A.Id = B.Id
WHERE a.City = ‘LA’ Option2: SELECT a.Name
FROM tblA AS a
INNER JOIN tblB AS B
ON A.Id = B.Id
AND a.City = ‘LA’ Thanks for your time
cbarus

Actually there is no difference in the text itself, so I’ll assume the queries are the same [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Gaurav- There is a difference. In the first query "a.City = ‘LA’" is in the WHERE clause. In the second, it is in the join criteria. cbarus

I haven’t plugged these 2 statements into the QA but I would imagine that the Execution plans would be different (depending on whether the a.City column is usefully indexed or not. For your million row tables, would you mind posting the respective execution plans? Nathan H.O.
Moderator
SQL-Server-Performance.com

Sorry for overlooking the same. I think the plans should be the same but it would be intersting to see what reality is. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Plans are exactly the same. Just ran a test.

I just checked the plans for both and they seemed to be the same. I was being told by another team mate that query took less time after moving the condition from the WHERE clause to the JOIN. But I just ran both the queries and they take the same time. Thanks for your time folks.

Most of the time, the plans will be the same. I havent found a time when they werent, but if performance is an issue, its always worth checking the plan for each way.
Personally (and this is a personal preferance), for inner joins (left/right outer joins are a different story), I like to keep pure ‘where’ conditions in the where clause. To me, I find it more readable to have only join conditions in the join and where’s in the where. Makes it easier to debug. In left/right outer joins, if you need a where on the left outer or right (inner) then its easier to put it in the join clause than to put =x or is null in the where clause.

For more complicated queries it can be an improvement to put the where clauses with the appropriate join e.g. select a.*
from tblA a
inner join tblB b
inner join tblC c
on b.col2 = c.col2
and c.col3 = 23
on a.col1 = b.col1 can be much more performant than select a.*
from tblA a
inner join tblB b
inner join tblC c
on b.col2 = c.col2
on a.col1 = b.col1
where c.col3 = 23 Cheers
Twan

]]>