SQL Where Clause Examples

Use SQL statements to return a collection of records that match your criteria.

• Filter all Account records where the 'IsPartner' field is checked:
  • isPartner = 'true'
  • Note that the IsPartner field on the Account object will be available only in Orgs where partner relationship management (partner portal) is enabled.

• Only keep records that have two specific Record Types:
  • RecordTypeId in ('01235000001BJrlAAW', '01215000001BJrLAAW')

• Only keep records that their name field contains the word 'acme':
  • Name LIKE '%acme%'

• Only keep records where a custom field (i.e., 'Value__c') is empty:
  • Value__c = ''

• Only keep records that have a value between 2 values (i.e., between 100 and 200):
  • cast(Value__c as integer) > 100 AND cast(Value__c as integer) <= 200

• Keep records that meet one of several conditions (Red, Blue or Green):
  • Color__c IN ('Red', 'Blue', 'Green')
  • Color__c = 'Red' OR Color__c = 'Blue' OR Color__C = 'Green'

• Keep records that must meet several conditions:
  • Cast(Height as integer) > 15 AND cast(Width as integer) < 5 AND cast(Depth as integer) >= 10

• Keep records that do not match:
  • Country != 'USA'
  • Country NOT IN ('USA', 'Canada')

• Replicate Accounts based on a checkbox and bring the parent account of those accounts:
  • WHERE CopyToDev__c = 'true' OR Id IN (SELECT ParentId FROM Account WHERE CopyToDev__c = 'true')

• Only keep records that were created on a specific date (i.e. November 27, 2017):
  • CreatedDate = '2017-11-27'

• Only keep records that were NOT created on a specific date:
  • CreatedDate != '2017-11-27' 

• Find all records added after a specific date:
  • CreatedDate > '2019-09-16T00:00:00.000Z'

Note: that you will need to update the date above to whichever date is applicable.
 

• Replicate a subset of record for each RecordType, for example, 5 records of each of my 3 Accounts RecordType
  • Id IN (SELECT Id FROM Account WHERE RecordTypeId='A' LIMIT 5) OR Id IN (SELECT Id FROM Account WHERE RecordTypeId='B' LIMIT 5) OR Id IN (SELECT Id FROM Account WHERE RecordTypeId='C' LIMIT 5)

• Replicate a subset of records where we have 2 Parents and related Junction Object
  • To illustrate that example, let’s consider we have Account and Survey as a Parent object and an object called Survey_Answer that is our Junction Object.

• On the Replicate Job, select Account and Survey
• Click on the gear next to Account and select a subset of date you would like
• Click on the gear next to the Parent Object Survey and run the following query: Id in (SELECT Survey__c FROM Account$Survey_Answer__c)
• Click on the gear of Survey_Answer (Under the object Survey) and run the query: Id in (Select Id from Account$Survey_Answer__c)

Considerations and Limitations

• Field values are case-sensitive e.g. Color__c IN ('Red', 'red') "Red", and "red" are considered two different values.
• Field names are case-insensitive, so (IsPartner = ispartner)
• Custom field names must be in the API name format; e.g. Field_Name__c
• When using the 'IN' operator with record IDs, you must use the 18-char version (not the 15 char)
• When using the 'LIKE' operator with records IDs, you can use the 15-char format, because this looks for a partial match
• The '%%' operator is case insensitive
• Performing a where clause on lookup fields requires the use of the record id as the value e.g. Lookup__c = '01215000001BJrLAAW'

Comparison Operators 

=                      Equals
!=                     Not equals
<                      Less than
<=                    Less than or equal to
>                      Greater than
>=                    Greater than or equal to
LIKE                 Like
IN                     In
NOT IN             Not In