Possibility of getting wrong results with SOQL “IN” filter !

In Apex, we all create SOQL queries with “IN” clause for filtering on a set/list of data. An example SOQL fragment could be to filter out all Accounts matching a bunch of Fax numbers, i.e.

// Load contacts on the basis of your biz logic
Contact [] contacts = [Select Id, Fax from Contact WHERE Name like '%Xyz'......];
Set<String> faxes = new Set<String>();
// Populate a collection to further filter on Contact faxes
for (Contact c :  contacts) {
   faxes.add(c.Fax);
}
// Find all matching accounts against those contacts
Account[] accounts = [Select Id, Name, Fax from Account where Fax IN =: faxes];

As expected the above code snippet will give you all matching Accounts with same fax numbers as of Contacts. But this will not be the case always, you might get extra non-matching Accounts!

Wondering how ? This is possible if any of the Contact’s fax is NULL or EMPTY, so it will also match all the Accounts with NO fax numbers. Its possible because both Set<> and List<> can validly hold NULL objects and empty strings.

So to avoid any last minute surprises its always recommended to carefully construct collections passing in to SOQL “IN” filter. Above code can be fixed by adding a simple null & empty string check, as shown below

Contact [] contacts = [Select Id, Fax from Contact ......];
Set<String> faxes = new Set<String>();
for (Contact c :  contacts) {
    // FIX 1. Check fax for both NULL and EMPTY string
    if (c.Fax != null && c.Fax.trim().length() > 0)
        faxes.add(c.Fax);
}
// FIX 2. Query only if you have any fax numbers
if (!faxes.isEmpty())
    Account[] accounts = [Select Id, Name, Fax from Account where Fax IN =: faxes];

Seems trivial fix ? yes it is ! But its usually ignored by many of us.

Feedback

Would be glad to hear back on your views on this.