SQL "ON" Clause Optimization

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)

From stackoverflow

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

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

  Tomalak : I don't think that "smartness" and "modern DBs" have anything to do with it. It's more that "a = b" and "b = a" are mathematically/logically equivalent.

  Learning : oh yes ... it does. If a = 1M records and b = 3M records , it does matter how you evaluate your joins. Earlier the sequence of joins did matter in queries ( you could see it in the execution plan changing). Now it does not.

• 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.

• 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.

  Sören Kuklau : The join order doesn't change. `FROM topic_info LEFT JOIN topic_data` and `FROM topic_data LEFT JOIN topic_info` *would* return different results.

  Bill Karwin : Right, the OP is changing only the order of terms in the ON clause. Equality is commutative, so the result would be the same. The only difference might be in the way the SQL engine chooses indexes.

  Wayne Khan : As I mentioned...

• 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