SOQL Comparison Operators

SOQL Tutorial What is SOQL SOQL Real Life Scenario SOQL Naming Conventions SOQL WHERE SOQL Comparison Operators SOQL Logical Operators SOQL ORDER BY SOQL LIMIT SOQL OFFSET SOQL Aggregate Functions SOQL GROUP BY SOQL HAVING SOQL Relationship Queries SOQL Date Literals SOQL Date Functions SOQL History Objects SOQL Sharing Objects SOQL in APEX Apex Variables in SOQL Queries Static and Dynamic SOQL SOQL return types SOQL Governor Limits Efficient SOQL Queries

The most straightforward approach to filter data is by using comparison operators.

The examples used in this section refers to the data model we introduced earlier.

Equal to =

This operator returns True when the value in the field matches the ‘value’ specified. For Strings, the match is case sensitive.

Example: Find all the clinics in Florida

SELECT Id, Name
FROM Clinic__c
WHERE State__c = 'Florida' 

Not Equal to !=

This operator returns True when the value in the field does not match the ‘value’ specified.

Example: Find all the patients who do not have their email id registered

SELECT Id, FirstName
FROM Contact
WHERE Email != null

Note that null or Null (case insensitive) is a special value to denote a field without any value in SOQL.

Greater than >

This operator returns True when the value in this field is greater than the ‘value’ specified in filter_expression.

Example: Find clinics which accept more than 100 appointments a day

SELECT Id,Name
FROM Clinic__c
WHERE Max_no_of_appointments__c > 100

Less than <

This operator returns True when the value in this field is less than the ‘value’ specified in filter_expression.

Example: Find all the patients with age more than 65 years.

SELECT Id, Name
FROM Contact
WHERE BirthDate < 1954-01-01

Greater than or equal to =<

This operator returns True when the value in this field is greater than or equal to the ‘value’ specified in filter_expression.

Example: Find clinics which accept 100 or more appointments a day

SELECT Id,Name
FROM Clinic__c
WHERE max_no_of_appointments__c >= 100

Greater than or equal to <=

This operator returns True when the value in this field is less than or equal to the ‘value’ specified in filter_expression. Example: Find clinics that accept up to 10 appointments a day.

SELECT Id,Name
FROM Clinic__c
WHERE max_no_of_appointments__c <= 10

SOQL LIKE operator

LIKE operator is similar to the SQL like operator. It is used to match a substring in the field’s values. It returns True if the string specified in the ‘value’ is present as a substring in the field’s value. The value specified can also contain special wildcard characters.

• The % wildcard denotes one or more characters.
• The _ wildcard denotes a single character.
• The % and _ can be matched with an escape character (\% , _).
• The LIKE operator can only work with string values.
• The search is case-insensitive, which is unlike String ‘equals to’ matching.
• The like operator is a complex operator and should be used wisely, as it can put a lot of load on the server.

Example #1: Find all patients whose MRN begins with 70. They denote patients in Florida.

SELECT Id, Name
FROM Contact
WHERE MRN__c
LIKE '70%'

Example #2: Find all patients whose first name has a second letter equal to ‘u’.

SELECT Id, Name
FROM Contact
WHERE Firstname
LIKE '_u%'

SOQL IN operator

The IN operator allows you to match a field’s value to multiple specific values in a single filter expression. The multiple values are written inside parentheses, each value enclosed in single quotes and separated by commas. It returns True if any one of the specified values matches the field’s value. IN operator works only on strings and is case sensitive.

Example: Find all the clinics in New York, Washington and Denver.

SELECT Id,Name,city__c
FROM Clinic__c
WHERE city__c IN ('New York', 'Denver', 'Washington')

SOQL NOT IN operator

The NOT IN operator, on the other hand, returns True if the value in the field does not match any of the specified values.

Example: Find all clinics which are not in the megacities New York, Washington and Denver.

SELECT Id,Name,city__c
FROM Clinic__c
WHERE city__c NOT IN ('New York', 'Denver', 'Washington')

TIPS

There is no limit on the number of values inside the IN clause. The only limit would be that you cannot exceed the max size of the SOQL query (20,000 characters)

SOQL IN operator with subqueries

In the previous section, we used the IN operator to match a field’s value with a list of values specified in brackets.

The IN operator can also be used with a subquery to match a field’s value with a list of values present in another query.

Example #1: Find all the clinics which had at least one appointment in the previous month.

SELECT Id, Name
FROM Clinic__c
WHERE Id IN (SELECT Clinic__c
  FROM Appointment__c
  WHERE Appointment_time__c = LAST_MONTH)

Here only those clinics will be returned whose ID we can also find on the Clinic__c field in a list of appointments made in the previous month.

Similarly, we can apply the NOT operator before IN:

Example #2: Find all the clinics which did not have any appointments in the previous month

SELECT Id,Name
FROM Clinic__c
WHERE Id NOT IN (SELECT Clinic__c
  FROM Appointment__c
  WHERE Appointment_time__c = LAST_MONTH)

Important

While using IN clause with a subquery, only an ID field or a Relationship field, can be compared from the subqueries object. Otherwise, an SOQL error is thrown.