SQL Server LEFT JOIN中ON子句日期过滤不生效问题求助

问题描述 #

执行以下查询语句时，cast(a.date_ as date)>= cast('2021_12_01' as date)条件未生效，结果中出现了早于2021-12-01的日期值：

select distinct a.*, b.*
from table_a a
left join 
(select * from table_b where number_ in ('1','2') ) b
on (a.id=b.id 
and cast(a.date_ as date)>= cast('2021_12_01' as date))

单独执行select * from table_a where cast(date_ as date)>= cast('2021_12_01' as date)可以正确过滤日期，但放到LEFT JOIN的ON子句中就失效了。

测试数据 #

table_a：

table_b：

期望结果仅包含id为2的相关信息，因其date_大于2021-12-01。

问题原因 #

LEFT JOIN的核心特性是保留左表（table_a）的所有记录，ON子句中的条件仅用于判断右表（table_b）的哪些记录能和左表关联匹配。如果左表记录不满足ON中的条件，只会让对应的右表字段显示为NULL，并不会过滤掉左表本身的这条记录。所以原查询中table_a的id=1记录会被保留，只是b的所有字段为NULL。

解决方案 #

方案一：将日期过滤条件放到WHERE子句 #

把日期条件从ON子句移到WHERE子句，直接过滤掉左表中不满足日期条件的记录：

select distinct a.*, b.*
from table_a a
left join 
(select * from table_b where number_ in ('1','2') ) b
on a.id=b.id
where cast(a.date_ as date)>= cast('2021_12_01' as date)

方案二：先过滤左表再关联 #

先对table_a做日期过滤，再和table_b进行LEFT JOIN，逻辑更清晰：

select distinct filtered_a.*, b.*
from (
    select * from table_a 
    where cast(date_ as date)>= cast('2021_12_01' as date)
) filtered_a
left join 
(select * from table_b where number_ in ('1','2') ) b
on filtered_a.id=b.id

两种方案都能得到期望的结果：仅返回id为2的记录及其关联的table_b数据。

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