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.

I did that to increase insertion speed, otherwise, there will be 1000+ records inserted for each comma-separated value. — Ishank Sharma, Feb 22 '23 at 12:32
Why tag your question mysql if it is snowflake? This may help - https://stackoverflow.com/a/71245855/1191247 — user1191247, Feb 22 '23 at 12:50
How is asin column value stored in Table ams_cubes.campaigns_asin_workbench? In case these are not comma separated values and stored as individual record like one row per value ex: one row for B098GJ3K6Z, other row for B08Q26RV4D then your comma separated values column will not be able to evaluate to these rows values , use non comma separated values in that case for IN Sql statement. — akshindesnowflake, Feb 22 '23 at 13:05
Debug questions require a [mre]. [ask] [Help] PS in(...) is not in(string value that has column-like substrings separated by comma characters), it is in(table with one column) or in(values separated by commas) which is interpreted as a table by taking each value as a row. This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. — philipxy, Feb 22 '23 at 14:13

score -1 · Answer 1 · edited Feb 24 '23 at 11:58

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 );

Where IN Clause not working when given with a subquery

1 Answers1