The Ultimate Guide To Salesforce SOQL Queries & Limits

Understanding Salesforce Object Query Language (SOQL)

Salesforce Object Query Language (SOQL) is not the same as SQL. Pronounced as “sock-el” or “soak-el,” SOQL is the query language used to interact with Salesforce databases. While developers frequently use it when writing Apex code, SOQL’s capabilities extend much further, enabling both developers and admins to retrieve data from the organization’s database and generate comprehensive reports by exporting query results.

Though most developers are familiar with basic querying (and most admins can follow the simple syntax), SOQL offers much more than just retrieving records from the database. In this discussion, we’ll explore the structure of a SOQL query and how to enhance its functionality.

Structure of a SOQL Query

The basic syntax of a query might seem simple with a SELECT statement and a few fields, but the syntax includes more detailed elements than what might be evident at first glance. Queries are composed of different segments called clauses, which specify what data should be retrieved and how it should be formatted.

At the very least, every query includes a SELECT clause, which defines the fields to be returned. This is followed by the FROM keyword, which indicates the object being queried.

The SELECT clause can be enhanced with several optional clauses to modify the results significantly:

• WHERE clause: Filters records based on specified conditions, using logical operators like AND or OR.

  

• TYPEOF clause: Selects fields when querying polymorphic lookups, such as tasks related to specific objects.
  
  
  
• WITH clause: Sets additional query behaviors, such as enforcing field-level security with WITH SECURITY_ENFORCED or specifying a data category when querying knowledge articles or questions.
  
  
• ORDER BY clause: Sorts the query results in a defined order.
  
  
• GROUP BY clause: Transforms a query into an aggregate query, summarizing data across the dataset rather than returning individual records. This enables the use of the HAVING clause, which we will discuss further.

  

Relational Queries

A key feature of SOQL is its ability to navigate relationships within queries. This can range from retrieving a parent account’s name to fetching contacts associated with an account. These relational queries fall into two categories:

• Child to Parent: Queries move up the relationship hierarchy, referencing parent object fields when querying a child object. They can traverse up to five levels, referencing fields from grandparents, great-grandparents, and so on. These relationships are accessed using dot notation and end with __r for custom relationships.
  
  
• Parent to Child: Queries move down the relationship hierarchy, referencing records and fields of an object that is a child of the queried record. These queries can only go down one level. For example, you could query an account’s contacts but not records related to those contacts. These are written using subqueries within the SELECT clause, with the FROM clause specifying the relationship name (e.g., Contacts or My_Custom_Objects__r).

  

Aggregate Queries

Aggregate queries are highly effective for condensing large and complex datasets into a few rows, providing exactly the required data with minimal effort. These queries are ideal when focusing on the dataset as a whole rather than on individual records. Aggregate queries use the GROUP BY clause along with aggregate functions in the SELECT clause.

Available aggregate functions include:

• COUNT() / COUNT(fieldName): Counts the number of rows returned. Often used with the Id field or other lookup fields.
  
  
• COUNT_DISTINCT(fieldName): Counts unique, non-null values in the query.
  
  
• AVG(fieldName):  Calculates the average value of a field based on the returned rows.

  

• MIN(fieldName): Finds the minimum value of a field in the query, even applicable to picklists (sorted by picklist order).
  
  
• MAX(fieldName):  Finds the maximum value of a field, also applicable to picklists.
  
  
• SUM(fieldName): Adds up numerical values of a field based on the query results.In addition to the standard GROUP BY clause, there are two variations that enhance the query’s capabilities by also returning subtotals:
  
  
• GROUP BY ROLLUP(fieldName, fieldName2, …): This clause, used with multiple field aggregates, provides subtotals for each category and a final grand total, calculated left to right in the provided fields.
  
  
• GROUP BY CUBE(fieldName, fieldName2, …): This provides aggregates for all combinations of grouped fields, offering more information than GROUP BY ROLLUP and is useful for cross-tabular reports.

  

These queries can be further refined using the HAVING clause. For instance, to find all contacts tagged with ‘United Kingdom,’ ‘Salesforce Developer,’ and ‘Available,’ a query can be written to filter the results precisely.

Limits and Limitations

Like many Salesforce features, SOQL has certain limitations, which can vary depending on the query’s context. Some universal limits include:

• A maximum query length of 100,000 characters, including all clauses.
• A 4,000-character limit for individual strings in the WHERE clause.
• No more than 55 child-to-parent relationships.
• A maximum of 5 chained parent-to-child relationships (e.g., “Contact.Account.Owner.Name” is valid, but a deeper chain such as “Contact.Account.Parent.Custom_Lookup__r.Owner.Manager.Name” is not).
• A maximum of 20 parent-to-child relationships.
• Parent-to-child relationships are only allowed for the object in the FROM clause.
• A 120-second timeout for queries, with up to 30 minutes allowed for processing results.

When using SOQL within Apex, additional restrictions apply:

• Maximum of 100 queries in a synchronous transaction.
• Maximum of 200 queries in an asynchronous transaction.
• Maximum of 50,000 rows returned per transaction. Note that aggregate queries count each row as one, regardless of aggregation.
  
  

Summary

SOQL queries are invaluable for both developers writing code and admins seeking complex data insights within a Salesforce org. Understanding the components of a query and the various clauses that extend its functionality can simplify even complex data manipulations. Leveraging advanced SOQL features can unlock deeper insights into your data and enable swift customizations in code. Hopefully, this overview of SOQL’s features and structure will aid you in crafting your next query or generating impressive reports.