使用MySQL和VB查找Calendar表存在但Attendance表缺失的日期

我有两张表：

• Attendance表：字段为ID, DLNumber, Time
• Calendar表：字段为ID, Time

需求是：找出指定日期范围内，Calendar表存在但Attendance表中指定驾照号（DLNumber）无对应记录的日期。例如查询2023-10-23至2023-10-27时，预期结果仅为2023-10-24，但之前尝试的SQL均返回全部日期，未达预期。

之前尝试的错误SQL：

1. NOT IN写法（未过滤Calendar的日期范围）：

Dim sql As String = "SELECT calendar.Time FROM calendar WHERE 
calendar.Time NOT IN (SELECT attendance.Time FROM attendance WHERE 
attendance.DLNumber=?DL AND attendance.Time>= ?ST And 
attendance.Time < ?ET)"

2. Left Join写法（将Attendance的过滤条件放在WHERE子句，导致Left Join退化为Inner Join）：

Dim sql As String = "Select calendar.Time,attendance.Time From calendar " _
& "Left Join attendance On attendance.time = calendar.Time WHERE 
attendance.DLNumber=?DL AND attendance.Time>= ?ST And 
attendance.Time < ?ET  Order BY calendar.Time"

能正确获取匹配日期的SQL：

Select calendar.Date,attendance.Date From calendar 
Left Join attendance On attendance.Date = calendar.Date WHERE 
attendance.DLNumber=?DL AND attendance.Date> ?ST And 
attendance.Date <=?ET AND  calendar.Date> ?ST And 
calendar.Date <= ?ET Order BY calendar.Date 

方法1：Left Join + 空值判断 #

核心是将Attendance的过滤条件（DLNumber、日期范围）放到ON子句而非WHERE，保留Calendar中所有目标日期区间的记录，再筛选出Attendance匹配失败的记录（即attendance.ID IS NULL）：

Dim sql As String = "SELECT calendar.Time FROM calendar " _
& "LEFT JOIN attendance ON attendance.Time = calendar.Time " _
& "AND attendance.DLNumber = ?DL " _
& "AND attendance.Time >= ?ST " _
& "AND attendance.Time <= ?ET " _
& "WHERE calendar.Time >= ?ST " _
& "AND calendar.Time <= ?ET " _
& "AND attendance.ID IS NULL " _
& "ORDER BY calendar.Time"

方法2：NOT EXISTS（性能更稳定） #

用NOT EXISTS替代NOT IN，同时给Calendar表加上日期范围过滤，仅返回目标区间内的缺失日期：

Dim sql As String = "SELECT calendar.Time FROM calendar " _
& "WHERE calendar.Time >= ?ST " _
& "AND calendar.Time <= ?ET " _
& "AND NOT EXISTS (" _
& "    SELECT 1 FROM attendance " _
& "    WHERE attendance.Time = calendar.Time " _
& "    AND attendance.DLNumber = ?DL " _
& "    AND attendance.Time >= ?ST " _
& "    AND attendance.Time <= ?ET " _
& ") " _
& "ORDER BY calendar.Time"

错误原因分析 #

1. 第一个NOT IN的SQL未对Calendar表做日期范围过滤，会返回Calendar中所有不在Attendance指定范围内的日期，而非目标区间内的缺失日期。
2. 第二个Left Join的SQL将Attendance的条件放在WHERE子句，导致Left Join退化为Inner Join——attendance.DLNumber=?DL会过滤掉所有Attendance匹配失败的记录（此时attendance.DLNumber为NULL，不满足条件），无法得到缺失日期。

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