How to fetch records from an object where field 1 = field 2 only using soql?

There might be a requirement to fetch a record from a sObject where field 1 (say Firstname) is equal to field 2 (say Lastname) and unfortunately, the salesforce SOQL framework doesn’t support field-to-field comparison.

Then how do we do it?

We can definitely do it using apex and loops as shown below. However, the ask is to achieve it via 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 coming to the ask which is achieving the same requirement using only SOQL.

There's a bit of a workaround that's involved here. I will achieve this requirement in 2 steps.

First, I need to create a formula field that folds a boolean value and the formula in it is going to be like this.

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

Last, the query is going to be as follows. If you look at it, it's a plain old SOQL query with a WHERE clause in it that's used to filter the records whose NameCompare__c field is boolean false.

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

Hope this helps!