为何SQL查询的WHERE IN子句仅返回单个值的记录？

我执行以下多表关联查询时，仅能得到lab_sdg = 'X3B0341'的记录：

SELECT
        dr.facility_id,
        ds.sys_sample_code,
        ds.sys_loc_code,
        ds.matrix_code,
        ds.sample_type_code,
        ds.start_depth,
        ds.end_depth,
        ds.sample_date,
        ds.task_code,
        dt.prep_date,
        dt.analysis_date,
        dt.lab_name_code,
        dt.lab_sdg,
        dt.lab_sample_id,
        dt.analytic_method,
        dt.prep_method,
        dr.cas_rn,
        dr.custom_field_1 as chemical_name, 
        dr.detect_flag,
        dr.result_numeric as result_value_rdl,
        dr.result_numeric as result_value_mdl,
        dr.result_numeric as result_text_rdl,
        dr.result_numeric as result_text_mdl,
        dr.validator_qualifiers as final_qualifier,
        dr.lab_qualifiers,
        dr.result_unit,
        dr.method_detection_limit,
        dr.quantitation_limit,
        dr.reporting_detection_limit,
        dr.detection_limit_unit,
        dr.reportable_result,
        dr.result_type_code,
        dt.percent_moisture,
        dt.fraction, dt.basis,
        dt.dilution_factor,
        dr.custom_field_2 as validation_date,
        dr.dqm_remark as validation_comment,
        dr.custom_field_3 as validator_name,
        dr.validated_yn, 
        NULL as result_comment,
        dr.approval_code,
        dc.x_coord, dc.y_coord,
        'Groundwater' AS matrix_name
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061;

但单独查询dt_test表时，这些lab_sdg值都存在对应数据：

SELECT dt.lab_sdg, COUNT(*)
FROM [dbo].dt_test dt
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
--AND dt.facility_id = 928061
GROUP BY dt.lab_sdg;

输出结果：

X3B0341 408
X3C0002 239
X3D0121 438
X3E0289 673
X3E0336 303

我尝试将WHERE条件改为WHERE dt.lab_sdg = 'X3D0121' OR dt.lab_sdg = 'X3E0336'仍无效，目前只能通过多UNION语句实现需求，希望找到更优方案。

可能的原因及解决办法 #

1. INNER JOIN 过滤了不完整的关联数据 #

主查询使用了两次INNER JOIN：dt_sample与dt_test关联，dt_test与dt_result关联。如果其他lab_sdg对应的dt_test记录，没有匹配的dt_sample或dt_result数据，就会被INNER JOIN完全过滤。

排查方法：针对单个非X3B0341的lab_sdg（比如X3D0121），执行以下查询验证关联链完整性：

-- 检查X3D0121的dt_test是否有匹配的dt_sample
SELECT COUNT(*)
FROM [dbo].dt_test dt
LEFT JOIN [dbo].dt_sample ds ON dt.sample_id = ds.sample_id
WHERE dt.lab_sdg = 'X3D0121' AND dt.facility_id = 928061
AND ds.sample_id IS NULL; -- 返回>0则说明存在无对应样本的测试记录

-- 检查X3D0121的dt_test是否有匹配的dt_result
SELECT COUNT(*)
FROM [dbo].dt_test dt
LEFT JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
WHERE dt.lab_sdg = 'X3D0121' AND dt.facility_id = 928061
AND dr.test_id IS NULL; -- 返回>0则说明存在无对应结果的测试记录

解决办法：如果业务需要保留所有符合条件的dt_test记录（即使没有关联的样本或结果），将INNER JOIN替换为LEFT JOIN：

SELECT
    -- 保留原字段列表...
    'Groundwater' AS matrix_name
FROM [dbo].dt_test dt
LEFT JOIN [dbo].dt_sample ds ON dt.sample_id = ds.sample_id
LEFT JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
AND dt.facility_id = 928061;

注：无关联的字段会显示NULL，需根据业务需求判断是否接受。

2. facility_id 条件的限制 #

单独查询dt_test时注释掉了dt.facility_id = 928061，计数结果包含了其他设施的数据。需确认其他lab_sdg在facility_id = 928061下是否真的有数据：

验证查询：

SELECT dt.lab_sdg, COUNT(*)
FROM [dbo].dt_test dt
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
AND dt.facility_id = 928061
GROUP BY dt.lab_sdg;

如果该查询仅返回X3B0341的计数，说明其他lab_sdg在该设施下确实无数据，单独查询的计数来自其他设施。

3. 关联字段的潜在问题 #

检查关联条件dt.sample_id = ds.sample_id和dr.test_id = dt.test_id是否存在：

• 数据类型不匹配（比如一个是字符串、一个是数字）
• 字段包含隐形字符（空格、换行等）导致匹配失败

这类问题会导致部分lab_sdg的关联无法成功，仅X3B0341的字段刚好匹配。

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