Salesforce: SOQL Reference

Using a query tool of your choice (e.g., the Developer Console Query Editor, Workbench), you can execute the SOQL (Salesforce Object Query Language) query to get raw data from Salesforce, where some of this is unavailable from the report.

Simple query
SELECT Id, Name FROM Account WHERE Name LIKE 'A%'
SELECT Id, Name FROM Account WHERE Name LIKE 'A%' AND BillingCity = 'Redwood City'
SELECT Id, Name FROM Account WHERE CurrencyIsoCode = 'USD' OR CurrencyIsoCode = 'SGD'

Query with LIKE and NOT
SELECT a.Id, a.Name FROM Account a WHERE NOT a.Name LIKE 'A%'
SELECT Id, Name FROM Account WHERE AND BillingCity = 'Redwood City' AND (NOT Name LIKE '%Vendor%')
** NOT should be put before the field name
** Notice usage of a variable

However, in some fields like the Id or polymorphic relationship field, we cannot use LIKE.

For Task and Event object, if you know the related object, you can filter with the object name

SELECT Id, WhatId, What.Type, What.Name  FROM Task WHERE What.Type IN ('Account','Opportunity') LIMIT 100

Query filter on DateTime
SELECT Name FROM Account WHERE CreatedDate >= 2011-04-26T10:00:00-08:00 this is GMT-8
SELECT Name, CreatedDate FROM Account WHERE CreatedDate > 2011-04-26T10:00:00Z this is GMT

Query with Date function
SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

SELECT Amount FROM Opportunity WHERE CreatedDate = THIS_YEAR

Query filter on Null value
SELECT Id, AccountId FROM Event WHERE ActivityDate != Null

same result using <> SELECT Id, AccountId FROM Event WHERE ActivityDate <> Null

SELECT Id, AccountId FROM Event WHERE ActivityDate = Null

SELECT Id, AccountId FROM Event WHERE AccountId = Null

same result using '' SELECT Id, AccountId FROM Event WHERE AccountId = ''

but you can't use '' for Date field

Query Multi-Select Picklists
SELECT Id, Country__c from Account WHERE Country__c INCLUDES ('Indonesia;Singapore','Malaysia')

Semi-Join Query
SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost' )

Reference Field Semi-Join Query
SELECT Id FROM Task WHERE WhoId IN ( SELECT Id FROM Contact WHERE MailingCity = 'Twin Falls' )
To query attachment from an object only
SELECT Id, Name, ParentId FROM Attachment WHERE ParentId IN ( SELECT Id FROM Account )

Anti-Join Query
SELECT Id FROM Account WHERE Id NOT IN ( SELECT AccountId FROM Opportunity WHERE IsClosed = False )

Reference Field Anti-Join Query
SELECT Id FROM Opportunity WHERE AccountId NOT IN ( SELECT AccountId FROM Contact WHERE LeadSource = 'Web' )

Multiple Semi-Joins Query
SELECT Id, Name FROM Account WHERE 
Id IN ( SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%' ) AND 
Id IN ( SELECT AccountId FROM Opportunity WHERE isClosed = False )

Relationship Query: parent to child
SELECT Id, (SELECT Id,Name from OpportunityLineItems) FROM Opportunity
SELECT Id, (SELECT Id,Name from Account_Requests__r) FROM Account
Notice for the child object name in yellow highlight, it is plural. You can get the name from the lookup field in the child object "Child Relationship Name" and add __r for custom field.

Relationship Query: child to parent 
SELECT Id, Name, Account.Name FROM Contact

Relationship Query: Polymorphic 
A polymorphic relationship field in an object being queried that can reference multiple object types. For example, the What relationship field of an Event or Task could be an Account, or a Campaign, or an Opportunity. Check references at the bottom of this blog.
SELECT Id FROM Task WHERE What.TYPE IN ('Account', 'Opportunity')

SELECT Id, WhatId, 

  TYPEOF What WHEN Account THEN BillingState, BillingCity END 

FROM Task WHERE WhatId IN (SELECT Id FROM Account)

SELECT 

  TYPEOF What

    WHEN Account THEN Phone, NumberOfEmployees

    WHEN Opportunity THEN Amount, CloseDate

    ELSE Name, Email

  END

FROM Event

Select ALL

In standard SQL, we can use SELECT * from table_name, but this is not available in SOQL

In API 51.0, Salesforce introduced 

  FIELDS(ALL)—to select all the fields of an object.

  FIELDS(CUSTOM)—to select all the custom fields of an object.

  FIELDS(STANDARD)—to select all the standard fields of an object

Sample: SELECT FIELDS(ALL) FROM Account LIMIT 200

Reference FIELDS()

Using OFFSET
Use OFFSET to specify the starting row offset into the result set returned by your query.
SELECT Id, Name FROM Opportunity ORDER BY Name OFFSET 5

Using COUNT
Returns the number of rows matching the query criteria
SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'

If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT()

Using COUNT_DISTINCT
Returns the number of distinct non-null field values matching the query criteria
SELECT COUNT_DISTINCT(Company) FROM Lead

This will return the number of distinct values from the Company field

Using GROUP BY
From API version 18.0 and later, you can use GROUP BY with aggregate functions, such as COUNT(), SUM(), MAX(), MIN(), AVG()
SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY Stagename

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

SELECT StageName, SUM(amount), MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName

SELECT CALENDAR_YEAR(CloseDate) Year, CALENDAR_MONTH(CloseDate) Month, COUNT(Id) TotalCount FROM Opportunity WHERE IsClosed = False GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate) ORDER BY CALENDAR_YEAR(CloseDate) ASC

Note: you cannot use a formula field for grouping.

Using Alias with GROUP BY
You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you’re processing the query results in your code.
SELECT StageName, SUM(amount) Jumlah, MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName ORDER BY SUM(amount) Desc

Using GROUP BY ROLLUP
Same with GROUP BY, with additional ROLLUP, it adds subtotal for aggregated data in the last row
SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY ROLLUP(StageName)

Using GROUP BY ROLLUP with 2 fields
SELECT Status, LeadSource, COUNT(Id) FROM Lead GROUP BY ROLLUP(Status, LeadSource)

*Yellow highlight = total subgroup

*Green highlight = total all  

HAVING in GROUP BY
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, same with WHERE with normal query
SELECT LeadSource, COUNT(Id) FROM Lead GROUP BY LeadSource HAVING COUNT(Id) > 2

Querying Currency Fields in Multi-currency Organizations
SELECT Id, Name, Amount, CurrencyIsoCode FROM Opportunity WHERE Amount > SGD5000 ORDER BY Amount
** without currency code it will use the organization's default currency
** Amount in query result will be in record currency



Reference: 

• Salesforce Object Query Language (SOQL)
• TYPEOF
• Filtering on Polymorphic Relationship Fields
• Salesforce SOQL query LAST_WEEK ; LAST_N_DAYS:n ; N_DAYS_AGO:n

Last update: 10 Jun 2021