Where IN Clause not working when given with a subquery

Question

I have a query select comma_separated_asin from temp.asin_workbench_filtered

which gives out

COMMA_SEPARATED_ASIN
'B098GJ3K6Z','B08Q26RV4D'

I have another query

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

This does not work

But if I manually give the values like where asin in ('B098GJ3K6Z','B08Q26RV4D')

I get desired results

I want to know and understand why is the where in clause does not accept the values when given via a subquery.

I tried checking the data type of both asin and comma_separated_asin both have same data type VARCHAR(16777216)

I was expecting that data should come because i dont want to hardcode asin values, since it is a dynamic input.

Answer 1

Based on your question, I understand that you were running this query and it was working:

select
    distinct asin,
    ordered_revenue,
    report_date
from
    ams_cubes.campaigns_asin_workbench
where asin in
    ('B098GJ3K6Z','B08Q26RV4D');

So you tried to replace it with subquery but it returned 0 rows:

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

So this query should give the result you expected:

select
    distinct asin,
    ordered_revenue,
    report_date
from
    ams_cubes .campaigns_asin_workbench
where  contains((select comma_separated_asin from temp .asin_workbench_filtered), asin );