Re: 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.

szymon_dybczak · ‎08-22-2025

Hi @,

This is expected. Your id column is of type string and you're trying to provide in in clause array<string>

zc · ‎08-22-2025

Thanks for responding. So what's the proper way of using an array there?

szymon_dybczak · ‎08-22-2025

I don't have access to computer right now, but I'll provide you tomorrow code that should work

WiliamRosa · ‎08-22-2025

Hi @zc,

I managed to reproduce your scenario and put together a possible solution—see if it helps.

Simulating the error:

Solution with array:

Wiliam Rosa
Data Engineer | Machine Learning Engineer
LinkedIn: linkedin.com/in/wiliamrosa

zc · ‎08-22-2025

@WiliamRosa

Thank you for this, your solution works but in a different way. I've actually been using temp views in the same way, but that requires extra steps that's why I wanted to go for variables instead. But thank you for putting this together!

ManojkMohan · ‎08-22-2025

Root Cause

In your SQL statement, where IDs in (ID) is comparing a string column (IDs) with an array variable (ARRAY<STRING>).
Databricks SQL does not allow IN to directly take an array — IN expects a list of scalar values, not an array object.

That’s why you see the error

Solution:

Try Using array_contains to structure your query.

Use array_contains

If you’re filtering using an array variable:

DECLARE ids ARRAY<STRING>;

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

CREATE TABLE check1 AS

SELECT *

FROM dataA

WHERE array_contains(ids, IDs);

View solution in original post

zc · ‎08-22-2025

@ManojkMohan

Thank you so much for your detailed explanation and perfect solution!

BS_THE_ANALYST · ‎08-23-2025

Nice solutions! @ManojkMohan @WiliamRosa

I love the use of the temp view for the intermediate result. The array_contains is also a really nice touch.

@ManojkMohan when you write "SET VARIABLE ids = ARRAY('12483258','12483871','12483883');" ... can this array be from another query to make it dynamic?

Really cool stuff.

@zc I guess we could also use parameters: https://docs.databricks.com/aws/en/sql/user/queries/query-parameters#query-based-dropdown-list . The benefit of this is that the query can be maintained/updated outside of the script, given that you used a query based drop down list parameter. I haven't tried it but it makes sense, in theory. Example of using a parameter below:

All the best,
BS

zc · ‎08-25-2025

@BS_THE_ANALYST

Thank you for responding. Yeah I guess that's an option too. Talking about a list maintained outside of the script though, I'd prefer having a csv and import it as a temp view when needed.