joins in mysql5

Question

I've seen people writing the following query

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

written like this

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name2.column_name =table_name1.column_name

does it actually make any difference?

Answer 1

Not under normal circumstances, no. It's imaginable that there could be some sufficiently complex query, much more complex than your examples, where the join optimizer would be so overwhelmed that this could matter, but if that's even possible you'd have to work pretty hard.

You can write both of those as USING (column_name), by the way. :)

I'd also recommend, for purposes of DIY analysis of issues like this, that you familiarize yourself with EXPLAIN.

Answer 2

There is no difference.

table_name1.column_name=table_name2.column_name

and

table_name2.column_name=table_name1.column_name

have the same meaning.

The 'left' part of the join is referring the the tables and not to the comparison -- I guess that was your implicit question?

Answer 3

It's likely to be a matter of personal taste; I happen to write joins like your second example but couldn't say I'm 100% consistent in doing it.

Also, back in the Bad Old Days when the query optimizers weren't as smart as they are now, you could find that re-ordering the operands in a join expression or WHERE clause could make a big difference; i.e., the optimizer would use an index if the operands were ordered one way but not when they were reversed.

I think I'm having an Oracle 7 flasback. Gotta lie down...