MySQL: LEFT JOIN not working as expected

Question

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?

Answer 1

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 >= '2016-07-02'
    AND a.attIn  < '2016-07-03' ;

Tested at SQLfiddle.

Answer 2

Your LEFT JOIN delivers the missing attendanceIn fields as NULL. If you still want this result then you must add:

or attIn is NULL

at the end of your query. Now you will get the expected result.