|
Home > Archive > MS SQL Server > October 2006 > Criteria in Inner Join clause
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Criteria in Inner Join clause
|
|
| cbrichards via SQLMonster.com 2006-10-24, 6:38 pm |
| Is there any performance benefit by including filter criteria in an Inner
Join, rather than in the Where clause?
For instance, would there be any performance benefit in one over the other in
the following two samples?
SAMPLE #1
SELECT ...
FROM dbo.Table1 T1
JOIN dbo.Table2 T2
ON T2.Table1FID = T1.Table1UID
AND T2.TableKey = T1.TableKey
WHERE T1.Status < 10
AND T1.ChangeDate > '01/01/2006'
AND T2.TableKey = 1000
SAMPLE #2
SELECT ...
FROM dbo.Table1 T1
JOIN dbo.Table2 T2
ON T2.Table1FID = T1.Table1UID
AND T2.TableKey = T1.TableKey
AND T1.Status < 10
AND T1.ChangeDate > '01/01/2006'
WHERE T2.TableKey = 1000
While these are made up queries, I have run similar queries, rewritten like
the above and each returns identical explain plans. While my tests may or may
not be conclusive, I was wondering for a more official response/answer to
this question. Thanks.
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200610/1
| |
| Tracy McKibben 2006-10-24, 6:38 pm |
| cbrichards via webservertalk.com wrote:
> Is there any performance benefit by including filter criteria in an Inner
> Join, rather than in the Where clause?
>
> For instance, would there be any performance benefit in one over the other in
> the following two samples?
>
> SAMPLE #1
> SELECT ...
> FROM dbo.Table1 T1
> JOIN dbo.Table2 T2
> ON T2.Table1FID = T1.Table1UID
> AND T2.TableKey = T1.TableKey
> WHERE T1.Status < 10
> AND T1.ChangeDate > '01/01/2006'
> AND T2.TableKey = 1000
>
>
> SAMPLE #2
> SELECT ...
> FROM dbo.Table1 T1
> JOIN dbo.Table2 T2
> ON T2.Table1FID = T1.Table1UID
> AND T2.TableKey = T1.TableKey
> AND T1.Status < 10
> AND T1.ChangeDate > '01/01/2006'
> WHERE T2.TableKey = 1000
>
> While these are made up queries, I have run similar queries, rewritten like
> the above and each returns identical explain plans. While my tests may or may
> not be conclusive, I was wondering for a more official response/answer to
> this question. Thanks.
>
In the case of an INNER join, no, there is no difference between WHERE
conditions and ON conditions. You can verify this by comparing the
execution plans of the two different queries.
Other join types, however, will behave differently with conditions in
the WHERE clause vs. the ON clause.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|
|
|
|
|