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

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

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?

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

good call.

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

have a groovy day

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.

that's a good way of putting it "descriptive".

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

Thanks