SQL查询如何在日期范围内无变更日志时仍返回预期结果？

你将LEFT JOIN关联的4张变更日志表的日期过滤条件写在了WHERE子句中，当对应表在指定日期范围内没有匹配记录时，cldp.createdon这类字段值为NULL，WHERE子句的范围判断会直接排除整行数据，等效于把LEFT JOIN变为INNER JOIN，因此无变更记录时会返回空结果。

把所有变更日志表的日期过滤条件从WHERE子句移动到对应LEFT JOIN的ON子句中，仅过滤变更日志的匹配行，不会影响主表（Customer、Members）的行返回，即可触发你预先编写的兜底逻辑。

Declare @StartDate as date
Declare @EndDate as date

Set @StartDate = '9/20/2021'
Set @EndDate = '10/1/2021'

Select DISTINCT n.ID, n.FULL_NAME, n.Company, n.Status, n.Email, 
    Case 
        when Coalesce(CLDP.CurrentValue, 'No Update Found') <> 'No Update Found' then CLDP.CurrentValue
        When Coalesce(CLDP.CurrentValue, 'No Update Found') = 'No Update Found' and b.PROFESSION <> '' then b.PROFESSION
        when Coalesce(CLDP.CurrentValue, 'No Update Found') = 'No Update Found' and b.PROFESSION = '' then 'No Update Found'
    end as ProfessionUpdate,

    Case 
        when Coalesce(CLDE.CurrentValue, 'No Update Found') <> 'No Update Found' then CLDE.CurrentValue
        When Coalesce(CLDE.CurrentValue, 'No Update Found') = 'No Update Found' and b.ETHNICITY <> '' then b.ETHNICITY
        when Coalesce(CLDE.CurrentValue, 'No Update Found') = 'No Update Found' and b.ETHNICITY = '' then 'No Update Found'
    end as EthnicityUpdate,

    Case 
        when Coalesce(CLDG.CurrentValue, 'No Update Found') <> 'No Update Found' then CLDG.CurrentValue
        When Coalesce(CLDG.CurrentValue, 'No Update Found') = 'No Update Found' and b.Gender <> '' then b.Gender
        when Coalesce(CLDG.CurrentValue, 'No Update Found') = 'No Update Found' and b.Gender = '' then 'No Update Found'
    end as GenderUpdate,

    Case 
        when Coalesce(nl.LOG_TEXT, 'No Update Found') <> 'No Update Found' then nl.Log_Text 
        When Coalesce(nl.LOG_TEXT, 'No Update Found') = 'No Update Found' and Cast(n.BIRTH_DATE as varchar) <> '' then Cast(n.BIRTH_DATE as VarChar)
        when Coalesce(nl.LOG_TEXT, 'No Update Found') = 'No Update Found' and Cast(n.BIRTH_DATE as varchar) = '' then 'No Update Found'
    end as BirthDateUpdate

from Customer as n 
inner join Members as b on b.ID = n.ID 
left join Profession as CLDP on n.ID = CLDP.ID and cldp.createdon between @startDate and @endDate
left join Ethnicity as CLDE on n.ID = CLDE.ID and clde.createdon between @startdate and @enddate
Left Join Gender as CLDG on n.ID = CLDG.ID and cldg.createdon between @startdate and @enddate
Left Join BirthDate as nl on nl.ID = n.ID and nl.date_time between @StartDate and @EndDate
Where n.ID in ('12345', '67890')

你原有的CASE判断可以用NULLIF+COALESCE简化，写法更简洁，逻辑完全一致：

-- 示例，其他字段可按相同逻辑改写
COALESCE(NULLIF(CLDP.CurrentValue, ''), NULLIF(b.PROFESSION, ''), 'No Update Found') as ProfessionUpdate

该语法优先取变更日志的非空值，其次取Members表的非空值，最后兜底为No Update Found。

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