There great difference where clause vs. on clause, when it comes to left join.

Here is example:

mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | fid | int(11) | NO | | NULL | | | v | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 

There fid is id of table t2.

mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | v | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 

Query on "on clause"

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' -> ; +----+-----+---+------+------+ | id | fid | v | id | v | +----+-----+---+------+------+ | 1 | 1 | H | NULL | NULL | | 2 | 1 | B | NULL | NULL | | 3 | 2 | H | NULL | NULL | | 4 | 7 | K | NULL | NULL | | 5 | 5 | L | NULL | NULL | +----+-----+---+------+------+ 5 rows in set (0.00 sec) 

Query on "where clause":

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K'; +----+-----+---+------+------+ | id | fid | v | id | v | +----+-----+---+------+------+ | 4 | 7 | K | NULL | NULL | +----+-----+---+------+------+ 1 row in set (0.00 sec) 

It is clear that, the first query returns a record from t1 and its dependent row from t2, if any, for row t1.v = 'K'.

The second query returns rows from t1, but only for t1.v = 'K' will have any associated row with it.