How Can Records Be Retrieved From An Object In SOQL Where The Value Of Field 1 Equals The Value Of Field 2?

Have you ever pondered the method for retrieving records from an object in SOQL where field 1 equals field 2?

There could be a need to retrieve a record from an sObject where field 1 (e.g., Firstname) is equal to field 2 (e.g., Lastname), but regrettably, the Salesforce SOQL framework does not allow direct field-to-field comparison.

So, how can we address this?

Although it is possible to achieve this using Apex and loops, the specific requirement is to accomplish it using SOQL.

List<Contact> contactsList = new List<Contact>();

for(Contact contact : [SELECT Firstname, Lastname FROM Contact]){
	if(contact.Firstname == contact.Lastname){
    	contactsList.add(contact);
    }
}

System.debug(' 🚀 ' + contactsList);

Now, turning to the request, the objective is to fulfill the same requirement using solely SOQL.

To achieve this requirement, a workaround is necessary, involving two steps.

Initially, a formula field needs to be created, incorporating a boolean value. The formula for this field will be structured as follows:

// name of the formula field is going to be NameCompare__c
IF(Contact.Firstname != Contact.Lastname, 'true', 'false')

Finally, the query will be as follows. Upon examination, it is a straightforward SOQL query with a WHERE clause that filters records based on the boolean value of the NameCompare__c field being false.

List<Contact> contacts = [SELECT Id FROM Contact WHERE NameCompare__c = false];