In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?

Question

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.

Answer 1

The ON clause defines the relationship between the tables.

The WHERE clause describes which rows you are interested in.

Many times you can swap them and still get the same result, however this is not always the case with a left outer join.

• If the ON clause fails you still get a row with columns from the left table but with nulls in the columns from the right table.
• If the WHERE clause fails you won't get that row at all.