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 ยป


        Contact Us

        Sometimes you just want to talk to someone. Our customer support team is available by phone:
        Request a Technical Support Call Back