Let's say that I have to query Contact records with:
FirstName as A, LastName as B, Company as C, or
FirstName as K, LastName as L, Company as M, or
FirstName as X, LastName as Y, Company as Z.
How I satisfy this requirement is that, I create three Set collections for each column values:
Set<String> firstNameSet = new Set<String>{'A', 'K', 'X'};
Set<String> lastNameSet = new Set<String>{'B', 'L', 'Y'};
Set<String> companySet = new Set<String>{'C', 'M', 'Z'};
Then querying all possible combinations in Contact object:
Contact[] contactList = [SELECT Id, FirstName, LastName, Company FROM Contact WHERE FirstName IN : firstNameSet AND LastName IN : lastNameSet AND Company IN : companySet];
Lastly, I (usually) run a nested loop and extract the matching Contact records from contactList collection (showing only X, Y, Z requirement):
Contact[] contactListMatching = new List<Contact>();
for (Contact c : contactList) {
if (c.FirstName='X' AND c.LastName='Y' AND c.Company='Z')
contactListMatching.add(c);
}
I am wondering if there's a more optimal way to do this query. This approach increases CPU time when the dataset is big. I would like to transfer some of processing time to the SOQL operation, if possible.