SOQL Query Examples
SOQL Query - Basic Query
This example shows how to write simple SOQL query (Salesforce Object Query Language). It uses WHERE clause, ORDER BY and LIMIT clause. Using limit clause it will return only 10 records at maximum.
SOQL is similar to database SQL query language but much simpler and many features you use in database query may not be supported in SOQL (Such as JOIN clause not supported)
For more information about SOQL query syntax check below links :
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm
SELECT Id, LastName, FirstName
FROM Contact
Where LastName!='Smith'
ORDER BY LastName, FirstName
LIMIT 10
SOQL Query - Parent fields (Child-to-Parent / Many-to-One)
This example shows how to get field value from related parent object using dot operator.
For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm
SELECT Id,FirstName, LastName, Account.Name FROM Contact
SOQL Query - Child records (Parent-to-Child / One-to-Many)
This example shows how to get child records for selected parent records (e.g. All contacts for specified accounts).
For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm
SELECT Account.Id,Account.Name
, (SELECT Contact.FirstName, Contact.LastName, Contact.Email FROM Account.Contacts)
FROM Account
SOQL Query - Child Query with Order By and LIMIT
This example shows how to use child query and limit number or child records for each parent row.
SELECT Name,
(SELECT FirstName, LastName FROM Contacts ORDER BY CreatedDate LIMIT 5)
FROM Account
Where Name='ZappySys'
SOQL Query - Object and field alias
This example shows how to alias Table name or field name in SOQL queries. SOQL has very limited support for alias. You can alias Table name in any query but field alias is only allowed in Aggregation query only (Query with at least one aggregation function).
For more information about Alias in SOQL query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_alias.htm?search_text=Alias
For more information about Alias in Aggregation query check this link: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby_alias.htm?search_text=Alias
SELECT c.Id, c.LastName, a.Name
FROM Contact c,c.Account a
LIMIT 10
SOQL Query - GROUP BY / Aggregate function
This example shows how to use GROUP BY clause along with optional HAVING clause. When you use Aggregate function in SQL Query you can also use alias for field (This is the only time alias can be used). Child query cannot be used when Aggregate functions are used. Refer help file to learn more about other consideration.
For more information about GRPOUP BY check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby.htm
SELECT LeadSource, COUNT(Name) Cnt
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100
SOQL Query with DateTime / Use of variable
This example shows how to use variable placeholders anywhere in query to make it dynamic. You can use inbuilt Date Literals (e.g. TODAY, YESTERDAY, LAST_N_DAYS:365). By default when variable with DateTime type is used then its automatically converted to UTC datetime format before sending query to server. If you want to query by UTC Date only (without time portion from variable) then use TO_UTC_DATE rather than TO_UTC_DATETIME format specifier (e.g. SELECT Id, FirstName FROM Contact WHERE CreatedDate < {{System::ContainerStartTime,TO_UTC_DATE}} ). To convert UTC date variable to Local Date use TO_LOCAL_DATE or TO_LOCAL_DATETIME
For more information about query datetime field check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm?search_text=date
For more information about variable placeholder usage check this link : https://zappysys.com/onlinehelp/ssis-powerpack/scr/ssis-format-specifiers.htm
SELECT Id, LastName, FirstName, Email, Account.Name
FROM Contact
Where LastModifiedDate < {{System::ContainerStartTime,TO_UTC_DATETIME}} AND CreatedDate < YESTERDAY
LIMIT 10
SOQL Query - IN clause with Subquery
This example shows how to use sub query result for IN clause.
For more information check this link:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm
SELECT Id, Name
FROM Account
WHERE Id IN
(
SELECT AccountId
FROM Contact
WHERE LastName LIKE 'a%'
)
AND Id IN
(
SELECT AccountId
FROM Opportunity
WHERE isClosed = false
)
SOQL Query - Use of special characters (i.e Escape sequence)
This example shows how to use reserved special characters inside string literals using escape sequence. Anytime you have reserved character in string then use slash (\) in front of character e.g. Sam's Farm would be Sam\'s Farm.
For more information check below links:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_reservedcharacters.htm
SELECT Id FROM Account WHERE Name = 'Sam\'s Farm'
