使用子查询时WHERE IN子句失效问题排查

为什么IN子句不工作？ #

问题出在：你的子查询返回的是单个完整字符串（'B098GJ3K6Z','B08Q26RV4D'），而IN子句需要的是多个独立的ASIN值。

• 手动写IN ('B098GJ3K6Z','B08Q26RV4D')时，数据库会把两个字符串当成独立的匹配项；
• 但子查询返回的是一整个带引号和逗号的字符串，数据库会把它当成一个整体去匹配ASIN字段，自然找不到对应结果。

解决方案：拆分字符串后再匹配 #

根据你使用的数据库，选择对应的字符串拆分方法，把单个逗号分隔的字符串拆分成多行独立的ASIN值，同时去掉每个ASIN两边的单引号：

如果你用的是Snowflake（从表命名和字段类型推测大概率是） #

select
    distinct asin,
    ordered_revenue,
    report_date
from
    ams_cubes.campaigns_asin_workbench
where asin in (
    -- 拆分逗号分隔的字符串，并去除前后单引号
    select trim(value, '''')
    from temp.asin_workbench_filtered,
    lateral flatten(input => split(comma_separated_asin, ','))
);

如果你用的是PostgreSQL #

select
    distinct asin,
    ordered_revenue,
    report_date
from
    ams_cubes.campaigns_asin_workbench
where asin in (
    select trim(unnest(string_to_array(comma_separated_asin, ',')), '''')
    from temp.asin_workbench_filtered
);

如果你用的是MySQL 8.0+ #

select
    distinct asin,
    ordered_revenue,
    report_date
from
    ams_cubes.campaigns_asin_workbench
where asin in (
    select trim(json_unquote(value), '''')
    from temp.asin_workbench_filtered,
    json_table(
        concat('["', replace(comma_separated_asin, '''', ''), '"]'),
        '$[*]' columns(value varchar path '$')
    ) as jt
);

优化建议 #

长期来看，尽量不要用逗号分隔的字符串存储多值，把temp.asin_workbench_filtered改成每行存一个ASIN的结构，这样查询效率更高，也能避免这类拆分问题。

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