带'top 1'的子查询返回NULL值，但表字段无NULL的原因排查

核心原因 #

你的子查询select top 1 ... from learner_employment order by DATE_STATUS_APPLIES是从整个learner_employment表中取出最早的单条雇佣记录，这条记录只属于某一个特定的PERSON_CODE：

• 当用left join关联people表时，只有people表中PERSON_CODE和这条唯一top1记录完全匹配的行，能拿到emp的字段值；其他所有不匹配的行，因left join的特性，emp相关字段都会返回NULL。
• 单独针对某个PERSON_CODE执行子查询时，是在该PERSON_CODE的范围内取最早记录，所以能得到对应结果；去掉top1后子查询返回所有雇佣记录，left join会匹配每个PERSON_CODE对应的所有记录，因此不会出现大量NULL。

正确实现方案（以SQL Server为例） #

因为你的需求是每个人员仅获取最早的雇佣记录，需要按PERSON_CODE分组取每组的第一条记录，而非全表取一条，下面是两种常用写法：

方法1：使用ROW_NUMBER()窗口函数

select 
    p.PERSON_CODE,
    emp.EMPLOYMENT_STATUS,
    emp.EMPLOYMENT_INTENSITY,
    emp.DATE_STATUS_APPLIES
from people p
left join (
    select 
        PERSON_CODE, 
        EMPLOYMENT_STATUS, 
        EMPLOYMENT_INTENSITY, 
        DATE_STATUS_APPLIES,
        -- 按PERSON_CODE分组，每组内按日期升序排序，最早的记录标记为1
        ROW_NUMBER() over (partition by PERSON_CODE order by DATE_STATUS_APPLIES) as rn
    from learner_employment
) emp on emp.PERSON_CODE = p.PERSON_CODE and emp.rn = 1

方法2：使用OUTER APPLY（更简洁直观）

select 
    p.PERSON_CODE,
    emp.EMPLOYMENT_STATUS,
    emp.EMPLOYMENT_INTENSITY,
    emp.DATE_STATUS_APPLIES
from people p
outer apply (
    -- 针对people表的每一行，查询对应PERSON_CODE的最早雇佣记录
    select top 1 
        PERSON_CODE, 
        EMPLOYMENT_STATUS, 
        EMPLOYMENT_INTENSITY, 
        DATE_STATUS_APPLIES 
    from learner_employment le
    where le.PERSON_CODE = p.PERSON_CODE
    order by DATE_STATUS_APPLIES
) emp

说明 #

• 方法1通过窗口函数给每个PERSON_CODE的雇佣记录排序，筛选出每组的第一条记录后再关联people表。
• 方法2利用outer apply实现“逐行查询”，对每个人员单独取其最早的雇佣记录，完全贴合业务需求。

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