SQL "ON" Clause Optimization

Question

Which query would run faster?

SELECT * FROM topic_info
    LEFT JOIN topic_data ON topic_info.id = topic_data.id
WHERE id = ?

or

SELECT * FROM topic_info
    LEFT JOIN topic_data ON topic_data.id = topic_info.id
WHERE id = ?

The difference is the order of expressions on the "ON" clause: the first query is checking topic_info.id against topic_data.id, the second topic_data.id against topic_info. Which query would generally run faster?

(I know either query won't parse because of the ambiguous "id" column, but let's ignore that)

Answer 1

I don't think it should make a difference. Pick a convention and stick with it.

Answer 2

Modern dbs are smart enough so that it makes no difference in execution performance.

Answer 3

It probably doesn't matter, but do test it out.

If you use MySQL, try 'explain select ...' -- it'll tell you what you need to know.

Answer 4

Won't they return slightly different results?

Because its a left join, if there are rows in either table without linked records in the other, they'll not be retrieved when that table is on the right of the join clause.

Or have i misunderstood?

EDIT: I have misunderstood completely - just done some tests. Ta to the commenters for putting me straight.

Answer 5

Hi,

The other posters are correct, it does not matter which way round you express the join.

This is becuase they query optimizer will determine the most efficient method and of course query plan to use for your query. This will happen irrespective of your join statement ordering.

Hope this clears things up for you however please feel free to request additional details.

Cheers, John