Results 1 to 6 of 6

Thread: Table Joins!

  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: Table Joins!

    Just curious if anyone has any in depth knowledge of how table join filtering works:

    SELECT col1
    FROM tbl1 a
    INNER JOIN tbl2 b
    ON a.col2 = b.col2
    AND a.col3 = 1

    (versus)

    SELECT col1
    FROM tbl1 a
    INNER JOIN tbl2 b
    ON a.col2 = b.col2
    WHERE a.col3 = 1

    Running some simple tests, the executions plans look identical. Does anyone know if/when either of these options would be preferential over the other? This is a rather difficult topic to Google and find any decent information.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The optimiser will (especially in a simple case) create identical plans for these. I would always (can't think of an exception right now...) put the column comparisons in the from clause and the absolute comparison in the where clause since that is logical to me. The only exception is if you use an outer join. Try looking at the different plans & returns if you change the inny to an outy

  3. #3
    Join Date
    Oct 2007
    Posts
    22
    Right on.

    Without even testing, using outer joins with both of these query types should actually produce different result sets since you are filtering at different levels.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You know more than you are letting on

  5. #5
    Join Date
    Oct 2007
    Posts
    22
    Heh.

    This really wasn't mean to be a 'help me!' thread. Someone asked me this question the other day and I honestly couldn't give them a good answer. Even after consulting with some of the other DBAs, the general consensus was "This is what <i>feels</i> right" or "Logically (without really knowing), it would seem that using query type X in scenario Y would be best."

    After giving the person who asked this question an answer that was specific to his query, it started me thinking. This whole topic seems like it could be rather profound, but no one seemed to have a good answer based on factual knowledge of the database engine rather than what "feels" right! My initial thoughts were in line with your first post, but again, that is just the way I think that it should work.

    If you have any more thoughts, or anyone else for that matter, I'd love to hear!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is defo no performance issue. In theory the optimiser should always treat those two examples identically. Having said that, sometimes in very complex queries logically identical expressions can lead to different physical evaluations.

    Ok - I thought of an exception too Inequality I would always put in the where clause (col1 <> col2).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •