# 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: ```soql SELECT fieldList [subquery][...] [TYPEOF typeOfField whenExpression[...] elseExpression END][...] FROM objectType[,...] [USING SCOPE filterScope] [WHERE conditionExpression] [WITH [DATA CATEGORY] filteringExpression] [GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)} [HAVING havingConditionExpression] ] [ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ] [LIMIT numberOfRowsToReturn] [OFFSET numberOfRowsToSkip] [{FOR VIEW | FOR REFERENCE} ] [UPDATE {TRACKING|VIEWSTAT} ] [SET OPTIONS (optionName = optionValue [, optionName = optionValue])] [FOR UPDATE] ``` | 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 fieldlist values: `SELECT Id, Name, BillingCity FROM Account`, `SELECT count() FROM Contact`, `SELECT Contact.Firstname, Contact.Account.Name FROM Contact`, or `SELECT FIELDS(STANDARD) 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. The fieldlist can also be an aggregate function, such as `COUNT()` and `COUNT(fieldName)`, or be wrapped in the [toLabel()](/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-tolabel.md) function to translate returned results. See [`SELECT`](/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-fields.md) for more information. | | `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` can’t 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) or `KnowledgeArticleVersion` (to query articles). See [`WITH DATA CATEGORY`. ](/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-with-datacategory.md#with-data-category) If `WITH DATA CATEGORY` isn’t specified, filteringExpression can be used to enforce field- and object-level permission or to specify user- or system-mode access in Apex code. Salesforce recommends that you enforce field- and object-level security permissions by using `WITH USER_MODE` because it has fewer limitations than `WITH SECURITY_ENFORCED`. | | `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. | Example fieldList with a subquery: ```codesegment SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account ``` **SECURITY_ENFORCED:** Use the `WITH SECURITY_ENFORCED` clause to filter records based on user access permissions on fields or objects being queried within Apex code. See [Filter SOQL Queries Using WITH SECURITY_ENFORCED](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_with_security_enforced.htm) in the *Apex Developer Guide*. **USER_MODE** or **SYSTEM_MODE:** Specify user mode or system mode access for Apex database operations. Apex code runs in system mode by default, which means that it runs with substantially elevated permissions over the user running the code. To enhance the security context of Apex, you can specify user mode access using `WITH USER_MODE`. Field-level security (FLS) and object permissions of the running user are respected and sharing rules are applied in user mode, unlike in system mode. See [Enforce User Mode for Database Operations](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_enforce_usermode.htm) in the *Apex Developer Guide*. Example fieldOrderByList: ```codesegment SELECT Id, LastName, FirstName FROM Contact ORDER BY LastName, FirstName ``` ## Implementation Tips for SELECT Statement - **Statement Character Limit**—By default, SOQL statements can't 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. :::note Long, complex SOQL statements, such as statements that contain many formula fields, can 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. The SOQL statement character limit does not apply when using SOQL with dynamic Apex. ::: - **Statements Returning Many Results**—If a SELECT statement returns a large amount of data per record, SOQL automatically reduces the number of results. The number of results can also vary based on how a SOQL query is called and on the complexity of the query. When you search a list view, only the first 2,000 records in the list are searched. If the object contains formula fields, derived fields, or CLOB or BLOB fields, large volumes of data can also be returned. To retrieve all the pages of results, use one of these methods: - 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. Don’t increment OFFSET by LIMIT. - In SOAP API, use [`queryMore()`.](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_querymore.htm#topic-title) - In REST API, use the nextRecordsUrl returned by `/query` and `/queryAll`. - In Bulk API 2.0, use the Sforce-Locator response header that is returned in the job’s results. - **Localized Results**—SELECT statements can include the [toLabel()](/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-tolabel.md), [`convertCurrency()`](/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-querying-currency-fields.md), and [`FORMAT()`](/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-format.md#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 isn’t 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. - **Avoid Query Timeouts**—Consider using [`WHERE` ](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_conditionexpression.htm) or [`WITH` ](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_with.htm) clauses to reduce the query operation time. In REST API, you can also try the `explain` parameter (beta) to get information on query processing. For more information, see [Get Feedback on Query Performance](https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_query_explain.htm) in the *REST API Developer Guide.* **See Also** - [*Salesforce Developer Limits and Allocations Quick Reference*: SOQL and SOSL Limits for Search Queries](https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm)