Salesforce SOQL Logical Operators (AND, OR) with Examples

In Salesforce, SOQL (Salesforce Object Query Language) is the approach of querying data in a structured way. Now that you know how to use the logical operators such as AND & OR properly, we can ensure more precise and faster queries so that it returns only what is required. This article will help you understand the basics of SOQL logical operators, their syntax, and practical examples in a three-part tutorial.

Introduction to SOQL

Salesforce Object Query Language (SOQL) is similar to SQL (Structured Query Language) and is used to search your organization’s Salesforce data for specific information. It helps fetch data from one or more Salesforce objects (which are like tables in a database).

Basic SOQL Syntax

SELECT [fields]
FROM [object]
WHERE [conditions]

Example:

SELECT Id, Name, Email
FROM Contact
WHERE LastName = 'Smith'

This above query retrieves the Id, Name, and Email of all contacts with the last name “Smith”.

Logical Operators Overview

Logical operators are used to combine multiple conditions in the WHERE clause of a SOQL query. The basic logical operators are:

  • AND: Ensures that all conditions are true.
  • OR: Evaluates to true if at least one of the combined conditions is satisfied.
  • NOT: Reverses the logical state of a condition.

In this tutorial, we’ll focus on AND and OR.

Using the AND Operator

You can use the AND operator to require that all conditions must be satisfied. When you connect conditions using AND, only records that satisfy each defined condition are selected.

Syntax

SELECT [fields]
FROM [object]
WHERE [condition1] AND [condition2] AND ...

Example

Get All Contacts that are Active in the ‘Sales’ Department.

SELECT Id, FirstName, LastName, Department__c, Status__c
FROM Contact
WHERE Department__c = 'Sales' AND Status__c = 'Active'

Explanation:

  • Department__c = 'Sales': The contact must be part of the Sales department.
  • Status__c = 'Active': The contact should have an active status.
  • Both conditions need to be satisfied for a contact to be included in the results.

Using the OR Operator

The OR operator is used when you want at least one condition to be true. Using two or more conditions with OR will return records that match any of them.

Syntax

SELECT [fields]
FROM [object]
WHERE [condition1] OR [condition2] OR [condition3] ...

Example

Fetch All Contacts that are Active or Part of the Marketing Department.

SELECT Id, FirstName, LastName, Department__c, Status__c
FROM Contact
WHERE Status__c = 'Active' OR Department__c = 'Marketing'

Explanation:

  • Status__c = 'Active': The contact has an active status.
  • Department__c = 'Marketing': The contact belongs to the Marketing department.
  • Either condition being true will include the contact in the results.

Combining AND and OR Operators

Using a single SOQL query, you can mix AND and OR operators to build complex conditions. It’s essential to use parentheses () to group conditions and define the order of evaluation.

Syntax

SELECT [fields]
FROM [object]
WHERE ([condition1] AND [condition2]) OR ([condition3] AND [condition4])

Example

Retrieve All Active Contacts in the ‘Sales’ Department or Inactive Contacts in the ‘Marketing’ Department.

SELECT Id, FirstName, LastName, Department__c, Status__c
FROM Contact
WHERE (Department__c = 'Sales' AND Status__c = 'Active') 
   OR (Department__c = 'Marketing' AND Status__c = 'Inactive')

Explanation:

  • First Group: Department__c = 'Sales' AND Status__c = 'Active'
  • Second Group: Department__c = 'Marketing' AND Status__c = 'Inactive'
  • A contact is included if it satisfies either the first group or the second group of conditions.

Real-life Use Cases for Using Salesforce

How can logical operators be harnessed in SOQL queries within Salesforce to leverage real-world scenarios?

Example 1: Opportunities Filter

Use Case: Fetch all Open Opportunities of Account: ‘Enterprise’ or Stage: ‘Strategic’.

SELECT Id, Name, StageName, Account.Type, IsClosed
FROM Opportunity
WHERE (Account.Type = 'Enterprise' AND IsClosed = FALSE)
   OR StageName = 'Strategic'
ORDER BY CreatedDate DESC NULLS FIRST

Explanation:

  • First Condition: Opportunities linked to Enterprise accounts that are not closed.
  • Second Condition: Opportunities in the Strategic stage, regardless of account type or closed status.
  • The query returns opportunities that meet either of these criteria.

Example 2: Selecting Cases

Use Case: Get all Cases that are either High Priority and Unresolved, or Assigned to a particular Support Agent.

SELECT Id, CaseNumber, Priority, Status, Owner.Name
FROM Case
WHERE (Priority = 'High' AND Status != 'Resolved')
   OR Owner.Name = 'Jane Doe'

Explanation:

  • First Condition: Cases with High priority that are not yet resolved.
  • Second Condition: Cases assigned to Jane Doe.
  • The query retrieves cases that are either high priority and unresolved or are assigned to Jane Doe.

Example 3: Contact Segmentation

Use Case: Retrieve Contacts who are either VIP customers in the ‘Finance’ industry or have made purchases over $10,000.

SELECT Id, Name, Industry__c, Is_VIP__c, Total_Purchases__c
FROM Contact
WHERE (Is_VIP__c = TRUE AND Industry__c = 'Finance')
   OR Total_Purchases__c > 10000

Explanation:

  • First Condition: Contacts marked as VIP in the Finance industry.
  • Second Condition: Contacts with total purchases exceeding $10,000.
  • The query selects contacts meeting either of these conditions, useful for targeted marketing campaigns.

Best Practices and Tips

  1. Use Parentheses for Clarity:
    • When combining AND and OR, use parentheses to clearly define the logic and ensure the query behaves as expected.
  2. Optimize Query Performance:
    • Place the most selective conditions first to reduce the number of records processed.
    • Use indexed fields in your WHERE clause to improve performance.
  3. Limit the Number of Records:
    • Use the LIMIT clause to restrict the number of returned records, especially during testing.
    • Example:
      SELECT Id, Name
      FROM Account
      WHERE Industry = 'Technology'
      LIMIT 10
  4. Avoid Using SELECT *:
    • Explicitly specify the fields you need to retrieve. This reduces the amount of data transferred and improves query performance.
  5. Test Complex Queries:
    • Break down complex queries into simpler parts to test each condition individually before combining them.
  6. Use Consistent Naming Conventions:
    • Clearly name your custom fields and objects to make your queries more readable and maintainable.
  7. Stay Within Governor Limits:
    • Salesforce enforces limits on the number of SOQL queries and the amount of data processed. Ensure your queries are efficient to stay within these limits.

SOQL Logical Operator AND Example

Logical Operator “AND” is used to retrieve the records when all the conditions in SOQL statement is satisfied. If any  of the condition in SOQL statement is not satisfied records will not be retrieved.

  • SELECT name, Amount FROM opportunity WHERE Amount > 1000 AND Amount < 10000
soql logical opertors

SOQL Logical Operator OR Example

Logical operator OR is used to retrieve the data if any of the condition in SOQL statement is satisfied. SOQL logical operator “OR” matches the first condition with second condition and data will be retrieved.

soql logical opertors1

SELECT name, Amount FROM opportunity WHERE name = ‘express logistics‘  OR name = ‘GenePoint SLA’