topic Re: Use Array in WHERE IN clause in Data Engineering

Use Array in WHERE IN clause

zc — Fri, 22 Aug 2025 17:19:49 GMT

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.

Re: Use Array in WHERE IN clause

szymon_dybczak — Fri, 22 Aug 2025 17:46:41 GMT

Hi @,

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

Re: Use Array in WHERE IN clause

zc — Fri, 22 Aug 2025 17:55:39 GMT

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

Re: Use Array in WHERE IN clause

szymon_dybczak — Fri, 22 Aug 2025 18:12:56 GMT

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

Re: Use Array in WHERE IN clause

WiliamRosa — Fri, 22 Aug 2025 18:54:58 GMT

Hi @zc,

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

Simulating the error:

Solution with array:

Re: Use Array in WHERE IN clause

ManojkMohan — Fri, 22 Aug 2025 19:16:36 GMT

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

Re: Use Array in WHERE IN clause

zc — Fri, 22 Aug 2025 20:20:36 GMT

@ManojkMohan

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

Re: Use Array in WHERE IN clause

zc — Fri, 22 Aug 2025 20:20:14 GMT

@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!

Re: Use Array in WHERE IN clause

BS_THE_ANALYST — Sat, 23 Aug 2025 12:44:10 GMT

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

Re: Use Array in WHERE IN clause

zc — Mon, 25 Aug 2025 16:33:57 GMT

@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.