Loading

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

Publish Date: May 28, 2026
Description

 

Salesforce SOQL does not support direct field-to-field comparison in a WHERE clause. Attempting to compare two fields of the same object on the right-hand side of a WHERE condition returns a QueryException error.
For example, the following SOQL query attempts to compare the FirstName and LastName fields on the User object directly in the WHERE clause:

List<user> users = [SELECT Id, name FROM User WHERE (FirstName != Lastname)];

This query returns: "System.QueryException: unexpected token: 'Lastname'"
Salesforce does not evaluate the right-hand side of a WHERE condition as a field reference — it expects a literal value. This is a known platform limitation.

 

Resolution

 

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

 

Additional Resources

Vote and comment on the Salesforce Idea for Field-to-Field filters for SOQL.

Knowledge Article Number

000386076

 
Loading
Salesforce Help | Article