SQL Join查询：关联表B按条件筛选表A中符合要求的记录

核心逻辑 #

• 用左连接将Table A与Table B通过id字段关联，自动过滤掉Table B中不存在于Table A的id记录
• 保留满足以下任一条件的Table A记录：
  • 关联后Table B的id为NULL（即当前Table A的id在Table B中无匹配，无需校验直接保留）
  • Table A的date字段处于对应Table B记录的date1与date2区间内
• 注意避免Table B中同一个id存在多条区间时，导致Table A的记录重复返回

通用SQL代码 #

左连接写法 #

SELECT DISTINCT A.*
FROM TableA A
LEFT JOIN TableB B 
  ON A.id = B.id
WHERE 
  B.id IS NULL 
  OR A.date BETWEEN B.date1 AND B.date2

更高效的EXISTS写法（无需去重，性能更优） #

SELECT A.*
FROM TableA A
WHERE 
  -- A的id在B中不存在，直接保留
  NOT EXISTS (SELECT 1 FROM TableB B WHERE B.id = A.id)
  -- A的id在B中存在，且日期匹配任意一条区间
  OR EXISTS (
    SELECT 1 FROM TableB B 
    WHERE B.id = A.id 
    AND A.date BETWEEN B.date1 AND B.date2
  )

特殊场景适配 #

如果表中date、date1、date2存储为字符串格式（示例中为日/月/年格式），需要先转换为日期类型再比较，避免字符串排序导致的逻辑错误，以下为不同数据库的适配示例：

MySQL #

SELECT A.*
FROM TableA A
WHERE 
  NOT EXISTS (SELECT 1 FROM TableB B WHERE B.id = A.id)
  OR EXISTS (
    SELECT 1 FROM TableB B 
    WHERE B.id = A.id 
    AND STR_TO_DATE(A.date, '%d/%m/%Y') BETWEEN STR_TO_DATE(B.date1, '%d/%m/%Y') AND STR_TO_DATE(B.date2, '%d/%m/%Y')
  )

PostgreSQL #

SELECT A.*
FROM TableA A
WHERE 
  NOT EXISTS (SELECT 1 FROM TableB B WHERE B.id = A.id)
  OR EXISTS (
    SELECT 1 FROM TableB B 
    WHERE B.id = A.id 
    AND TO_DATE(A.date, 'DD/MM/YYYY') BETWEEN TO_DATE(B.date1, 'DD/MM/YYYY') AND TO_DATE(B.date2, 'DD/MM/YYYY')
  )

以上代码完全匹配提供的测试用例，执行后可得到预期结果。

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