SOQL Limits on Objects

1SELECT fieldList [...]
2FROM objectType
3    [USING SCOPE filterScope]
4[WHERE conditionExpression]
5[ORDER BY field {ASC|DESC} [NULLS {FIRST|LAST}] ]

• 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
• You can use ORDER BY only with non-relationship fields.
• You can use ORDER BY, ASC, and DESC with multiple (non-relationship) fields.
• You can only use ORDER BY when the ordered field is a selected field.
• Metadata relationship fields support all standard relationship queries.

• A SOQL query can only filter on the fields defined in the big object’s index, in the order that they are defined, without gaps.
• The last field in a query can use only the following operators.
  • =, <. >, <=, >=, and IN
  • Any prior fields in a query can use only the = operator.
• Big objects don’t support the following operators.
  • !=, LIKE, NOT IN, EXCLUDES, and INCLUDES

• 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 external objects associated with a SharePoint 2010/2013 external data source using SecureAgent.

• In SOQLueries of external objects, IN clauses with more than approximately 15 IDs return the error “This operation is too complicated for Secure Agent.” The exact IN clause limit varies based on SharePoint ID length.

• 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 cannot use binding variables in Apex SOQL statements with KnowledgeArticleVersion objects. For example, the following SOQL statement causes a compilation error.
  1final String PUBLISH_STATUS_ONLINE = 'Online';
  2List<Knowledge__kav> articles = [ 
  3SELECT Id FROM Knowledge__kav 
  4WHERE PublishStatus = :PUBLISH_STATUS_ONLINE 
  5];

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

  1final String PUBLISH_STATUS_ONLINE = 'Online'; 
  2final String q = 'SELECT Id, PublishStatus FROM Knowledge__kav 
  3WHERE PublishStatus = :PUBLISH_STATUS_ONLINE';
  4List<Knowledge__kav> articles = Database.query(q);

• 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.

• Always use the query formats specified in the SOAP API Developer Guide.
• May include an ORDER BY clause. You must ORDER BY HasAccess if you SELECT HasAccess, and ORDER BY MaxAccessLevel if you SELECT MaxAccessLevel.
• Maximum number of records that can be queried is 200.

• 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].

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