JOIN vs WHERE clause.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: JOIN vs WHERE clause.

  1. #1
    Igor Guest

    JOIN vs WHERE clause.


    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


  2. #2
    Rune Bivrin Guest

    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
    >
    >



  3. #3
    kevin knudson Guest

    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
    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center