Advanced SOQL keywords: locking, aggregates, TYPEOF
Where FOR UPDATE, WITH USER_MODE, GROUP BY ROLLUP and CUBE, GROUPING, HAVING, TYPEOF, ALL ROWS and semi- and anti-joins go in a query, what each one does in Apex, and the keyword combinations SOQL rejects.
Demonstrate how and where to use advanced keywords in a SOQL query structure.
~7 min read · 8 practice questions ·
What the exam expects
- Lock records against concurrent updates with
FOR UPDATE, and know its wait time, callout release and incompatible clauses. - Explain why
WITH USER_MODEis preferred overWITH SECURITY_ENFORCED. - Filter groups with
HAVING, add subtotals withGROUP BY ROLLUPorCUBE, and tell subtotal rows apart withGROUPING(). - Select type-specific fields from a polymorphic relationship with
TYPEOF. - Return Recycle Bin rows with
ALL ROWS, and filter by related records with semi-joins (IN) and anti-joins (NOT IN).
Key terms: lock (a hold that stops other clients updating a record until the transaction ends); user mode (database access that respects the running user's object permissions, FLS and sharing); subtotal row (an extra ROLLUP or CUBE row whose grouped field is null); polymorphic relationship (a lookup such as Event.What that can point to several objects); anti-join (a NOT IN subquery on another object).
Key facts
- Detail
- Until the transaction completes
- Detail
- Up to 10 seconds, then QueryException
- Detail
- FOR UPDATE locks are released automatically
- Detail
- No ORDER BY; no ALL ROWS
- Detail
- System mode; WITH USER_MODE opts in to user permissions and sharing
- Detail
- SELECT and FROM only, not WHERE or ORDER BY
- Detail
- AggregateResult objects; unaliased fields are expr0, expr1
- Detail
- Up to three fields each
- Detail
- 1 for a subtotal or grand-total row across that field, else 0
- Detail
- SELECT clause only; not with GROUP BY or HAVING
- Detail
- Max two semi/anti-join IN or NOT IN per WHERE; not inside HAVING
Sources for this section (12)
- Locking Statementsdeveloper.salesforce.com (opens in a new tab)
- FOR UPDATEdeveloper.salesforce.com (opens in a new tab)
- Querying All Recordsdeveloper.salesforce.com (opens in a new tab)
- Enforce User Modedeveloper.salesforce.com (opens in a new tab)
- WITH SECURITY_ENFORCEDdeveloper.salesforce.com (opens in a new tab)
- SOQL Aggregate Functionsdeveloper.salesforce.com (opens in a new tab)
- GROUP BY ROLLUPdeveloper.salesforce.com (opens in a new tab)
- GROUP BY CUBEdeveloper.salesforce.com (opens in a new tab)
- Identify Subtotalsdeveloper.salesforce.com (opens in a new tab)
- TYPEOFdeveloper.salesforce.com (opens in a new tab)
- Comparison Operatorsdeveloper.salesforce.com (opens in a new tab)
- HAVINGdeveloper.salesforce.com (opens in a new tab)
- Why does a second integration see the up-to-date stock value when both use FOR UPDATE?
- When does WITH SECURITY_ENFORCED miss a field that WITH USER_MODE catches?
- Which keyword returns records from the Recycle Bin in Apex SOQL?
- Why can't a TYPEOF query also use GROUP BY?
The questions are free to read. Answers with their sources and self-grading are included with the pack.
Keep reading with the Platform Developer II pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 28 lessons of the Platform Developer II guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Inline practice questions and one-click topic practice (8 questions for this lesson)
- An official source cited for every claim
- An AI tutor that answers from the guide
- Unlimited timed practice exams