Using SOQL to Query Salesforce Database: A Comprehensive Guide

on Data Integration, Tutorials • September 10th, 2020 • Write for Hevo

QUERY SALESFORCE DATABASE

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:

Hevo, A Simpler Alternative to Integrate your Data for Analysis

Hevo offers a faster way to move data from databases or SaaS applications like Salesforce into your data warehouse to be visualized in a BI tool for free. Hevo is fully automated and hence does not require you to code.

HEVO: UNIFIED DATA PLATFORM
Get Started with Hevo for Free

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources(Including Free Sources like Salesforce) that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support call.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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
  1. Knowledge of relational database concepts such as:
  • Tables
  • Indexes
  • Joins
  • Primary/Foreign Key
  1. Knowledge of the Salesforce Developer Console and Query Editor. You’ll be using the Developer Console to run these queries.
  2. To open the Developer Console from Salesforce Classic:
  • Click Your Name.
  • Select Developer Console.
  1. 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!

No-code Data Pipeline for your Data Warehouse