查询schedule表中未在timecard表匹配的记录问题求助

现有schedule表和timecard表，需要找出schedule表中所有在timecard表中无对应记录的条目。示例场景：

• schedule表包含2024-09-30至2024-10-04的5条记录
• timecard表缺少2024-10-03的对应记录

尝试了以下SQL语句，但无法仅筛选出2024-10-03的缺失记录：

SELECT *
FROM schedule s
WHERE NOT EXISTS (
    SELECT 1
    FROM timecard t
    WHERE t.id = s.id
    AND t.[punch date] BETWEEN '2024-09-30' AND '2024-10-05'
)
AND s.[start date] BETWEEN '2024-09-30' AND '2024-10-05';

该语句仅在timecard表无任何该id的记录时返回结果，无法满足需求。

还尝试了左连接语句，同样无效：

SELECT s.*
FROM schedule s
LEFT JOIN timecard t
    ON s.id = t.id AND t.[punch date] BETWEEN '2024-09-30' AND '2024-10-05'
WHERE t.id IS NULL OR t.[punch date] IS NULL;

之前的查询错误在于没有将schedule的日期与timecard的日期做精准关联，只是匹配了id和宽泛的日期范围，导致只要该id在timecard中有任意日期的记录，就会排除所有该id的schedule条目。以下是两种修正后的方法：

方法一：使用NOT EXISTS（推荐） #

在子查询中同时匹配id和对应日期，确保只检查当前schedule条目的日期是否在timecard中有对应记录：

SELECT s.*
FROM schedule s
WHERE s.[start date] BETWEEN '2024-09-30' AND '2024-10-04'
AND NOT EXISTS (
    SELECT 1
    FROM timecard t
    WHERE t.id = s.id
    AND t.[punch date] = s.[start date] -- 关键：关联两张表的日期
);

方法二：使用左连接 #

同样需要在连接条件中关联日期，然后筛选出未匹配到的记录：

SELECT s.*
FROM schedule s
LEFT JOIN timecard t
    ON s.id = t.id 
    AND t.[punch date] = s.[start date] -- 关键：关联两张表的日期
WHERE s.[start date] BETWEEN '2024-09-30' AND '2024-10-04'
AND t.id IS NULL;

补充说明 #

如果schedule和timecard的日期格式存在差异（比如timecard的punch date是带时间的datetime类型，schedule的start date是纯date类型），可以调整日期匹配条件为：

-- 统一转换为日期类型后对比
CAST(t.[punch date] AS DATE) = CAST(s.[start date] AS DATE)

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