[SalesForce] Multiple WHERE IN conditions in SOQL query

Can I use the below syntax in SOQL query.

set<String> firstName = new set<String>();
set<String> lastName =  new set<String>();
set<String> ssnNumber = new set<String>();

[select firstName,lastName,ssnNumber from Nominee where firstName IN :firstName AND
                                                        lastName  IN : lastName AND
                                                        ssnNumber IN  :ssnNumber]

can i use the above format of SOQL query with multiple WHERE in conditions.

Best Answer

For this type of question, a pretty simple "can I do X?", the fastest way to get an answer is usually to just go and try to do it yourself through anonymous apex.

The short answer here is yes, you can do this, and in exactly the way that you laid out in your example.

The longer answer is yes, and you need to keep in mind that you're going to get the cartesian product of your filters.

I.e. if you have {'John', 'Vijay', 'Andrei'} in your firstName set
and {'123-45-6789' ,'123-45-6790'} in your ssnNumber set
you'll get results for:

  • John + 123-45-6789
  • John + 123-45-6790
  • Vijay + 123-45-6789
  • Vijay + 123-45-6790
  • Andrei + 123-45-6789
  • Andrei + 123-45-6790

if they exist.

SOQL is unable to do things like "find records that match index I in all of these collections". If that's what you're looking to do, then you'll need to do some extra work (like further filtering the query results in Apex, or creating a formula field on your object to act as a "composite key" of sorts)

Related Topic