difference between these two mysql queries?

Question

I thought these two queries were the same. But the results turned out the first query's constraints starting with the four ANDs worked, many wrong rows with the left join are returned. The results in the second query look like right. Could anyone explain what's the difference between these two queries? this question is similar with this one, but I 100% sure that the results are different.

       SELECT uinfo.serial_number, uinfo.firmware, upd.objtype, upd.category
       FROM UnitInfo uinfo
       LEFT JOIN 
       UnitUpdateInfo upd
       ON uinfo.serial_number = upd.serial_number
       AND substring(uinfo.serial_number,1,2) NOT IN ('AB','CD','EF','GH')  
       AND substring(uinfo.serial_number,1,1) NOT LIKE 'M%'
       AND upd.objtype ='HEEN' 
       AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(uinfo.firmware, '-', -2), '-', 1)) LIKE '3.0%'   


       SELECT uinfo.serial_number, uinfo.firmware, upd.objtype, upd.category
       FROM UnitInfo uinfo
       LEFT JOIN 
       UnitUpdateInfo upd
       ON uinfo.serial_number = upd.serial_number
       WHERE substring(uinfo.serial_number,1,2) NOT IN ('AB','CD','EF','GH')  
       AND substring(uinfo.serial_number,1,1) NOT LIKE 'M%'
       AND upd.objtype ='HEEN' 
       AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(uinfo.firmware, '-', -2), '-', 1)) LIKE '3.0%'

[edit]
the second query is the same as:

       SELECT uinfo.serial_number, uinfo.firmware, upd.objtype, upd.category
       FROM UnitInfo uinfo
       INNER JOIN 
       UnitUpdateInfo upd
       ON uinfo.serial_number = upd.serial_number
       WHERE substring(uinfo.serial_number,1,2) NOT IN ('AB','CD','EF','GH')  
       AND substring(uinfo.serial_number,1,1) NOT LIKE 'M%'
       AND upd.objtype ='HEEN' 
       AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(uinfo.firmware, '-', -2), '-', 1)) LIKE '3.0%'

Answer 1

As soon as a right-hand side table in a LEFT JOIN has fields compared for e.g. equality in the WHERE clause, it effectively becomes an INNER JOIN - in your query, you're forcing the objtype field of UnitUpdateInfo to be non-NULL => the join must have succeeded.

The first query, with all the join logic as part of the LEFT JOIN will include all rows from the first table joined to those rows from the second table - with NULLs for the second tables fields where a join wasn't possible.

The second query will not include those rows from the first table that don't join to the second.