SOQL How-To Guide

Discover how to use Salesforce Object Query Language (SOQL) to access, filter, and analyse your Salesforce data more effectively. This practical guide covers query fundamentals, relationships, date filters, and real-world examples to help administrators and consultants work smarter.

This newsletter article was inspired by a presentation made by Michelle Hanson. You can read more about Michelle at the bottom of this email/post.

Why Use SOQL?

SOQL (Salesforce Object Query Language) is worth learning because it lets you do things list views and reports can't always do:

1. Query objects that aren't available in list views or don't have a tab — useful for junction objects, share objects, and other "behind the scenes" data.

2. Query data from related objects together — pull parent and child (or related) records in a single query.

3. No extra metadata required — unlike a report, you don't need to save anything in the org to run a SOQL query.

4. Faster once you're comfortable — writing a quick query can often beat building and running a report.

Tools to Help You Write Queries

You don't need to write SOQL from scratch every time — several tools can help:

• Developer Console (built into Salesforce)

• Data Loader / Dataloader.io

• Workbench

• Salesforce Inspector Reloaded (browser extension)

• Visual Studio Code – SOQL Builder

• Code Builder

• ...and more!

Anatomy of a SOQL Query

A basic SOQL query is built from up to five clauses, in this order:

• SELECT Id, Name, Amount
• FROM Opportunity
• WHERE StageName = 'Closed Won'
• ORDER BY Amount DESC
• LIMIT 5

Example result for the query above (Opportunities, Closed Won, sorted by Amount descending, top 5):

How SOQL Compares to a List View or a Report

It helps to see that a List View and a Report are really just doing the same SELECT / FROM / WHERE / ORDER BY / LIMIT logic behind a UI:

List View mapping:

1. SELECT → the columns you've added to the list view (e.g. Opportunity Name, Amount)

2. FROM → the object the list view is built on (e.g. Opportunities)

3. WHERE → the filters applied (e.g. Closed equals True, Won equals True)

4. ORDER BY → the column sort you click on (e.g. Amount)

5. LIMIT → list views don't have a true row limit; they just paginate

Report mapping:

1. SELECT → the columns/fields shown in the report

2. FROM → the report type (e.g. Opportunities)

3. WHERE → the filters (Show Me, Close Date, Opportunity Status, Stage, etc.)

4. ORDER BY → the column sort

5. LIMIT → the Row Limit setting on the report

Why this matters: Once you can see a list view or report as a SOQL query in disguise, it becomes much easier to translate what you want into actual SOQL — and to go beyond what the UI filters allow.

Querying Related Records

This is where SOQL really outperforms list views and reports — pulling data across object relationships in a single query. There are two directions to think about: parent → child and child → parent.

a) Querying Child Record Fields From a Parent Record (Subquery)

Use a SOQL query inside the SELECT statement of the parent query. This works the same way whether the relationship is standard or custom — only the child relationship name changes.

Standard Relationship (Opportunity → OpportunityLineItems):

SELECT Id, Name, Amount, (SELECT Name, TotalPrice FROM OpportunityLineItems)

FROM Opportunity

WHERE StageName = 'Closed Won'

Custom Relationship (Contact → Help_Topics, a custom child relationship):

SELECT Email, FirstName, LastName, (SELECT Name, Status__c FROM Help_Topics__r)

FROM Contact

Example result (custom relationship):

Note that a Contact can have multiple child records returned in the nested subquery result (e.g. multiple Help Topics per Contact).

b) Querying Parent Record Fields From a Child Record (Dot Notation)

Dot notation lets you traverse a relationship from child to parent, pulling parent fields directly into your main SELECT — no subquery needed.

• Standard Relationship: ParentObject.Field

• Custom Relationship: ParentObject__r.Field

Standard Relationship example (Contact → Account):

SELECT FirstName, LastName, Email, Account.Name, Account.BillingState

FROM Contact

Custom Relationship example (Help__c → SME, a custom lookup to Contact):

SELECT Name, Status__c, SME__r.FirstName, SME__r.LastName, SME__r.Email

FROM Help__c

Key distinction to remember:

Subquery = parent query pulling in a set of child records (one-to-many). 

• We can only traverse 1 level down from parent to child (Parent → Child, but not Parent → Child → Grandchild).

Dot notation = child query pulling in one parent's fields (many-to-one). 

• Dot Notation Depth Limit: We can traverse up to 5 levels up using dot notation (e.g., Contact.Account.Owner.Manager.Name).

c) Finding the Child Relationship Name in Object Manager

To write a subquery, you need to know the exact Child Relationship Name — not just the field or object name. Here's how to find it:

1. Open Object Manager
2. Navigate to the (parent) object
3. Open the lookup field on the child object that points back to this parent
4. Locate the "Child Relationship Name" value on that field's detail page

Example: a custom lookup field called "SME" on the Help object, related to Contact, has a Child Relationship Name of Help_Topics. This is what you'd use (with __r appended for custom objects) in a subquery from Contact.

Handy Date & Time References

Dates and datetimes in SOQL trip people up — here's a quick cheat sheet.

Date Literals

Instead of typing exact dates, SOQL has built-in literals you can drop straight into a WHERE clause:

DateTime Format

DateTime values follow ISO 8601 format:

YYYY-MM-DDThh:mm:ssZ

Example — 11/5/24 1:30pm (GMT) becomes:

2024-11-05T13:30:00Z

Less Than / Greater Than a Date

• Less Than (<) — anything that happened before that date

• Greater Than (>) — anything that happened after that date

Real-World Query Examples

These are practical, everyday queries you're likely to reuse.

a) Records Created on X Date by X User

Use AND to combine more than one condition in the WHERE clause — e.g. filtering by both a date range and the user who created the record.

SELECT Id, Name, CreatedDate

FROM Help__c

WHERE CreatedDate = LAST_N_DAYS:14

AND CreatedBy.Username = 'michelle.e.hansen@gmail.com'

b) Orphaned Contacts

To find Contacts with no Account, use a  WHERE clause and check for Null. 

SELECT Id, FirstName, LastName, Email

FROM Contact

WHERE AccountId = Null

c) Opportunities Without Products

Use a subquery in the WHERE clause to exclude Opportunities that already have OpportunityLineItems.

SELECT Name, Id, Amount

FROM Opportunity

WHERE Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem)

Key technique: subqueries in a WHERE clause let you filter one object based on the existence (or non-existence) of related records on another object — different from the SELECT-clause subquery in Section 5, which returns the related records rather than just filtering on them.

Opportunities With More Than One SLA Product

This example uses GROUP BY and HAVING to find Opportunities that have more than one product whose name contains "SLA."

SELECT COUNT(Id), Opportunity.Name

FROM OpportunityLineItem

WHERE Name LIKE '%SLA%'

GROUP BY Opportunity.Name

HAVING COUNT(Id) > 1

How it works:

• WHERE Name LIKE '%SLA%' filters line items to only those containing "SLA" in the name.
• GROUP BY Opportunity.Name groups the line items by their parent Opportunity.
• HAVING COUNT(Id) > 1 only returns groups (Opportunities) with more than one matching line item.

This is a great pattern any time you need to flag "duplicate child records" — i.e., more than one child record per parent.

More Real-World Examples

a) Who Has Access to a Record

Query the relevant Share object for a given object to determine who has access to a record, and how (Owner, Rule, Manual, etc.).

Custom Object Example (Help__c):

SELECT AccessLevel, ParentId, Parent.Name, RowCause, UserOrGroupId, UserOrGroup.Name

FROM Help__Share

Standard Object Example (Account):

SELECT AccountAccessLevel, AccountId, Account.Name, ContactAccessLevel, RowCause, UserOrGroupId, UserOrGroup.Name

FROM AccountShare

Key fields to note:

• AccessLevel / AccountAccessLevel — the level of access (e.g. All, Read, Edit)

• RowCause — why the access was granted (e.g. Owner, Rule, Manual)

• UserOrGroupId / UserOrGroup.Name — who has the access

b) Freezing (Portal) Users — Query, Update, Data Load

Freezing a user's login is done via the UserLogin object's IsFrozen field. This is a great example of using SOQL to identify records, then feeding the results into a data load to update them in bulk.

Query all Users:

SELECT Id, IsFrozen FROM UserLogin

Query only Portal Users:

SELECT Id, IsFrozen, UserId

FROM UserLogin

WHERE UserId IN (SELECT Id FROM User WHERE ContactId != '')

Workflow:

1. Run the query to get a list of relevant UserLogin records (e.g. all Portal Users).

2. Export the Id and IsFrozen columns.

3. Change IsFrozen from FALSE to TRUE (or vice versa) in your spreadsheet.

4. Use Data Loader (or similar) to update the UserLogin records in bulk.

c) Duplicate Child Records

Use a subquery + HAVING clause to identify parent records with more than one related child record (see the SLA Product example in Section 8 above for the pattern).

Aggregate Functions & Data Summarisation

Aggregate functions allow you to perform calculations on sets of records to summarise data directly in SOQL—similar to summary formulas in Salesforce Reports or GROUP BY statements in SQL.

Core Aggregate Functions

Combining Aggregate Functions with GROUP BY

When using aggregate functions alongside non-aggregated fields, you must group the results using the GROUP BY clause.

Example: Total & Average Opportunity Amount by Stage

SELECT StageName, SUM(Amount), AVG(Amount), COUNT(Id)

FROM Opportunity

GROUP BY StageName

Filtering Aggregated Results with HAVING

While the WHERE clause filters individual records before they are aggregated, the HAVING clause filters summarized groups after they are calculated.

Example: Accounts with more than $1,000,000 in Closed-Won Deals

SELECT AccountId, SUM(Amount) TotalWon

FROM Opportunity

WHERE StageName = 'Closed Won'

GROUP BY AccountId

HAVING SUM(Amount) > 1000000

Quick Reference Summary

About the Presenter - Michelle Hansen

Michelle is a 20+ certified Salesforce professional who has been working on the platform for over a decade. Active in the Salesforce community, she was inducted into the MVP Hall of Fame in 2026 and serves as a member of the Midwest Dreamin’ planning team, a coach/coach lead for RAD Women Code and a frequent presenter at user groups and events. Michelle is passionate about helping the next generation of Trailblazers excel in their careers!

You can view and print our SOQL How-To Guide here.