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
       

       

        Next Article ยป