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.
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.
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.
Hope this helps!