How can I filter with an array?

[hellyars]

I want to filter out multiple rows using their IDs.    I want to filter something along the lines [ID] !=('A1','A2',A3','B4',X5') without using the noob approach of having ten thousand AND statements.

Any quick ideas?

Thank you ?

[Dynamomo · Accepted solution]

Hi @hellyars,

You use the Value IN operator

!([ID] IN ('A1','A2',A3','B4',X5')) or [ID] NOT IN ('A1','A2',A3','B4',X5') 

will work

[Dynamomo]

However, if it is a really long list of IDs, I would probably just bring that list in as an input and then use a join and select whatever did NOT join (as those IDs are not the ones you want).

[hellyars]

Works perfectly for small sets.  Thank you.

[dmccandless]

For big sets is this the most performant method?

Joining gets the job done, but I was wondering if there is a way (and if it's more performant) that you can have something like a select sub-query.

For example:

select * from employee where employee_ID in

(

select employee_ID from employee_compensation where annual_comp > 200000

);

or similarly:

select * from employee where employee_ID not in

(

select employee_ID from employee_compensation where annual_comp > 200000

);

[dmccandless]

Trying to answer my own question - I guess one way to do this is using the find and replace tool.

[Dynamomo]

Yes a Find/Replace with a Filter after it would work.  Not sure if it would be faster.

Of course, if you had a really big dataset, you could use in-database tools as well and run the entire process in the database.

[robinsadhwani1602]

Could you provide the screenshot?

I am trying something like this.

Select column1, column2 from table 1 left join table 2 where id in (select id from table 1 inner join table 2 where id > 10)

[Dynamomo]

@robinsadhwani1602  I'm not a SQL pro but if what you are trying to do is join table 1 and 2 together where ID in table 1 is >10 (and just keep columns 1 and 2 from table 1) then I would use a filter on table 1 to select records where ID >10 then join the results of that to Table 2 and deselect the fields you don't want in the join tool.