SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
Condition Expression Syntax (WHERE Clause)
fieldExpression Syntax
USING SCOPE
ORDER BY
LIMIT
OFFSET
Update an Article’s Keyword Tracking with SOQL
Update an Article Viewstat with SOQL
WITH filteringExpression
GROUP BY
HAVING
TYPEOF
FORMAT ()
FOR VIEW
FOR REFERENCE
FOR UPDATE
Aggregate Functions
Date Functions
Querying Currency Fields in Multi-Currency Orgs
Example SELECT Clauses
Relationship Queries
Change the Batch Size in Queries
SOQL Limits on Objects
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Async SOQL
Salesforce Object Search Language (SOSL)
PDF

Newer Version Available

This content describes an older version of this product. View Latest

SOQL SELECT Syntax

SOQL query syntax consists of a required SELECT statement followed by one or more optional clauses, such as TYPEOF, WHERE, WITH, GROUP BY, and ORDER BY.

The SOQL SELECT statement uses the following syntax:

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:
  • SELECT Id, Name, BillingCity FROM Account
  • SELECT count() FROM Contact
  • SELECT Contact.Firstname, Contact.Account.Name FROM Contact
Use valid field names and include read-level permissions for each specified field. The fieldList defines the ordering of fields in the query results.
If the query traverses a relationship, fieldList can include a subquery. For example:

The fieldlist can also be an aggregate function, such as COUNT() and COUNT(fieldName), or be wrapped in Translating Results.
typeOfField A polymorphic relationship field in objectType or a polymorphic field in a parent of objectType that can reference multiple object types. For example, the Who relationship field of a Task can be a Contact or a Lead.typeOfField cannot reference a relationship field that is also referenced in fieldList. See TYPEOF for more information.
whenExpression A clause of the form WHEN whenObjectType THEN whenFieldList. You can have one or more whenExpression clauses inside a TYPEOF expression. See TYPEOF for more information.
elseExpression A clause of the form ELSE elseFieldList. This clause is optional inside a TYPEOF expression. See TYPEOF for more information.
objectType Specifies the type of object that you want to query(). Provide a valid object, such as Account, and must have read-level permissions to that object.
filterScope Available in API version 32.0 and later. Specifies the filterScope for limiting the results of the query.
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.
filteringExpression If WITH DATA CATEGORY is specified, the query() only returns matching records that are associated with the specified data categories and are visible to the user. If unspecified, the query() returns the matching records that are visible to the user. The WITH DATA CATEGORY clause only filters objects of type:
  • Question—to query questions.
  • KnowledgeArticleVersion—to query articles.

For more information about the WITH DATA CATEGORY clause, see WITH DATA CATEGORY filteringExpression.
fieldGroupByList Available in API version 18.0 and later. Specifies a list of one or more fields, separated by commas, that are used to group the query results. A GROUP BY clause is used with aggregate functions to summarize the data. This clause also lets you roll up query results rather than having to process the individual records in your code. See GROUP BY.
fieldSubtotalGroupByList Available in API version 18.0 and later. Specifies a list of up to three fields, separated by commas, that are used to group the query results. The results include extra subtotal rows for the grouped data. See GROUP BY ROLLUP and GROUP BY CUBE.
havingConditionExpression Available in API version 18.0 and later. If the query includes a GROUP BY clause, this conditional expression filters the records that the GROUP BY returns. See HAVING.
fieldOrderByList Specifies a list of one or more fields, separated by commas, that are used to order the query results. For example, you can query for contacts and order the results by last name, and then by first name:
Note the following implementation tips:
  • Statement Character Limit—By default, SOQL statements cannot exceed 100,000 characters in length. For SOQL statements that exceed this maximum length, the API returns a MALFORMED_QUERY exception code; no result rows are returned.

    Long, complex SOQL statements, such as statements that contain many formula fields, can sometimes result in a QUERY_TOO_COMPLICATED error. The error occurs because the statement is expanded internally when processed by Salesforce, even though the original SOQL statement is under the 100,000 character limit. To avoid this error, reduce the complexity of your SOQL statement.

    Page layouts in Lightning with more than 250 fields can also cause a QUERY_TOO_COMPLICATED error. Lightning uses auto-generated SOQL to retrieve fields for a record page layout, so the error can occur even if there isn’t any customer-written SOQL.

    The character limit can also be reached by including too many currency fields. Currency fields require SOQL to use a format method, roughly doubling the field API name length for each currency field.

    Note

  • Statements Returning Many Results—If a SELECT statement returns a large amount of data per record, SOQL automatically reduces the number of results. When you search a list view, only the first 2,000 records in the list view are searched. Large volumes of data can also occur, for example, if the object contains formula fields, derived fields, or CLOB or BLOB fields. To retrieve all the pages of results, use one of these methods:

    In Bulk API 2.0, use the Sforce-Locatorresponse header that is returned in the job’s results.

    In REST API, use the nextRecordsUrl returned by /queryand / queryAll.

    In SOAP API, use queryMore().

    In SOQL, if you use OFFSET and LIMIT in your SOQL query, the number of records returned can be less than LIMIT. Check the number of returned results and adjust OFFSET as required. Do not just increment OFFSET by LIMIT.

  • Localized Results—SELECT statements can include the Translating Results, convertCurrency(), and FORMAT() functions in support of localized fields.
  • Dynamic SOQL in Apex—Apex requires that you surround SOQL and SOSL statements with square brackets to use them in your statements. You can use Apex script variables and expressions when preceded by a colon (:).
  • Ordered Results—The order of results is not guaranteed unless you use an ORDER BY clause in a query.
  • Picklist Values—In API version 39.0 and later, query for picklist values by the value’s API name, which can be different from the actual value.