SQL表关联筛选：保留教师有效日期范围内的学生记录

现有表结构及数据 #

TABLE1（教师有效日期区间表） #

TEACHER DATE1       DATE2
1       2018-04-28  2019-05-30
1       2019-09-03  2019-09-06
1       2019-10-13  2019-12-21
2       2018-01-10  2018-12-30
2       2019-09-18  2019-11-03
3       2018-01-04  2019-07-27
4       2018-05-08  2019-01-19
4       2019-11-09  2019-11-20

TABLE2（学生授课记录） #

STUDENT TEACHER DATE
1       2       2019-08-09
1       2       2018-12-16
1       4       2018-11-01
1       2       2018-10-31
1       4       2019-09-05
2       3       2019-08-13
2       3       2019-10-19
2       4       2018-09-16
2       1       2018-06-12
2       3       2019-03-02
2       4       2018-12-19
2       4       2019-04-24
2       4       2018-09-16

需求说明 #

需要关联两张表得到目标表WANT，仅保留TABLE2中DATE落在对应教师在TABLE1中的DATE1与DATE2区间内的记录。目标表结构及数据如下：

WANT        
STUDENT TEACHER DATE
1       2       2018-12-16
1       2       2018-10-31
1       4       2018-11-01
2       1       2018-06-12
2       3       2019-03-02
2       4       2018-12-19
2       4       2019-04-24

例如，TABLE2中记录1 2 2019-08-09未被纳入WANT表，因为该记录的DATE（2019-08-09）不在TABLE1中教师2的有效日期区间内。

尝试的错误SQL #

SELECT * FROM TABLE2
WHERE
TEACHER IN (SELECT TEACHER FROM TABLE1)
AND DATE >= (SELECT DATE1 FROM TABLE1)
AND DATE <= (SELECT DATE2 FROM TABLE1)

错误原因 #

这段SQL的问题在于：

• 子查询未关联当前记录的TEACHER，导致判断日期时用了所有教师的DATE1和DATE2，而非对应教师的区间；
• 当TABLE1有多条记录时，SELECT DATE1 FROM TABLE1会返回多个值，直接用于DATE >=条件会引发逻辑错误或报错。

正确实现方法 #

方法1：使用JOIN关联筛选 #

通过TEACHER字段关联两张表，同时筛选日期在区间内的记录，用DISTINCT去重（避免同一学生记录匹配教师多个区间导致重复）：

SELECT DISTINCT t2.STUDENT, t2.TEACHER, t2.DATE
FROM TABLE2 t2
JOIN TABLE1 t1 
  ON t2.TEACHER = t1.TEACHER
  AND t2.DATE BETWEEN t1.DATE1 AND t1.DATE2
ORDER BY t2.STUDENT, t2.TEACHER, t2.DATE;

方法2：使用EXISTS子查询 #

通过EXISTS检查当前TABLE2记录是否存在对应教师的有效日期区间，无需额外去重：

SELECT t2.*
FROM TABLE2 t2
WHERE EXISTS (
    SELECT 1
    FROM TABLE1 t1
    WHERE t1.TEACHER = t2.TEACHER
      AND t2.DATE BETWEEN t1.DATE1 AND t1.DATE2
)
ORDER BY t2.STUDENT, t2.TEACHER, t2.DATE;

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