SOQL: How to query for identical field values? ( SELECT form Object WHERE field1 = field2 )

Question

I found a redundant Owner field in an Org, which I want to check if it's matching the standard Owner field or not. A very simple SOQL query - at least I thought:

SELECT Account_Owner__c,OwnerId,Id,Name 
FROM Account
WHERE Account_Owner__c = OwnerId

But getting:

MALFORMED_QUERY:
WHERE Id = Name
ERROR at Row:3:Column:12
Bind variables only allowed in Apex code

Is it not possible in SOQL to match fields against other fields of the same record?

Answer 1

You can not compare 2 fields in SOQL. You can do the check in code by doing something similar to this:

for ( Account a : [SELECT Account_Owner__c,OwnerId,Id,Name 
                             FROM Account
                             WHERE Account_Owner__c != null 
                             and OwnerId != null]) {

    if (a.Account_Owner__c != a.OwnerId) {
        // do something
    }
}

Or you can add a boolean formula field called something like AccountOwnerSameAsOwner which returns true when both fields are the same. You will then be able to do the following:

SELECT Account_Owner__c,OwnerId,Id,Name 
FROM Account
WHERE AccountOwnerSameAsOwner__c = true 

Answer 2

It's already answered here:

Error in WHERE clause of SOQL

Unfortunately that query cannot be executed as written in SOQL since SOQL does not allow direct field comparisons. I would recommend adding a checkbox formula field to your object