CODEWITHSUNDEEP
sqlmysqloptimizationjoin
moo
moo

Reputation: 7789

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)

Upvotes: 2

Views: 500

Answers (5)

benlumley
benlumley

Reputation: 11382

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.

Upvotes: 2

John Sansom
John Sansom

Reputation: 41879

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

Upvotes: 3

Wayne
Wayne

Reputation: 1290

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.

Upvotes: 3

Learning
Learning

Reputation: 8185

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

Upvotes: 2

Samantha Branham
Samantha Branham

Reputation: 7451

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

Upvotes: 4

Related Questions