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 IN (‘Red’, ‘Blue’, ‘Green’)
      • Color = ‘Red’ OR ‘Blue’ OR ‘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’

     

    • 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)

     

    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
     

    Considerations and Limitations

    • Field names are case-insensitive, so (IsPartner = ispartner)
    • 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
    « Previous ArticleNext Article »


    Contact Us

    Sometimes you just want to talk to someone. Our customer support team is available by phone:

    Monday – Friday: 3:00 AM – 5:00 PM ET

    Sunday: 7:00 AM – 3:00 PM GMT