Essential SOQL Queries for Salesforce Developers

In the Salesforce ecosystem, developers frequently need to interact with data, troubleshoot issues, and optimize application performance. Salesforce Object Query Language (SOQL) is a powerful tool that allows developers to query data stored in Salesforce objects. Below, we highlight some useful SOQL queries that can be incorporated into daily workflows to enhance productivity and maintain system integrity.

1.Finding Records Without Related Data using Child Query

A common task in ensuring data integrity is identifying records that are missing related data.

Query: Retrieve accounts with no associated contacts.

SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Contact)

The subquery (SELECT AccountId FROM Contact) retrieves the AccountId. The NOT IN condition ensures that only accounts whose Id is not in this list are included in the results. In other words, it filters out accounts that have related contacts and returns only the accounts that do not have any associated contacts.

2. Tracking Field Changes by LastModifiedDate

Monitoring changes to data is critical for auditing and keeping data up-to-date.

Query: Retrieve accounts that were modified in the last 7 days, along with modification details.

SELECT Id, Name, LastModifiedBy.Name, LastModifiedDate 
FROM Account 
WHERE LastModifiedDate = LAST_N_DAYS:7

In SOQL, LAST_N_DAYS is a dynamic date literal that allows you to filter records based on the past N days.

Available Date Literals: YESTERDAY, TODAY, TOMORROW, LAST_WEEK, NEXT_WEEK, LAST_MONTH, THIS_MONTH, LAST_90_DAYS, N_DAYS_AGO:n

For More Date Literals Refer Salesforce Date Formats and Date Literals in WHERE Clause Documentation.

3. Fetching Duplicate Records using Aggregate Query

Duplicate records can lead to confusion and data redundancy, so identifying them is crucial. The below query finds and lists all account names that are duplicated in the Salesforce system.For each duplicate account name, it will show the name and how many accounts share that name.

Here’s a sample dataset for the Account object with multiple records but with duplicate names:

Query: Identify accounts with duplicate names.

SELECT Name, COUNT(Id) 
FROM Account 
GROUP BY Name 
HAVING COUNT(Id) > 1

SELECT Name This part tells Salesforce to retrieve the Name field from the Account object, which refers to the account name.

COUNT(Id) This counts the number of records (or accounts) that share the same Name. The Id is used to count the number of instances of each account name.

GROUP BY Name This clause groups the result set by the Name field. Essentially, it will group all accounts that have the same name together and aggregate them, allowing you to count how many accounts share that name.

HAVING COUNT(Id) > 1 The HAVING clause filters the results after the GROUP BY operation. In this case, it filters out any account names that appear only once.

COUNT(Id) > 1 ensures that only those account names that appear more than once (i.e., duplicates) are returned in the result set.

Result after the query:

The query skipped Beta Industries and returned the duplicate records with it’s count.

4. Retrieve Apex Classes and VF Pages Using SetupEntityAccess

The SetupEntityAccess object is a child of the PermissionSet object, functioning similarly to other child objects of PermissionSet, such as FieldPermissions and ObjectPermissions. To filter Apex classes by permission set, include Parent.Name in the WHERE clause.

Query: Retrieve Apex classes by permission set name.

SELECT Id, SetupEntity.Name, Parent.Name, Parent.NamespacePrefix, SetupEntityType 
FROM SetupEntityAccess 
WHERE SetupEntityType='ApexClass' AND Parent.Name = 'Permission_Set_Name'

Supported SetupEntityType:

• ApexClass for Apex classes
• ApexPage for Visualforce pages
• FlowDefinition for flows
• CustomEntityDefinition  for Custom Settings and Custom Metadata Types
• TabSet for apps
• CustomPermission for custom permissions

Refer Salesforce Documentation for supported API Versions.

5. Query to Retrieve Field Names Using EntityParticle

To retrieve field names using EntityParticle, you can run a query on the EntityParticle object within Salesforce’s Tooling API. Here’s how you can construct a query to get all fields for a specific object (for example, the Account object):

SELECT QualifiedApiName, DataType, Label
FROM EntityParticle
WHERE EntityDefinition.QualifiedApiName = 'Account'

QualifiedApiName – The API name of the field (e.g., Name, Industry, BillingAddress).

DataType – The type of the field (e.g., string, picklist, date, reference).

Label – The user-friendly label of the field displayed in the UI.

EntityDefinition.QualifiedApiName = 'Account' – Filters results to show fields only for the Account object.

6. Fetching Users Assigned to Permission Sets using PermissionSetAssignment

This SOQL query retrieves details about users and the permission sets assigned to them using the PermissionSetAssignment object.

SELECT PermissionSet.Name, Assignee.Name
FROM PermissionSetAssignment

Assignee.Name is simply the user’s name (who is assigned to the permission set).

To retrieve the ID of permission sets or users, use the PermissionSetId or AssigneeId fields in the query.

SELECT PermissionSet.Name, PermissionSetId, Assignee.Name, AssigneeId 
FROM PermissionSetAssignment

Using PermissionSetId and AssigneeId, we can export the data to Excel and bulk assign permission sets.

7. Return the total sum of Opportunity Amounts for each Account.

If we want to query the total amount of opportunities from each account, we can use the below query:

SELECT AccountId, SUM(Amount) FROM Opportunity GROUP BY AccountId

What the Query Does:

• Groups Opportunities by Account: The query groups all Opportunity records by their AccountId. This means that all Opportunities associated with the same Account will be treated as a single group.
• Calculates the Total Amount for Each Account: For each group (i.e., each AccountId), the query calculates the total sum of the Amount field. The Amount field typically represents the value of the Opportunity, so this will give the total value of all Opportunities for each Account.

For Example:

Consider these opportunity records. Few of them associated to same accounts.

After running the query, it would return the following results:

GROUP BY is essential when using aggregate functions like SUM(). Without it, we can’t calculate the sum for different groups of records.