Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: Put selection criteria for driver table in first inner join clause or in where clause

    Which is better: putting selection criteria for the driver table in the first inner join clause or in the where clause?

    For instance:

    select dr.x ...
    from driver_table_t dr
    join driven_table_t1 dn1
    on dr.x = dn1.x
    join driven_table_t2 dn2
    on dn1.y = dn2.y
    and dn1.z = dn2.z
    where dr.a = 42
    and dr.b = 'stringvalue'
    and dr.c between min_date and max_date
    or

    select dr.x ...
    from driver_table_t dr
    join driven_table_t dn1
    on dr.x = dn1.x
    and dr.a = 42
    and dr.b = 'stringvalue'
    and dr.c between min_date and max_date
    join driven_table_t2 dn2
    on dn1.y = dn2.y
    and dn1.z = dn2.z

  2. #2
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    driver_table_t has 1M rows

    I am not sure if it will make a difference in the answer, but assume that driver_table_t has 1M rows.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would always put "driver table" conditions in the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    Thanks, ... but why? Just a coding convention?

    Actually, in the case I am dealing with now the driver table does have 1M+ rows, and both methods return the result data in about the same amount of time.

    I was thinking that putting the selection criteria in the 1st inner join clause would greatly narrow down the result set which is then joined with the 2nd driven table.

    Am I missing something?

    Thoughts?

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Look at the explain plan for both approaches and see if they differ.
    -=cf

  6. #6
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    the explain plan for both approaches is exactly the same ... we must be dealing with an intelligent query optimizer ...

    thanks for the help ...

Posting Permissions

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