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

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.

WHERE is a part of the SELECT query as a whole, ON is a part of each individual join.

ON can only refer to the fields of previously used tables.

When there is no actual match against a record in the left table, LEFT JOIN returns one record from the right table with all fields set to NULLS. WHERE clause then evaluates and filter this.

In your query, only the records from gifts without match in 'sentgifts' are returned.

Here's the example

gifts1   Teddy bear2   Flowerssentgifts1   Alice1   Bob---SELECT  *FROM    gifts gLEFT JOIN        sentgifts sgON      g.giftID = sg.giftID---1  Teddy bear   1     Alice1  Teddy bear   1     Bob2  Flowers      NULL  NULL    -- no match in sentgifts---SELECT  *FROM    gifts gLEFT JOIN        sentgifts sgON      g.giftID = sg.giftIDWHERE   sg.giftID IS NULL---2  Flowers      NULL  NULL    -- no match in sentgifts

As you can see, no actual match can leave a NULL in sentgifts.id, so only the gifts that had not ever been sent are returned.

When using INNER JOIN, ON and WHERE will have the same result. So,

select *from Table1 t1inner join Table2 t2 on t1.id = t2.idwhere t1.Name = 'John'

will have the exact same output as

select *from Table1 t1inner join Table2 t2 on t1.id = t2.id    and t1.Name = 'John'

As you have noted, this is not the case when using OUTER JOIN. What query plan gets built is dependent on the database platform as well as query specifics, and is subject to change, so making decisions on that basis alone is not going to give a guaranteed query plan.

As a rule of thumb, you should use columns that join your tables in ON clauses and columns that are used for filtering in WHERE clauses. This provides the best readability.