In SQL / MySQL, what is the difference between "On" and "Where" in a join statement?
- by Jian Lin
The following statements give the same result (one is using "on", and the other using "where"):
mysql> select * from gifts INNER JOIN sentGifts on gifts.giftID = sentGifts.giftID;
mysql> select * from gifts INNER JOIN sentGifts where gifts.giftID = sentGifts.giftID;
I can only see in a case of a Left Outer Join finding the "unmatched" cases:
(to find out the gifts that were never sent by anybody)
mysql> select name from gifts LEFT OUTER JOIN sentgifts
on gifts.giftID = sentgifts.giftID
where sentgifts.giftID IS NULL;
In this case, it is first using "on", and then "where". Does the "on" first do the matching, and then "where" does the "secondary" filtering? Or is there a more general rule of using "on" versus "where"? Thanks.
