SQL Left Join仅返回匹配记录问题：如何保留主表全量数据？

问题场景 #

我有一张包含300+条记录的vtRawDayData表，需与tblPools表关联：

• vtRawDayData的StopPlaceIDs字段状态：可为null、单个格式如P12C1234的ID，或多个用逗号分隔的ID
• tblPools表字段：varchar类型的ID、整数类型的ServiceLevel_ID

需求是返回vtRawDayData的全部300+条记录，仅当StopPlaceIDs为单个ID且与tblPools的ID完全匹配时，填充对应的ServiceLevel_ID，否则该字段为null。但当前SQL仅返回99条匹配记录，问题出在哪里？

当前SQL代码 #

Select 
  rdd.Vehicle,
  rdd.Date,
  rdd.StartLocation,
  rdd.StartGeoFence,
  rdd.StartPlaceIDs,
  rdd.StartLatitude,
  rdd.StartLongitude,
  rdd.StartTime,
  rdd.TravelTime,
  rdd.StopLocation,
  rdd.StopGeoFence,
  rdd.StopPlaceIDs,
  rdd.StopLatitude,
  rdd.StopLongitude,
  rdd.ArrivalTime,
  rdd.StopDuration,
  rdd.StopDurationSeconds,
  rdd.IdleDuration,
  rdd.DepartureTime,
  rdd.Odometer,
  rdd.IdleTimeSeconds,
  rdd.StopDurationSeconds / 60 as StopDurationMinutes,
  p.ServiceLevel_ID 

FROM
  vtRawDayData rdd 

LEFT JOIN
  tblPools p 

WHERE
  rdd.StopPlaceIDs = p.ID

问题原因 #

1. LEFT JOIN 被降级为 INNER JOIN：你把关联条件rdd.StopPlaceIDs = p.ID写在了WHERE子句里。对于vtRawDayData中不匹配的记录，p.ID会是null，而WHERE子句会直接过滤掉这些null记录，导致最终只返回匹配的99条，而非全部300+条。
2. 缺少单个ID的判断：原SQL没有判断StopPlaceIDs是否为单个ID（不含逗号），如果存在某个逗号分隔的字符串恰好和tblPools的ID完全匹配，会错误返回ServiceLevel_ID，不符合需求。

修正后的SQL代码 #

Select 
  rdd.Vehicle,
  rdd.Date,
  rdd.StartLocation,
  rdd.StartGeoFence,
  rdd.StartPlaceIDs,
  rdd.StartLatitude,
  rdd.StartLongitude,
  rdd.StartTime,
  rdd.TravelTime,
  rdd.StopLocation,
  rdd.StopGeoFence,
  rdd.StopPlaceIDs,
  rdd.StopLatitude,
  rdd.StopLongitude,
  rdd.ArrivalTime,
  rdd.StopDuration,
  rdd.StopDurationSeconds,
  rdd.IdleDuration,
  rdd.DepartureTime,
  rdd.Odometer,
  rdd.IdleTimeSeconds,
  rdd.StopDurationSeconds / 60 as StopDurationMinutes,
  p.ServiceLevel_ID 

FROM
  vtRawDayData rdd 

LEFT JOIN
  tblPools p 
ON 
  -- 仅当StopPlaceIDs是单个ID（不含逗号）且与p.ID完全匹配时关联
  rdd.StopPlaceIDs = p.ID
  AND CHARINDEX(',', rdd.StopPlaceIDs) = 0
  -- 排除StopPlaceIDs为null的情况，避免不必要的关联
  AND rdd.StopPlaceIDs IS NOT NULL

说明 #

• 把关联条件移到ON子句里，保证LEFT JOIN能返回vtRawDayData的全部记录，不匹配的记录中p.ServiceLevel_ID会自动为null。
• 增加CHARINDEX(',', rdd.StopPlaceIDs) = 0判断，确保只有单个ID的记录才会尝试关联。
• 加上rdd.StopPlaceIDs IS NOT NULL，避免null值的记录去关联，提升查询效率。

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