SOQL Object Limits and Limitations

SOQL applies specific limits to objects and situations in search results. SOQL limits are defined for the ContentDocumentLink object, ContentHubItem object, big objects, external objects, NewsFeed, KnowledgeArticleVersion, RecentlyViewed, TopicAssignment, UserRecordAccess, UserProfileFeed, and Vote.

Some objects or situations have specific limits on SOQL.

Custom metadata types:

• You can use metadata relationship fields in the fieldList and conditionExpression.

• FROM can include only 1 object.

• You can use the following operators.

  • IN and NOT IN
  • =, >, >=, <, <=, and !=
  • LIKE, including wild cards
  • AND
  • OR when on the same column with LIKE and = operations

• You can use ORDER BY only with non-relationship fields.

• You can use ORDER BY, ASC, and DESC with multiple (non-relationship) fields.

• Metadata relationship fields support all standard relationship queries.

Note: OR can’t be used as a compound filter when child filters are on two different columns.

Data 360 Objects:

• To prevent large queries from crashing the app server, Data 360 limits Salesforce Object Query Language (SOQL) results to 12-MB. SOQL returns the results up to the size limit with a query more link to paginate through the rest of the results. If a single query result exceeds the size limit, SOQL rejects the query and you can’t retrieve the results.

• Queries that contain aggregate functions don’t support currency fields even when the fields are outside the aggregate functions.

• For currency field query limitations, see Considerations for Querying Currency Data Using SOQL.

• These aggregate functions are available for querying data model objects (DMOs) and data lake objects (DLOs).

  • AVG(fieldName)
  • SUM(fieldName)
  • COUNT()
  • COUNT(fieldName)
  • MIN(fieldName)
  • MAX(fieldName)

• You can’t query calculated insight objects (CIOs).

• Use the SET OPTIONS clause to specify a dataspace when querying a data lake object (DLO) or to distinguish between NULL and empty string ('') when querying a data lake object (DLO) or data model object (DMO).

• Data Cloud objects have these limitations related to the Id field.

  • You can’t use the Id field in the HAVING clause in HAVING Id or HAVING COUNT(Id).
  • You can’t use the Id field in the GROUP BY clause in GROUP BY Id or GROUP BY COUNT(Id).

• Data Cloud objects have these limitations related to the HAVING clause.

  • You can’t use the HAVING clause with the IN operator. For example, this query isn’t supported: SELECT AVG(Number) FROM Dmo HAVING AVG(Number) IN (0, 10, 20)
  • You can’t use the HAVING clause when comparing to a null value. For example, this query isn’t supported: SELECT AVG(Number) FROM Dmo HAVING AVG(Number) != NULL

• You can only use the default strategy for string collation as defined by Unicode Root Collation in the Unicode Technical Standard. String collations include locale-based collations and collation strategies defined in the SOQL query. Custom collations aren’t supported. Comparison operators (>, <, >=, and <=) that are used with string collations aren’t supported for Data Cloud queries. For example, this query isn’t supported: SELECT Text_Field FROM Dmo WHERE Text_Field > 'SomeValue'

• Child-to-parent object relationships aren’t supported.

• Semi-join queries between two DMOs have these limitations.

  • You must always use the lookup fields when constructing the semi-join, irrespective of the actual relationship fields. The structure of the query depends on if the parent is in the outer or inner query.

    • Outer query on parent DMO:

    • Inner query on parent DMO:

  • Standard SOQL semi-join limitations apply. See Considerations for Semi-Join and Anti-Join Queries.

• Semi-joins between an outer child DMO and an inner parent CRM object have these restrictions.

  • Both direct and identity resolution-based relationships are supported.

  • The child DMO must look up the parent sObject’s ID field.

  • Example query:

  • The inner CRM object query is limited to 2,000 records. If more than 2,000 records are returned, the query returns an error.

  • The DMO outer query can’t trigger queryMore(). If it does, the query returns an error.

  • Standard SOQL semi-join limitations apply. See Considerations for Semi-Join and Anti-Join Queries.

• Semi-joins between an outer parent CRM object and inner child DMO have these restrictions:

  • Only direct DMO relationships between CRM and DMO objects are supported. Identity resolution-based relationships aren’t supported.

  • The child DMO must look up the parent sObject’s ID field.

  • Example query:

  • The inner CRM object query is limited to 2,000 records. If more than 2,000 records are returned, the query returns an error.

  • The DMO outer query can’t trigger queryMore(). If it does, the query returns an error.

  • Standard SOQL semi-join limitations apply.

External objects:

• A subquery that involves external objects can fetch up to 1,000 rows of data.

• Each SOQL query can have up to 4 joins across external objects and other types of objects. Each join requires a separate round trip to the external system when executing the query. Expect longer response times for each join in a query.

• External objects don’t support the following aggregate functions and clauses.

  • AVG() function
  • COUNT(fieldName) function (however, COUNT() is supported)
  • HAVING clause
  • GROUP BY clause
  • MAX() function
  • MIN() function
  • SUM() function

• External objects also don’t support the following.

  • EXCLUDES operator
  • FOR VIEW clause
  • FOR REFERENCE clause
  • INCLUDES operator
  • LIKE operator
  • toLabel() function
  • TYPEOF clause
  • WITH clause

The following limits apply only to the OData 2.0 and 4.0 adapters for Salesforce Connect.

• External objects have the following limitations for the ORDER BY clause.

  • NULLS FIRST and NULLS LAST are ignored.
  • External objects don’t support the ORDER BY clause in relationship queries.

• The COUNT() aggregate function is supported only on external objects whose external data sources have Request Row Counts enabled. Specifically, the response from the external system must include the total row count of the result set.

The following limits apply only to custom adapters for Salesforce Connect.

• Location-based SOQL queries of external objects aren’t supported.

• If a SOQL query of an external object includes the following, the query fails.

  • convertCurrency() function
  • UPDATE TRACKING clause
  • UPDATE VIEWSTAT clause
  • USING SCOPE clause

• In an ORDER BY clause, the following are ignored.

  • NULLS FIRST syntax
  • NULLS LAST syntax

• In Apex tests, use dynamic SOQL to query external objects. Tests that perform static SOQL queries of external objects fail.

KnowledgeArticleVersion:

• Filter on a single value of PublishStatus for best results. To find all versions of each article, omit the PublishStatus filter, but do filter on one or more primary key IDs. To retrieve all archived versions for a given article, specify a SOQL filter where IsLatestVersion is false.
• In API version 46.0 and earlier, queries without a filter on PublishStatus return published articles by default. In API version 47.0 and later, draft, published, and archived articles are returned when Lightning Knowledge is enabled.
• To support security, only users with the “View Draft Articles” permission see articles whose PublishStatus value is Draft. Similarly, only users with the “View Archived Articles” permission see articles whose PublishStatus value is Archived
• Archived article versions are stored in the Knowledge__kav object. To query archived article versions, specify the article Id and set IsLatestVersion='0'.
• You can’t use binding variables in Apex SOQL statements with KnowledgeArticleVersion objects. For example, the following SOQL statement causes a compilation error.

Instead, use dynamic SOQL as follows. See Dynamic SOQL in Apex Developer Guide.

NewsFeed:

• No SOQL limit if logged-in user has “View All Data” permission. If not, specify a LIMIT clause of 1,000 records or fewer.
• SOQL ORDER BY on fields using relationships is not available. Use ORDER BY on fields on the root object in the SOQL query.

UserProfileFeed:

• No SOQL limit if logged-in user has “View All Data” permission. If not, specify a LIMIT clause of 1,000 records or fewer.
• SOQL ORDER BY on fields using relationships is not available. Use ORDER BY on fields on the root object in the SOQL query.

Also, a SOQL query must include WITH UserId = {userId].

Vote:

• ParentId = [single ID]
• Parent.Type = [single type]
• Id = [single ID]
• Id IN = [list of IDs]

For other SOQL limits, see SOQL and SOSL Limits in the Salesforce Developer Limits and Allocations Quick Reference.