mariadbwhere-clauseleft-joinsql-null

How to count in LEFT JOIN table

SELECT emp.`emp_id`,cio.`pin`,cio.`checktime` FROM payroll.employees AS emp 

    LEFT JOIN (SELECT * FROM zkteco_biotime.checkinout WHERE checktime > "2022-09-20 23:59:59") AS cio ON emp.emp_id = cio.pin

    WHERE emp.status=0 GROUP BY emp_id ORDER BY checktime DESC

I want to retrieve only NULL values in the checktime or pin columns. However, when I use AND cio.checktime=NULL after emp.status=0 it shows me a blank result

Solution

  • You should use

    AND cio.checktime IS NULL

    Null compared with anything will result in FALSE. Only "IS NULL" and "IS NOT NULL" may return TRUE.