SOQL Fundamentals

Introducing the core concepts and structure of SOQL queries.

Basic syntax

The basic syntax of a SOQL query is straightforward. Here’s a simple example:

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

This query retrieves the Name and Email fields from the Contact object where the LastName is ‘Smith’.

Key components of SOQL

Mandatory components

• SELECT: Starts the query and specifies the fields to retrieve.
• FROM: Specifies the object from which to retrieve data.

With these clauses, we can retrieve all records of an object:

SELECT Id, Name FROM Account

Optional components

• WHERE: Specifies the conditions that the data must meet (optional).

Now we can start to filter the records we retrieve from the database:

SELECT Name, Phone FROM Account WHERE Industry = 'Technology'

Here instead of all Account records being returned, only those records where the Industry is ‘Technology’ are returned. Records with a null value in the Industry field will not be included. The = operator specifies that only records with an exact match to ‘Technology’ will be returned.

Additional filters can be added using the AND & OR operators.

• ORDER BY: Specifies the order in which to return the results (optional).

Often when we are retrieving records from the database we require them to be returned in a specific order.

SELECT Name, AnnualRevenue FROM Account ORDER BY AnnualRevenue DESC

This query retrieves the Name and AnnualRevenue fields from Account records and orders them by AnnualRevenue in descending order. With this query, we will get the Accounts with the highest annual revenue returned at the top of the list. Another example could be to get the latest the newest records at the top.

• LIMIT: Limits the number of records returned (optional).

To limit the number of records returned, use the LIMIT clause:

SELECT Name FROM Contact WHERE LastName = 'Smith' LIMIT 10

This query retrieves the Name field from the first 10 Contact records where the LastName is ‘Smith’. 10 is the maximum number of records that will be returned, e.g. if only 7 contacts existed with the LastName of Smith, only those 7 records will be returned.

API Names vs Field Labels

In SOQL, you must use API names, not the field labels you see in the Salesforce interface.

Standard Fields

Field Label: "Account Name" → API Name: "Name"
Field Label: "Created Date" → API Name: "CreatedDate"
Field Label: "Annual Revenue" → API Name: "AnnualRevenue"

Custom Fields

Field Label: "Customer Priority" → API Name: "Customer_Priority__c"
Field Label: "Project Status" → API Name: "Project_Status__c"

The same applies for objects:

Standard Objects

Object Label: "Account" → API Name: "Account"
Object Label: "Opportunity" → API Name: "Opportunity"
Object Label: "Contact Point Email" → API Name: "ContactPointEmail"
Object Label: "Advanced Account Forecast Fact" → API Name:"AdvAccountForecastFact"

Custom Object

Object Label: "Project" → API Name: "Project__c"
Object Label: "Custom Object" → API Name: "MyPackage__Custom_Object__c" (Managed package object)

Best Practices

Select Only Needed Fields

Only select fields you actually need to improve performance and stay within governor limits.

Include Id When Needed

Explicitly include Id in your SELECT clause when you need it for further processing.

Trying Out SOQL

The easiest way to try SOQL is probably through the Salesforce Developer Console. Alternatively, tools like Salesforce Inspector provide a user-friendly interface to build and run your queries.

Using the Developer Console

1. Open the Developer Console: In Salesforce, click on your profile picture in the top right corner and select “Developer Console”.
2. Navigate to the Query Editor: In the Developer Console, go to Query Editor at the bottom.
3. Write and Execute Your Query: Type your SOQL query into the editor and click “Execute” to see the results.

Using Salesforce Inspector

Salesforce Inspector is a Chrome extension that simplifies the process of building and running SOQL queries.

1. Install Salesforce Inspector: Add the Salesforce Inspector extension from the Chrome Web Store.
2. Open Salesforce Inspector: Click on the Salesforce Inspector icon in your browser while logged into Salesforce and press ‘Data Export’.
3. Build Your Query: Use the intuitive interface to build your SOQL query and execute it to see the results.

Conclusion

SOQL is an essential tool for Salesforce developers and administrators. With its SQL-like syntax, it’s easy to learn and use for querying Salesforce data. By understanding the basic syntax and practising with simple queries, you can quickly become proficient in SOQL and leverage its power to retrieve and manipulate Salesforce data effectively.

For further information check out the Salesforce documentation