Field-to-Field Comparison in a Salesforce SOQL WHERE Clause — Workaround Using Formula Fields

This article describes two approaches to filter records based on field-to-field comparisons in Salesforce SOQL.

Workaround: Use a Formula Field to Pre-Evaluate the Comparison

Since SOQL cannot compare two fields directly in a WHERE clause, create a formula field on the object that evaluates the comparison and returns a text value. You can then filter on the formula field's return value in the SOQL query.
Example — compare FirstName and LastName on the User object:

1. Create a formula field on the User object with return type Text. Name it NameCompare.
2. Set the formula to evaluate whether the two fields differ and return a text string:

IF(User.FirstName != User.LastName, 'true', 'false')

This formula returns the text string 'true' when FirstName and LastName are different, and 'false' when they match.

1. Update your SOQL query to filter on the formula field value instead of comparing the fields directly:

List<User> Users = [SELECT id, name FROM User where NameCompare = 'true'];

Spring '20 Update: Field-to-Field Filters in Reports

As of the Spring '20 release, Salesforce Reports support field-to-field filters natively. This allows you to create a report on the User object and filter by comparing FirstName and LastName directly, without needing a formula field. Note that this feature applies to Reports only — SOQL queries in Apex still require the formula field workaround described above. See: Filter Reports by Field Comparisons with Field-to-Field Filters