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

This question already has answers here: Close开发者_如何学JAVAd 12 years ago.

Possible Duplicates:

Difference between “and” and “where” in joins

In MySQL queries, why use join instead of where?

What is the difference between

SELECT * 
 from T1 
 JOIN T2 ON T1.X = T2.X 
        AND T1.Y = T2.Y

and

SELECT * 
  from T1 
  JOIN T2 ON T1.X = T2.X
 WHERE T1.Y = T2.Y

?

---

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

---

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.

---

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.