What is the difference between ON and WHERE in mysql JOINs?

Answer 1

The where clause usually scans the results of any joins and filters those, thus potentially making it slower. However, with modern query optimizers, this could potentially be pushed earlier into the join process, making it just as fast/equivalent. I just opt to go for the more "correct" route of joining during on so I don't have to worry about what the optimizer is doing.

Note: the where clause can also have all kinds of conditions on the join such as

where t1.id = t2.id OR t1.pickmynose = 'TRUE'

This is why its available there (I think), but shouldn't be used unless needed.

Answer 2

The way that you're using them (inner join), I don't think that there will be any. You would see a difference if you were using outer joins, since the ON clause would not filter out rows, but rather return rows with T1 or T2's fields all NULL.

Answer 3

The difference is only in outer joins. I'd challenge you to parse this for simplicity -- I think the mental process you will go through will help clear this up. These can be rewritten in INNER JOINS and often are.

SELECT * FROM t1
LEFT OUTER JOIN t2
  ON ( true )
  WHERE false

-vs-

SELECT * FROM t1
LEFT OUTER JOIN t2
  ON ( false )
  WHERE true