LEFT JOIN结合GROUP BY与WHERE子句查询：无记录时显示NULL值

现有员工表employees和考勤记录表time_records，执行LEFT JOIN查询时添加日期条件后，仅返回有考勤记录的员工；需要修改SQL，让结果显示所有员工，无考勤记录的字段显示NULL或0。

表结构 #

员工表（employees） #

| emp_id | lastname | firstname |
+--------+----------+-----------+
| sgi01  | Doe      | John      |
| sgi02  | Doe      | Karen     |
| sgi03  | Doe      | Vincent   |
| sgi04  | Doe      | Kyle      |
+--------+----------+-----------+

考勤记录表（time_records） #

| emp_id | date     | time_in  | time_out  |
+--------+----------+----------+-----------+
| sgi01  | 2024-03-01 | 07:00:00 | 16:03:22 |
| sgi02  | 2024-03-01 | 07:00:00 | 16:03:22 |
+--------+----------+----------+-----------+

原SQL问题分析 #

原SQL中将日期条件t.date = '2024-03-01'放在WHERE子句中，这会导致LEFT JOIN后，所有t表字段为NULL的行（即无考勤记录的员工）被过滤掉——因为NULL = '2024-03-01'的结果不成立，最终只返回有考勤记录的员工。

修正后的SQL #

SELECT 
    e.emp_id, 
    e.lastname, 
    e.firstname, 
    t.time_in, 
    t.time_out,
    TIME_FORMAT(IFNULL(t.time_in, '00:00:00'), "%r") as Ctime_in,
    TIME_FORMAT(IFNULL(t.time_out, '00:00:00'), "%r") as Ctime_out,
    COALESCE(TIMEDIFF(t.time_out, t.time_in), '00:00:00') as number_of_hrs
FROM `employees` as e 
LEFT JOIN time_records as t  
    ON e.emp_id = t.emp_id 
    AND t.date = '2024-03-01' -- 将日期条件移至JOIN的ON子句中
GROUP BY e.emp_id;

关键修改说明 #

1. 将日期条件移至ON子句：LEFT JOIN时，仅匹配t表中符合日期条件的记录，同时保留employees表的所有行，无考勤记录的员工对应的t表字段会自动为NULL。
2. 处理NULL值：使用IFNULL()或COALESCE()函数将NULL值转换为指定内容（比如00:00:00），让结果更直观：
   • IFNULL(t.time_in, '00:00:00')：当time_in为NULL时，返回00:00:00
   • COALESCE(TIMEDIFF(...), '00:00:00')：当考勤时间差为NULL时，返回00:00:00

期望查询结果 #

| emp_id | lastname | firstname | time_in  | time_out  | Ctime_in   | Ctime_out  | number_of_hrs |
+--------+----------+-----------+----------+-----------+------------+------------+---------------+
| sgi01  | Doe      | John      | 07:00:00 | 16:03:22  | 07:00:00 AM| 04:03:22 PM| 09:03:22      |
| sgi02  | Doe      | Karen     | 07:00:00 | 16:03:22  | 07:00:00 AM| 04:03:22 PM| 09:03:22      |
| sgi03  | Doe      | Vincent   | NULL     | NULL      | 12:00:00 AM| 12:00:00 AM| 00:00:00      |
| sgi04  | Doe      | Kyle      | NULL     | NULL      | 12:00:00 AM| 12:00:00 AM| 00:00:00      |
+--------+----------+-----------+----------+-----------+------------+------------+---------------+

内容的提问来源于stack exchange，提问作者Raymart Calinao