Use Array in WHERE IN clause

zc · ‎08-22-2025

This is what I'm trying to do using SQL:

create table check1 as

select * from dataA

where IDs in ('12483258','12483871','12483883');

The list of IDs is much longer and may be changed so I want to use a variable for that. This is what I have tried

declare or replace ID ARRAY<string>;

SET VARIABLE ID = ARRAY('12483258','12483871','12483883');

create table check1 as

select * from dataA

where IDs in (ID);

And I get the error message below

data type mismatch: Input to `in` should all be the same type, but it's ["STRING", "ARRAY<STRING>"]

It appears that databricks won't compare IDs column to the array elements. I also tried split and explode but didn't work, maybe the syntax wasn't right.