Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    melbourne
    Posts
    4

    Unanswered: inner join efficiency compaired with where

    I've been trying to find out if within mysql there is a difference in efficiency between an "inner join" and a join made within the where.

    eg.
    using an inner join
    SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.id

    or within the where
    SELECT * FROM table_a, table_b WHERE table_a.id = table_b.id

    -----------

    Does mysql handle these statements differently? and if so than which is better practice? and which is more efficient?

    Thanks,
    ebow

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i really don't know whether there is a difference in efficiency, but i would be hugely surprised if there is

    but as for better practice, the JOIN syntax is far better

    rudy

  3. #3
    Join Date
    Oct 2003
    Location
    melbourne
    Posts
    4
    cool, thanks for that. Till now I've only been using statements within the WHERE clause to join the tables (or LEFT JOIN where needed) but then spotted someone elses code using the INNER JOIN and was interested to know if there's a better way.

    Do you know if mysql actually deals with the queries differently?

    as in, it was suggested to me that when using an inner join mysql links all the tables together and then you refine that through your WHERE statements and that when joining withing the WHERE statement (eg. table_a.id = table_b.id) it loops through to do the joins? ... hmmm, I don't know if that makes sense ... but i don't know how to explain it any better sorry?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i dunno, man

    like i said, i don't really know the internals

    however, as i also mentioned in the article Subquery or join? (registration required, but it's free), it is more important to make sure you are getting the correct results than to worry about the internal efficiency of a piece of software that countless hundreds of man-years went into...


    rudy

  5. #5
    Join Date
    Oct 2003
    Location
    melbourne
    Posts
    4
    good call.

    Thanks for that. I'll have a read of your article.

    have a groovy day

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    I don't know which might be "more efficient" .. that's up to the query execution planner .. but I do know which one is more descriptive, and that is "a join." So that's the syntax I think you should use.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Oct 2003
    Location
    melbourne
    Posts
    4
    that's a good way of putting it "descriptive".

    yeah, you guys have convinced me to use these a lot more regularly.

    Thanks

Posting Permissions

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