50 Apex SOQL Queries Every Salesforce Developer Should Know

Understanding the Basics of SOQL

Apex SOQL is to Salesforce what SQL is to databases. It allows for querying records, including filtering and retrieving data from related objects. The syntax of Apex SOQL is similar to SQL, making it intuitive for those with database querying experience. However, Apex SOQL offers Salesforce-specific functionality, such as querying across relationships, uniquely catering to the nuances of Salesforce data architecture.

Data Retrieval Queries

Basic SELECT Statement: Retrieve all records from an object.

SELECT Name FROM Account;

WHERE Clause: Filter records based on specific criteria.

SELECT Name FROM Contact WHERE LastName = 'Smith';

LIMIT Clause: Limit the number of records returned.

SELECT Name FROM Lead LIMIT 10;

ORDER BY: Order the returned records by specified fields.

SELECT Name, CreatedDate FROM Opportunity ORDER BY CreatedDate DESC;

Aggregate Functions

COUNT(): Count the number of records.

SELECT COUNT() FROM Opportunity WHERE StageName = 'Closed Won';

SUM(): Calculate the sum of a numeric field.

SELECT SUM(Amount) FROM Opportunity WHERE StageName = 'Closed Won';

MAX()/MIN(): Find the maximum or minimum value of a field.

SELECT MAX(CloseDate) FROM Opportunity;

Relationship Queries

Parent-to-Child (Subquery): Retrieve related records.

SELECT Name, (SELECT LastName FROM Contacts) FROM Account;

Child-to-Parent: Access parent object fields from a child object.

SELECT Contact.Name, Account.Name FROM Opportunity WHERE Id = 'XXXXXXX';

Advanced SOQL Queries for Complex Data Handling

Subquery and Semi-Join Examples

IN Operator with Subquery: Use a subquery result in the main query.

SELECT Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Smith');

Using NOT IN for Exclusions: Exclude records using a subquery.

SELECT Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost');

Retrieve specific fields from a single object:

   SELECT Field1, Field2 FROM ObjectName

Retrieve records based on a condition:

   SELECT Id, Name FROM ObjectName WHERE Condition

Retrieve records using logical operators (AND, OR):

   SELECT Id, Name FROM ObjectName WHERE Condition1 AND Condition2

Retrieve records ordered by a field:

   SELECT Id, Name FROM ObjectName ORDER BY FieldName ASC/DESC

Retrieve the first N records:

   SELECT Id, Name FROM ObjectName LIMIT N

Retrieve records with a specific field value:

   SELECT Id, Name FROM ObjectName WHERE FieldName = 'Value'

Retrieve records with a range of values:

   SELECT Id, Name FROM ObjectName WHERE FieldName BETWEEN low AND high

Retrieve records with a null value:

   SELECT Id, Name FROM ObjectName WHERE FieldName = NULL

Retrieve records with non-null values:

SELECT Id, Name FROM ObjectName WHERE FieldName != NULL

Retrieve records based on a date range

SELECT Id, Name FROM ObjectName WHERE CreatedDate > TODAY - N_DAYS

Retrieve records related to another object:

SELECT Id, Name, RelatedObject.Field FROM ObjectName WHERE RelatedObject.Field = 'Value'

Retrieve records with parent-child relationships:

SELECT Id, Name, (SELECT Id, Name FROM ChildRelationshipName) FROM ParentObjectName

Retrieve records using aggregate functions (COUNT, SUM, AVG, MAX, MIN):

SELECT COUNT(Id) FROM ObjectName WHERE Condition

Retrieve distinct values:

SELECT DISTINCT FieldName FROM ObjectName

Retrieve records with dynamic filters:

SELECT Id, Name FROM ObjectName WHERE FieldName IN :listOfValues

Retrieve records using LIKE operator:

SELECT Id, Name FROM ObjectName WHERE FieldName LIKE 'Value%'

Retrieve records using WITH SECURITY_ENFORCED clause:

SELECT Id, Name FROM ObjectName WHERE Condition WITH SECURITY_ENFORCED

Retrieve records with field aliases:

SELECT FieldName AS Alias FROM ObjectName

Retrieve records based on related objects’ fields:

SELECT Id, Name, RelatedObject.FieldName FROM ObjectName WHERE RelatedObject.FieldName = 'Value'

Retrieve records using aggregate functions with GROUP BY clause:

SELECT Field1, COUNT(Id) FROM ObjectName GROUP BY Field1

Retrieve records using HAVING clause with aggregate functions:

SELECT Field1, COUNT(Id) FROM ObjectName GROUP BY Field1 HAVING COUNT(Id) > N

Retrieve records using FOR VIEW clause:

SELECT Id, Name FROM ObjectName FOR VIEW

Retrieve records using FOR REFERENCE clause:

SELECT Id, Name FROM ObjectName FOR REFERENCE

Retrieve records using FOR UPDATE clause:

SELECT Id, Name FROM ObjectName FOR UPDATE

Retrieve records using FOR UPDATE OF clause:

SELECT Id, Name FROM ObjectName FOR UPDATE OF FieldName

Retrieve records with fields having a specified data type:

SELECT Id, Name FROM ObjectName WHERE FieldName__c INCLUDES ('Value')

Retrieve records using CALENDAR_MONTH function:

SELECT Id, Name FROM ObjectName WHERE CALENDAR_MONTH(CreatedDate) = N

Retrieve records using CALENDAR_QUARTER function:

SELECT Id, Name FROM ObjectName WHERE CALENDAR_QUARTER(CreatedDate) = N

Retrieve records using CALENDAR_YEAR function:

SELECT Id, Name FROM ObjectName WHERE CALENDAR_YEAR(CreatedDate) = N

Retrieve records using DAY_ONLY function:

SELECT Id, Name FROM ObjectName WHERE DAY_ONLY(CreatedDate) = TODAY

Retrieve records using MONTH_ONLY function:

SELECT Id, Name FROM ObjectName WHERE MONTH_ONLY(CreatedDate) = N

Retrieve records using YEAR_ONLY function:

SELECT Id, Name FROM ObjectName WHERE YEAR_ONLY(CreatedDate) = N

Retrieve records using INCLUDES operator:

SELECT Id, Name FROM ObjectName WHERE FieldName INCLUDES ('Value1', 'Value2')

Retrieve records using EXCLUDES operator:

SELECT Id, Name FROM ObjectName WHERE FieldName EXCLUDES ('Value1', 'Value2')

Retrieve records using TYPEOF clause:

SELECT TYPEOF What WHEN ObjectName THEN Field1, Field2 END FROM Event

Retrieve records using GROUP BY ROLLUP:

SELECT Account.Name, Industry FROM Account GROUP BY ROLLUP(Industry)

Retrieve records using GROUP BY CUBE:

SELECT Account.Name, Industry FROM Account GROUP BY CUBE(Industry)

Retrieve records using GROUP BY GROUPING SETS:

SELECT Account.Name, Industry FROM Account GROUP BY GROUPING SETS((Industry), ())

Retrieve records using COUNT_DISTINCT function:

SELECT COUNT_DISTINCT(Field1) FROM ObjectName

Retrieve records using DAY_IN_MONTH function:

SELECT Id, Name FROM ObjectName WHERE DAY_IN_MONTH(CreatedDate) = N

Retrieve records using DAY_IN_WEEK function:

SELECT Id, Name FROM ObjectName WHERE DAY_IN_WEEK(CreatedDate) = N

Retrieve records using HOUR_IN_DAY function:

SELECT Id, Name FROM ObjectName WHERE HOUR_IN_DAY(CreatedDate) = N

Retrieve records using CALENDAR_MONTH function:

SELECT Id, Name FROM ObjectName WHERE CALENDAR_MONTH(CreatedDate) = N

Retrieve records using CALENDAR_WEEK function:

SELECT Id, Name FROM ObjectName WHERE CALENDAR_WEEK(CreatedDate) = N

Retrieve records using CALENDAR_YEAR function:

SELECT Id, Name FROM ObjectName WHERE CALENDAR_YEAR(CreatedDate) = N

Retrieve records using WEEK_IN_MONTH function:

SELECT Id, Name FROM ObjectName WHERE WEEK_IN_MONTH(CreatedDate) = N

Retrieve records using WEEK_IN_YEAR function:

SELECT Id, Name FROM ObjectName WHERE WEEK_IN_YEAR(CreatedDate) = N

Retrieve records using DISTANCE function:

SELECT Id, Name FROM ObjectName WHERE DISTANCE(LocationField, GEOLOCATION(latitude, longitude), 'mi') < N

Retrieve records using GEOLOCATION function:

SELECT Id, Name FROM ObjectName WHERE GEOLOCATION(latitude, longitude) != NULL

---