SOQL

SOQL (Salesforce Object Query Language) is used to query data

from Salesforce objects.

Here are examples of different types of SOQL queries:

1. Basic SOQL Query

This retrieves specific fields from a Salesforce object.

List<Account> accounts = [SELECT Id, Name FROM Account];

2. SOQL Query with WHERE Clause

Retrieves records based on a condition.

List<Contact> contacts = [SELECT Id, Name FROM Contact

WHERE LastName = 'Smith'];

3. SOQL Query with ORDER BY

Orders the results based on a field.

List<Opportunity> opportunities = [SELECT Id, Name FROM

Opportunity ORDER BY CloseDate DESC];

4. SOQL Query with LIMIT

Limits the number of records returned.

List<Account> top5Accounts = [SELECT Id, Name FROM

Account LIMIT 5];
5. SOQL Query with OFFSET

Skips a number of records before returning results.

List<Account> accountsOffset = [SELECT Id, Name FROM

Account LIMIT 10 OFFSET 5];

6. SOQL Query with Relationship Queries (Parent to Child)

Retrieves related child records (e.g., querying Contacts related to

Accounts).

List<Account> accountsWithContacts = [SELECT Id, Name,

(SELECT Id, FirstName, LastName FROM Contacts) FROM
Account];

7. SOQL Query with Relationship Queries (Child to Parent)

Retrieves fields from parent objects (e.g., Account fields from

Contacts).

List<Contact> contactsWithAccount = [SELECT Id, FirstName,

LastName, Account.Name FROM Contact];

8. SOQL Query with Aggregate Functions

Retrieves aggregate data like count, sum, min, max, etc.

AggregateResult [ ] totalOpportunities = [SELECT COUNT(Id)

FROM Opportunity WHERE StageName = 'Closed Won'];
9. SOQL Query with GROUP BY

Groups results based on a field and applies aggregate functions.

AggregateResult [ ] groupedByStage = [SELECT StageName,

COUNT(Id) FROM Opportunity GROUP BY StageName];

10. Dynamic SOQL Query

The query is constructed at runtime as a string.

String soql = 'SELECT Id, Name FROM Account WHERE Name

LIKE \'%Allen%\'';
List<Account> accounts = Database.query(soql);

11. FOR UPDATE

Locks the records to prevent others from modifying them until

your transaction completes.

List<Account> lockedAccounts = [SELECT Id, Name FROM

Account WHERE Name = 'Allen' FOR UPDATE];

12. Query with IN Operator

This retrieves records where a field matches any value in a list.

List<Contact> contacts = [SELECT Id, FirstName, LastName

FROM Contact WHERE Id IN :contactIds];
13. SOQL Query with Date Functions

You can use date functions to filter records.

List<Opportunity> opportunities = [SELECT Id, Name FROM

Opportunity WHERE CloseDate = LAST_N_DAYS:30];

14. Semi-Join

This retrieves records where a related record exists.

List<Account> accountsWithContacts = [SELECT Id, Name

FROM Account WHERE Id IN (SELECT AccountId FROM
Contact)];

15. Anti-Join

Retrieves records where no related records exist.

List<Account> accountsWithoutContacts = [SELECT Id, Name

FROM Account WHERE Id NOT IN (SELECT AccountId FROM
Contact)];

16. Query with LIKE Operator

Use the LIKE operator to search for patterns in text fields.

List<Account> accounts = [SELECT Id, Name FROM Account

WHERE Name LIKE 'Acme%'];
17. SOQL with Subquery (Correlated Query)

This retrieves records based on the result of a subquery.

List<Account> accountsWithOpportunities = [SELECT Id, Name

FROM Account WHERE Id IN (SELECT AccountId FROM
Opportunity WHERE StageName = 'Closed Won')];

18. Query on Big Objects

Big Objects can store billions of records and can be queried

using SOQL.

List<YourBigObject__b> bigObjectRecords = [SELECT Field__c

FROM YourBigObject__b WHERE Field__c = 'value'];

19. SOQL Query with Polymorphic Relationships

Polymorphic relationships like WhoId or WhatId can refer to

multiple object types (e.g., in Task or Event).

List<Task> tasks = [SELECT Id, WhoId, Who.Name FROM

Task];

20. Querying with ALL ROWS to Include Deleted Records

This retrieves all records, including those in the Recycle Bin (soft
deleted).

List<Account> allAccounts = [SELECT Id, Name FROM Account

WHERE IsDeleted = true ALL ROWS];
21. Query with OFFSET and ORDER BY

This paginates large results by ordering and using OFFSET.

List<Contact> contacts = [SELECT Id, FirstName, LastName

FROM Contact ORDER BY LastName ASC LIMIT 50 OFFSET
100];

22. Query using Custom Metadata

Queries can retrieve data from custom metadata types as well.

List<MyCustomMetadata__mdt> metadataRecords = [SELECT

DeveloperName, Field__c FROM MyCustomMetadata__mdt];

23. Query with Rollup Summary Fields

You can query roll-up summary fields like any other fields.

List<Account> accounts = [SELECT Id, Name,

Total_Opportunities__c FROM Account];

These queries provide additional flexibility when interacting with

Salesforce data using Apex SOQL, and they are useful for
different scenarios like dealing with relationships, deleted
records, or large data sets.
Here are some advanced SOQL queries, including polymorphic
relationships, which often involve polymorphic fields like WhoId
and WhatId (e.g., in Tasks and Events).

24. Polymorphic Query: WhoId (Lead or Contact)

The WhoId field can refer to either a Lead or Contact in Task or

Event objects.

List<Task> tasks = [SELECT Id, Subject, WhoId, Who.Type,

Who.Name FROM Task WHERE WhoId != null];

In this query, Who.Type can dynamically refer to the type of the

related object (either Lead or Contact).

25. Polymorphic Query: WhatId (Any Standard or Custom

Object)

The WhatId field can refer to a variety of standard or custom

objects.

List<Task> tasks = [SELECT Id, Subject, WhoId, Who.Type,

Who.Name FROM Task WHERE WhoId != null];

This retrieves events where WhatId can refer to any related

object, such as Account, Opportunity, etc.
26. Query Polymorphic Fields with TYPEOF

The TYPEOF SOQL clause allows you to retrieve fields based on

the specific type in a polymorphic relationship.

List<Task> tasks = [SELECT Id, Subject, TYPEOF Who WHEN

Contact THEN FirstName, LastName WHEN Lead THEN
Company, Email END FROM Task WHERE WhoId != null];

This query pulls different fields based on whether the WhoId is a

Contact or a Lead.

27. Using TYPEOF with More Complex Object Types

You can use TYPEOF for polymorphic lookups involving custom

objects.

List<Event> events = [SELECT Id, Subject, TYPEOF What

WHEN Opportunity THEN Amount WHEN Case THEN Status
END FROM Event WHERE WhatId != null];

28. Querying Junction Objects

In the context of many-to-many relationships, you may want to

query junction objects.

List<CampaignMember> members = [SELECT Campaign.Name,

Contact.FirstName, Contact.LastName FROM
CampaignMember WHERE Campaign.IsActive = true];
This query retrieves active campaign members by joining the
Campaign and Contact objects via the CampaignMember
junction object.

29. Querying External Objects

When using Salesforce Connect with external data sources, you

can query external objects.

List<ExternalObject__x> externalRecords = [SELECT

ExternalId__c, Name__c FROM ExternalObject__x WHERE
ExternalId__c = '12345'];

30. Querying Multiple Objects with WITH

SECURITY_ENFORCED
Ensures the query respects the user's field-level security and
object permissions.

List<Contact> contacts = [SELECT Id, FirstName, LastName

FROM Contact WITH SECURITY_ENFORCED];

31. SOQL Query with Multiple Conditions and Operators

Combine multiple conditions using logical operators AND, OR,
NOT.

List<Account> accounts = [SELECT Id, Name FROM Account

WHERE (Industry = 'Technology' OR Industry = 'Finance') AND
AnnualRevenue > 1000000];
32.Querying Salesforce Shield Encrypted Fields

If using Salesforce Shield, you can query encrypted fields,

though the data may be masked.

List<Contact> contacts = [SELECT Id, LastName, SSN__c FROM

Contact WHERE SSN__c = 'XXX-XX-1234'];

33. Query Using DISTINCT

Retrieves unique values for a particular field.

List<String> uniqueIndustries = [SELECT DISTINCT Industry

FROM Account WHERE Industry != null];

34. Querying Large Data Sets with QueryMore

When dealing with large data sets, you can use QueryLocator
and QueryMore in Apex to retrieve more than 50,000 records.

Database.QueryLocator ql =
Database.getQueryLocator([SELECT Id, Name FROM Account]);

35. Querying for Soft-Deleted Records in Recycle Bin

Retrieve soft-deleted records using ALL ROWS.

List<Account> deletedAccounts = [SELECT Id, Name FROM

Account WHERE IsDeleted = true ALL ROWS];
36. Querying Approval Processes

SOQL can query approval process history via ProcessInstance.

List<ProcessInstance> approvalHistory = [SELECT Id, Status,

TargetObject.Name FROM ProcessInstance WHERE
TargetObjectId = :recordId];

These advanced SOQL queries help when dealing with more

complex Salesforce scenarios such as polymorphic fields,
security-enforced queries, and managing large data sets.