MySQL: LEFT JOIN not working as expected I have 2 tables: Employees, attendanceIn


When I query using LEFT JOIN 

SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees` 
LEFT JOIN attendanceIn ON employees.eno = attendanceIn.eno
WHERE date(attIn) like '2016-07-02%'


What I got is,




Whats wrong with my Query?
How do I get my Expected result?
The WHERE date(attIn) like '2016-07-02%' is converting the LEFT join to an INNER join. The condition should be moved to the ON clause.

Also: 


It's not good practise to use LIKE for dates comparison
Using functions on columns (like the date()) before comparing it makes indexes useless. It's better to make the condition sargable.


The query corrected:

SELECT e.eno, e.name, e.dept, a.attIn 
FROM employees AS e 
  LEFT JOIN attendanceIn AS a 
    ON  e.eno = a.eno
    AND a.attIn &gt;= '2016-07-02'
    AND a.attIn  &lt; '2016-07-03' ;


Tested at SQLfiddle.