How to provide an array in IN clause presto

user_12918044 0
Viewed 920

I have the following query

select * 
from Table1 
where NUMid in (select array_agg(NUMid) 
                from Table2 
                where email = 'xyz@gmail.com')

My intention is to get the list of all the NUMids from table2 having an email value equal to xyz@gmail.com and use those list of NUMids to query from Table1.

But I am getting the following error:

value and result of subquery must be of the same type for IN expression: bigint vs array(bigint)

Basically IN clause cannot take in an array. Is there workaround for this problem.

presto amazon-athena in-clause sql
1 Answers

If I understand correctly, you don't even need to call ARRAY_AGG here, just use a standard WHERE IN (...) construct:

SELECT *
FROM Table1
WHERE NUMid IN (SELECT NUMid FROM Table2 WHERE email = 'xyz@gmail.com');

You could also phrase this using exists logic, for a possible performance enhancement:

SELECT *
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM Table2 t2
              WHERE t2.NUMid = t1.NUMid AND t2.email = 'xyz@gmail.com');
user_18632290
Related