SOQL Object Limits and Limitations

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
  • OR when on the same column with LIKE and = operations

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

    Note

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

• 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

• 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.
• Only these aggregate functions are available for querying data model objects (DMOs).
  • AVG(fieldName)
  • SUM(fieldName)
  • COUNT()
  • COUNT(fieldName)
• Aggregate functions are available only for DMOs. They aren’t available for other Data Cloud objects, including data lake objects (DLOs).
• You can’t query calculated insight objects (CIOs).
• 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:
      1SELECT … 
      2FROM <parentDMO> 
      3WHERE ID IN 
      4(
      5    SELECT rel_…__c 
      6    FROM <childDMO> 
      7    …
      8)

    • Inner query on parent DMO:
      1SELECT … 
      2FROM <childDMO> 
      3WHERE rel_…__c IN 
      4(
      5    SELECT Id 
      6    FROM <parentDMO> 
      7    …
      8)

  • Standard SOQL semi-join limitations apply. See Considerations for Semi-Join and Anti-Join Queries.
• Semi-joins between a CRM object and a 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.
  • Only supported for DMO outer queries. For example:
    1SELECT … 
    2FROM <childDMO>
    3WHERE rel_…__c IN 
    4(
    5    SELECT Id 
    6    FROM <parent CRM Object> 
    7    …
    8)

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

• 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

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