Using SOQL to Query Salesforce Database: A Comprehensive Guide

Introduction

Every company needs to keep track of data about their customers and partnerships and this makes CRMs very popular. Salesforce is a cloud-based CRM platform where you can store a bunch of data and one that gives you access to this data from any location in the world. Whether it’s Sales, Marketing, or anything else you use to connect with your  customers, it is possible using the Salesforce platform.

There may be times when you want to see this data in its raw form and to query it on specific criteria. In this post, you’re going to 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. Here is what you will be covering in this post:

• sObject
• SOQL
• When to Use SOQL
• Prerequisites
• The Simple SOQL Structure
• Parent to Child Queries (Inner Query) in SOQL
• Child to Parent Query in SOQL
• Order By in SOQL
• Group By in SOQL
• Having in SOQL
• Aggregate Queries in SOQL
• Polymorphic Queries in SOQL
• Limitations
• Conclusion

sObject

At the core of the Salesforce data model is something referred to as the Salesforce Object or sObject.  sObject has a table-like data structure. It’s organized into a concept of Records and Fields which are similar to columns in a database table.

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 is the one you are going to use to query the Salesforce sObject layer for specific information. It has a SQL-like syntax that supports only SELECT statements (Read-only).

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

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.

When it comes to fully managed ETL, you can’t find a better alternative than Hevo. It is a No-code Data Pipeline product that will help you move data from multiple data sources like Salesforce to your destination for free. It is straightforward to set up as you can get the tool up and running in a jiffy.

With Hevo, you can get started in just a couple of minutes as all you need to do is select your data source, provide the user credentials to the data source, and the Hevo platform will do the rest in extracting the data and loading it into the specified destination.

Visit our Website to Explore Hevo

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!