Re: JOIN vs WHERE clause.
If you measure this, you will most likely discover that the two versions
use the exact same access plan. SQL Server tries very hard to optimize a
query, and in that process, a where clause which equates columns from two
tables will be converted to an inner join.
The second question must be answered in the time honoured fashion of "It
depends". In this case it depends on what the WHERE clause tests for.
Conceptually, the non joining conditions are evaluated after establishing
the result table of all the joins. In practice, the conditions are
usually evaluated as early as possible, to reduce the number of matching
rows elibible for join operations, as this improves performance.
The only way to know for sure is to test your specific query in Query
Analyzer and turn on "Show Execution Plan". Which is something the
performance aware developer should do routinely to uncover missing
indexes and faulty join clauses.
--
Rune Bivrin
- OOP since 1989
- SQL Server since 1990
- VB since 1991
"Igor" <irodionov@hotmail.com> wrote in
news:3e0c8fee$1@tnews.web.devx.com:
>
> Hi everybody,
> I have a performance issue question for you.
> I’ve got two tables TBL_ONE and TBL_TWO with primary key field on
> first table PR_KEY and foreign key field on second table FRGN_KEY. I
> would like to get some result data set from two tables, what should I
> use for better performance: “SELECT * from TBL_ONE INNER JOIN TBL_TWO
> ON PR_KEY=FRGN_KEY WHERE SOME_FIELD=’some_value’”
> or
> “SELECT * FROM TBL_ONE, TBL_TWO WHERE PR_KEY=FRNG_KEY AND
> SOME_FIELD=’some_value’”.
> And I guess there are two general questions here, first – is JOIN
> faster then WHERE clause, second – what is processed first on SQL
> Server WHERE clause or JOIN part of SQL statement(meaning when JOIN is
> used, does SQL Server joins tables first and then selects rows
> according the WHERE clause or first does WHERE and then tries to join
> result sets). Any input would be greatly appreciated.
> igor
>
>
Re: JOIN vs WHERE clause.
Keep in mind the JOIN statement is Ansi-92 defined standard, the join via
a WHERE clause is an "undocumented" standard that all of the DDMSs support.
For how long ???
Either should be processed through the analzer pretty much the same.
KlK, MCSE
Rune Bivrin <rune@bivrin.com> wrote:
>If you measure this, you will most likely discover that the two versions
>use the exact same access plan. SQL Server tries very hard to optimize a
>query, and in that process, a where clause which equates columns from two
>tables will be converted to an inner join.
>
>The second question must be answered in the time honoured fashion of "It
>depends". In this case it depends on what the WHERE clause tests for.
>Conceptually, the non joining conditions are evaluated after establishing
>the result table of all the joins. In practice, the conditions are
>usually evaluated as early as possible, to reduce the number of matching
>rows elibible for join operations, as this improves performance.
>
>The only way to know for sure is to test your specific query in Query
>Analyzer and turn on "Show Execution Plan". Which is something the
>performance aware developer should do routinely to uncover missing
>indexes and faulty join clauses.
>
>--
>Rune Bivrin
> - OOP since 1989
> - SQL Server since 1990
> - VB since 1991
>
>
>"Igor" <irodionov@hotmail.com> wrote in
>news:3e0c8fee$1@tnews.web.devx.com:
>
>>
>> Hi everybody,
>> I have a performance issue question for you.
>> I’ve got two tables TBL_ONE and TBL_TWO with primary key field on
>> first table PR_KEY and foreign key field on second table FRGN_KEY. I
>> would like to get some result data set from two tables, what should I
>> use for better performance: “SELECT * from TBL_ONE INNER JOIN TBL_TWO
>> ON PR_KEY=FRGN_KEY WHERE SOME_FIELD=’some_value’”
>> or
>> “SELECT * FROM TBL_ONE, TBL_TWO WHERE PR_KEY=FRNG_KEY AND
>> SOME_FIELD=’some_value’”.
>> And I guess there are two general questions here, first – is JOIN
>> faster then WHERE clause, second – what is processed first on SQL
>> Server WHERE clause or JOIN part of SQL statement(meaning when JOIN is
>> used, does SQL Server joins tables first and then selects rows
>> according the WHERE clause or first does WHERE and then tries to join
>> result sets). Any input would be greatly appreciated.
>> igor
>>
>>
>
