Using SOQL to Query Salesforce Database: A Comprehensive Guide

Learn how you can query Salesforce database to uncover valuable insights from your customer data. 

To query Salesforce database, you have 3 options:

1. DML or Data Manipulation Language
2. Salesforce Object Query Language (SOQL)
3. Salesforce Object Search Language (SOSL).

After reading this post, you will be in a position to understand what is SOQL and how you can execute SOQL to query Salesforce database using the Query Editor in the Developer Console.

sObject

The main features of sObject include:

• An extensible data model. You can extend the data model to build custom fields.
• Queryable.

Standard sObjects include:

• Account
• Contact
• Lead
• Opportunity
• Case

Standard sObject fields include:

• Id
• Name
• CreatedBy/Date
• ModifiedBy/Date
• OwnerId
• IsDeleted

SOQL

The Salesforce Object Query Language or SOQL can be used in:

• Apex Web Services API
• Developer tools e.g. Eclipse, Visual Studio, Salesforce developer console, etc.
• API calls (REST, SOAP, etc.)

The main differences between SOQL and SQL are quite subtle and they include:

• No wild cards such as SELECT * statements and this is implemented to avoid impacting the performance of Salesforce production servers.
• No views.
• SOQL is read-only and does not support DML operations such as INSERTS, DELETES, or UPDATES.
• Limited indexes.
• Object relational mapping is automatic.
• Schema changes are protected.

When to Use SOQL

• When retrieving data from a single object or multiple objects that are related to one another.
• When counting the number of records that meet a specified criteria.
• Sorting results as part of a query.
• When retrieving data from number, date, or checkbox fields.

Prerequisites

To follow along, you need to have:

1. A basic understanding of the Salesforce.com platform including:

• Creating custom objects and Fields
• Knowledge of the Salesforce Field API names

2. Knowledge of relational database concepts such as:

• Tables
• Indexes
• Joins
• Primary/Foreign Key

3. Knowledge of the Salesforce Developer Console and Query Editor. You’ll be using the Developer Console to run these queries.
4. To open the Developer Console from Salesforce Classic:

• Click Your Name.
• Select Developer Console.

5. To open the Developer Console from Lightning Experience:

• Click the Gear Icon.
• Select Developer Console.

The Simple SOQL Structure

SELECT one or more fields, [SubQuery]
FROM an object
WHERE filter statements
GROUP BY
HAVING Count(Email) > 2
ORDER BY CreatedDate ASC
LIMIT 10
OFFSET 5
FOR {VIEW, UPDATE}

Parent to Child Queries (Inner Query) in SOQL

An Inner Query is normally used when you want to retrieve the related child record for a particular parent record or set of parent records.

For example, to pull the Contacts related to a particular Account, you can use the following SOQL query:

SELECT Id, Name, (Select Id, Name FROM Contacts) FROM Account WHERE Id IN:accList

In the query above, Contacts is the relationship name. For standard Salesforce objects, the relationship name is always the plural label of the object name. For example:

• Contact => Contacts
• Case => Cases
• Opportunity => Opportunities

For custom objects, we append __v which will represent the relationship. For example, if the custom object name is Book and it has a lookup relationship with Account, then in the inner query it will be Books__v.

Child to Parent Query in SOQL

Sometimes you may want to fetch a few fields of the parent record while working with child records. In this case, we go for a child to parent query.

For example, while working with Contacts records, you can retrieve the Account Shipping address from a particular Contact using the following SOQL:

SELECT Id, Name, AccountId, Account.ShippingAddress FROM Contact

Order By in SOQL

Order By is used when you want to get fields ordered in ascending or descending format.

SELECT Name, CreatedDate, Account Name
FROM Account
ORDER BY Name DESC
LIMIT 5

Group By in SOQL

Group By is used in conjunction with an aggregate function to group the result-set by one or more columns.

SELECT Name, Max(CreatedDate)
FROM Account
GROUP BY Name
LIMIT 5

Having in SOQL

‘Having’ is used to find out the duplicate records based on a particular field. For example, to find duplicate Contacts, use the following query:

SELECT AccountId FROM Contact GROUP BY AccountId
HAVING Count(Id) > 2

To find duplicate emails, run the following query:

SELECT Email FROM Contact
GROUP BY HAVING Count(Email) > 1

Learn about Salesforce Connect.

Aggregate Queries in SOQL

These are used to find the aggregate result for a field. Below are a few methods that are supported in Aggregate Queries:

• Count
• Sum
• Max
• Min
• AVG

For example:

SELECT Count(Id), AccountId
FROM Contact
GROUP BY AccountId

Polymorphic Queries in SOQL

A polymorphic relationship is one where the current object can be one of several object types depending on a related Event, for example:

SELECT I'd
FROM Event
WHERE What.Type IN('Account', 'Opportunity')

Limitations

When you query Salesforce database, query usage is explicitly metered. There are:

• API batch limits for the number of records that can be returned.
• Apex governor limits.

Conclusion

• Congratulations, you just learnt what SOQL is, the different types of queries that are supported in SOQL, and when to use them.
• Hopefully you will be in a position to query Salesforce database using this information.

Would you like to give Hevo a try? Sign Up for a 14-day free trial today!

Share your thoughts on how to query Salesforce database in the comments below!