The SOQL SELECT command uses the following syntax:
SELECT fieldList FROM objectType [WHERE conditionExpression] [ORDER BY] LIMIT ?
| Syntax | Description |
|---|---|
| fieldList subquery? | Specifies a list of one or more fields, separated by commas,
that you want to retrieve from the specified object. The bold elements in the following examples are fieldlists:
fieldList can include a subquery if the query
will traverse a relationship. For example: SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account The fieldlist can also be count() or be wrapped in toLabel(). |
| objectType | Specifies the type of object that you want to query(). You must specify a valid object and must have read-level permissions to that object. For a list of valid objects, see Standard Objects. |
| conditionExpression | If WHERE is specified, determines which rows and values in the specified object (objectType) to filter against. If unspecified, the query() retrieves all the rows in the object that are visible to the user. See WHERE for the appropriate syntax. |
The conditionExpression, the WHERE clause, uses the following syntax:
fieldExpression logicalOperator fieldExpression2 ?
You can use date or datetime values, or date literals. The format for date and dateTime fields are different.
fieldExpression1 AND (fieldExpression2 OR fieldExpression3)
(fieldExpression1 AND fieldExpression2)OR fieldExpression3
See fieldExpression for the syntax of fieldExpression. See Logical Operators for the valid logical operators.
fieldExpression uses the following syntax:
fieldName comparisonOperator value
where:
| Syntax | Description |
|---|---|
| fieldName | The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList. |
| comparisonOperator | See Comparison Operators for a list of valid operators. |
| value | A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. For date values, use the formatting listed in Date Formats and Date Literals. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error. |
The following table lists the comparisonOperator values that are used in fieldExpression syntax. Note that comparisons on strings are case-insensitive.
| Operator | Name | Description |
|---|---|---|
| = | Equals | Expression is true if the value in the specified fieldName equals the specified value in the expression. String comparisons using the equals operator are case-insensitive. |
| != | Not equals | Expression is true if the value in the specified fieldName does not equal the specified value. |
| < | Less than | Expression is true if the value in the specified fieldName is less than the specified value. |
| <= | Less or equal | Expression is true if the value in the specified fieldName is less than, or equals, the specified value. |
| > | Greater than | Expression is true if the value in the specified fieldName is greater than the specified value. |
| >= | Greater or equal | Expression is true if the value in the specified fieldName is greater than or equal to the specified value. |
| LIKE | Like | Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value. The LIKE operator in SOQL and SOSL
is similar to the SAME operator in SQL; it provides
a mechanism for matching partial text strings and includes support
for wildcards.
For example, the following query matches Appleton, Apple,
and Bappl , but not Appl: SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE 'appl_%' |
| IN | IN | If the value equals any one of the specified values in a WHERE clause. For example: SELECT Name FROM ACCOUNT WHERE BillingState IN ('California', 'New York') Note that the values for IN must be in parentheses. String values must be surrounded by single quotes. IN and NOT IN can also be used for semi-joins and anti-joins when searching on ID fields. For more information, see Semi-Joins with IN and Anti-Joins with NOT IN. |
| NOT IN | NOT IN | If the value does not equal any of the specified values in
a WHERE clause. For example: SELECT Name FROM ACCOUNT
WHERE BillingState NOT IN ('California', 'New York')Note that the values for NOT IN must be in parentheses, and string values must be surrounded by single quotes. There is also a logical operator NOT. |
| INCLUDES EXCLUDES | Applies only to multi-select picklists. See Querying Multi-Select Picklists. |
You can query values in a field where another field on the same object has a specified set of values, using IN. For example:
SELECT Name FROM ACCOUNT
WHERE BillingState IN ('California', 'New York')
In addition, you can create more complex semi-joins and anti-joins using IN and NOT IN to query ID fields.
You can include a semi-join in a WHERE clause. For example, the following query returns account IDs if an associated opportunity is lost:
SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')
Notice that the subquery returns a single field of the same type as the field to which it is compared. A full list of restrictions that prevent unnecessary processing is provided at the end of this section.
SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
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)You can use at most two subqueries in a single semi-join or anti-join query. Multiple semi-joins and anti-join queries are also subject to existing limits on subqueries per query. For more information, see the subquery limits described in Understanding Relationship Query Limitations.
SELECT Id, (SELECT Id from OpportunityLineItems) FROM Opportunity WHERE Id IN ( SELECT OpportunityId FROM OpportunityLineItem WHERE totalPrice > 10000 )
Because a great deal of processing work is required for semi-join and anti-join queries, salesforce.com imposes the following restrictions to maintain the best possible performance:
SELECT Id FROM Idea WHERE (Id IN (SELECT ParentId FROM Vote WHERE CreatedDate > LAST_WEEK))
SELECT Id, Name FROM Account WHERE Id in ( SELECT AccountId from Contact WHERE LastName LIKE 'Brown_%' )
Using Account.Id (dot notation) instead of AccountId is not supported. Similarly, subqueries like Contact.AccountId FROM Case are invalid.
SELECT Id, Name FROM Account WHERE Id In ( SELECT ParentId FROM Account WHERE Name = 'myaccount' )
SELECT Id, Name FROM Account WHERE Parent.Name = 'myaccount'
SELECT Id FROM Idea WHERE (Idea.Title LIKE 'Vacation%') AND (Idea.LastCommentDate > YESTERDAY) AND (Id IN (SELECT ParentId FROM Vote WHERE CreatedById = '005x0000000sMgYAAU'))
The following query is invalid since the nested query is an additional level deep:
SELECT Id FROM Idea WHERE ((Idea.Title LIKE 'Vacation%') AND (CreatedDate > YESTERDAY) AND (Id IN (SELECT ParentId FROM Vote WHERE CreatedById = '005x0000000sMgYAAU') ) OR (Idea.Title like 'ExcellentIdea%'))
The following table lists the logical operator values that are used in fieldExpression syntax:
| Operator | Syntax | Description |
|---|---|---|
| AND | fieldExpressionX AND fieldExpressionY | true if both fieldExpressionX and fieldExpressionY are true. |
| OR | fieldExpressionX OR fieldExpressionY | true if either fieldExpressionX or fieldExpressionY is true. Relationship queries with foreign key values in an OR clause behave differently depending on the version of the API. In a WHERE clause using OR, if the foreign key value in a record is null, the record is returned in Version 13.0 and later, but not returned in versions before 13.0. SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'The contact with no parent account has a last name that meets the criteria, so it is returned in version 13.0 and later. |
| NOT | not fieldExpressionX | true if fieldExpressionX is false. There is also a comparison operator NOT IN. |
Reserved characters, if specified in aSELECT clause as a literal string (between single quotes), must be escaped (preceded by the backslash \ character) in order to be properly interpreted. An error occurs if you do not precede reserved characters with a backslash.
For example, to query the AccountName field for "Bob's BBQ," use the following SELECT statement:
SELECT Id FROM Account WHERE Name LIKE 'Bob\'s BBQ'
In order to discover the number of rows that are returned by a query, use count() in a SELECT clause:
SELECT count() FROM objectType [WHERE conditionExpression][LIMIT number_of_rows]
For example:
SELECT count() FROM Account WHERE Name LIKE 'a%'
SELECT count() FROM Contact, Contact.Account WHERE Account.name = 'MyriadPubs'
count() must be alone in the SELECT statement, it cannot be mingled with other elements in the SELECT clause. count() cannot be used with ORDER BY, however, it can be used with LIMIT.
The query result Size field returns the number of rows. The records themselves are returned as null.
You can use ORDER BY in a SELECT statement:
SELECT fieldList FROM objectType [WHERE conditionExpression] [ORDER BY fieldName ASC | DESC ? NULLS FIRST | LAST ? ] [LIMIT number_of_rows]
| Syntax | Description |
|---|---|
| ASC or DESC | Specifies whether the results are ordered in ascending (ASC) or descending (DESC) order. Default order is ascending. |
| NULLS FIRST or NULLS LAST | Orders null records at the beginning (NULLS FIRST) or end (NULLS LAST) of the results. By default, null values are sorted first. |
For example, the following query returns a query result with Account records in alphabetical order by first name, sorted in descending order, with accounts that have null names appearing last:
SELECT Name FROM Account ORDER BY Name DESC NULLS LAST
The following factors affect results returned with ORDER BY:
SELECT Id, CaseNumber, Account.Id, Account.Name FROM Case ORDER BY Account.NameAny case record for which AccountId is empty is returned in Version 13.0 and later.
The following limitations apply to data types when using ORDER BY:
You can use ORDER BY with the optional LIMIT qualifier, in a SELECT statement:
SELECT Name FROM Account WHERE industry = 'media'
ORDER BY BillingPostalCode ASC NULLS last LIMIT 125
You are limited to 32 fields in an ORDER BY query. If you exceed the limit, a malformed query fault is returned.
Use LIMIT to specify the maximum number of rows to return:
SELECT fieldList FROM objectType [WHERE conditionExpression] LIMIT number_of_rows
For example:
SELECT Name FROM Account WHERE industry = 'media' LIMIT 125
This query returns the first 125 records whose industry is Media.
You can use LIMIT with count() as the fieldList to count up to the maximum specified.
| Type of Search | Example(s) |
|---|---|
| Simple query | SELECT Id, Name, BillingCity FROM Account |
| WHERE | SELECT Id FROM Contact WHERE Name like 'A%' AND MailingCity='California' |
| ORDER BY | SELECT Name FROM Account ORDER BY Name DESC NULLS LAST |
| LIMIT | SELECT Name FROM Account WHERE industry = 'media' LIMIT 125 |
| ORDER BY with LIMIT | SELECT Name FROM Account WHERE industry = 'media' ORDER BY BillingPostalCode ASC NULLS last LIMIT 125 |
| count() | SELECT count() FROM Contact |
| Relationship queries: child-to-parent | SELECT Contact.Firstname, Contact.Account.Name FROM
Contact
SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry = 'media' |
| Relationship queries: parent-to-child | SELECT Name, (SELECT LastName FROM Contacts) FROM Account
SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account |
| Relationship query with WHERE | SELECT Name, (SELECT lastname FROM Contacts WHERE CreatedBy.Alias = 'x') FROM Account WHERE industry = 'media' |
| Relationship query: child-to parent with custom objects | SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c FROM Daughter__c WHERE Mother_of_Child__r.LastName__c LIKE 'C%' |
| Relationship query: parent to child with custom objects | SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account WHERE Name like 'Acme%' |
| Relationship queries with polymorphic key | SELECT Id, Owner.Name FROM Task WHERE Owner.FirstName
like 'B%'
SELECT Id, Who.FirstName, Who.LastName FROM Task WHERE Owner.FirstName LIKE 'B%' SELECT Id, What.Name FROM Event |
| Relationship queries with aggregate | SELECT Name, (SELECT CreatedBy.Name FROM Notes) FROM
Account
SELECT Amount, Id, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity |