CODEWITHSUNDEEP
sqlamazon-athenaprestoin-clause
prathik vijaykumar
prathik vijaykumar

Reputation: 385

How to provide an array in IN clause presto

I have the following query

select * 
from Table1 
where NUMid in (select array_agg(NUMid) 
                from Table2 
                where email = '[email protected]')

My intention is to get the list of all the NUMids from table2 having an email value equal to [email protected] 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.

Upvotes: 1

Views: 4636

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522032

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 = '[email protected]');

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 = '[email protected]');

Upvotes: 2

Related Questions