Development questions

Write a SOQL query to find all Contacts where the Contact's OwnerId is different from its Parent Account's OwnerId (Explain why SOQL cannot directly compare two fields like WHERE OwnerId != Account.OwnerId and how to solve it via a Formula checkbox field).

SOQL cannot compare two fields in a WHERE clause (e.g. WHERE OwnerId != Account.OwnerId is invalid); filters compare a field with a literal or bind variable only.

Solutions:

  1. Create a formula checkbox on Contact, e.g. Owner_Differs__c = OwnerId <> Account.OwnerId, then query WHERE Owner_Differs__c = true. Formula fields aren't indexed, so it may be non-selective on large data.
  2. Query both fields and compare in Apex:
apex
for (Contact c : [SELECT Id, OwnerId, Account.OwnerId FROM Contact WHERE AccountId != null]) {
    if (c.OwnerId != c.Account.OwnerId) { /* mismatch */ }
}
soqlscenariolimitationsformula-fields