# Advanced SOQL keywords: locking, aggregates, TYPEOF

*Platform Developer II Study Guide · Process Automation, Logic, and Integration · Last updated October 16, 2026 · https://www.certifyforce.com/study-guide/platform-developer-ii/process-automation-logic-and-integration/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.

- Certification: Salesforce Platform Developer II
- Exam section: Process Automation, Logic, and Integration
- Study guide: https://www.certifyforce.com/study-guide/platform-developer-ii

**Official exam objective:**

> Demonstrate how and where to use advanced keywords in a SOQL query structure.

## 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_MODE` is preferred over `WITH SECURITY_ENFORCED`.
- Filter groups with `HAVING`, add subtotals with `GROUP BY ROLLUP` or `CUBE`, and tell subtotal rows apart with `GROUPING()`.
- 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

| Fact | Detail | Source |
|---|---|---|
| FOR UPDATE lock duration | Until the transaction completes | [Locking Statements](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_locking_statements.htm) |
| Lock wait | Up to 10 seconds, then QueryException | [Locking Statements](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_locking_statements.htm) |
| Callout while locked | FOR UPDATE locks are released automatically | [Locking Statements](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_locking_statements.htm) |
| Locking incompatibilities | No ORDER BY; no ALL ROWS | [FOR UPDATE](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-for-update.html), [Querying All Records](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_query_all_rows.htm) |
| Apex default mode | System mode; WITH USER_MODE opts in to user permissions and sharing | [Enforce User Mode](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_enforce_usermode.htm) |
| SECURITY_ENFORCED scope | SELECT and FROM only, not WHERE or ORDER BY | [WITH SECURITY_ENFORCED](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_with_security_enforced.htm) |
| Aggregate results | AggregateResult objects; unaliased fields are expr0, expr1 | [SOQL Aggregate Functions](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm) |
| ROLLUP and CUBE | Up to three fields each | [GROUP BY ROLLUP](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-groupby-rollup.html), [GROUP BY CUBE](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-groupby-cube.html) |
| GROUPING(field) | 1 for a subtotal or grand-total row across that field, else 0 | [Identify Subtotals](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-groupby-grouping.html) |
| TYPEOF restrictions | SELECT clause only; not with GROUP BY or HAVING | [TYPEOF](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-typeof.html) |
| Semi- and anti-join limits | Max two semi/anti-join IN or NOT IN per WHERE; not inside HAVING | [Comparison Operators](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-comparisonoperators.html), [HAVING](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-having.html) |

## Check yourself

- 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?

## Sources

- [Locking Statements](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_locking_statements.htm)
- [FOR UPDATE](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-for-update.html)
- [Enforce User Mode for Database Operations](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_enforce_usermode.htm)
- [Filter SOQL Queries Using WITH SECURITY_ENFORCED](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_with_security_enforced.htm)
- [WITH](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-with.html)
- [HAVING](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-having.html)
- [Working with SOQL Aggregate Functions](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm)
- [GROUP BY ROLLUP](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-groupby-rollup.html)
- [GROUP BY CUBE](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-groupby-cube.html)
- [Identify Subtotals with GROUP BY](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-groupby-grouping.html)
- [TYPEOF](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-typeof.html)
- [Querying All Records with a SOQL Statement](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_query_all_rows.htm)
- [FOR VIEW and FOR REFERENCE](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-for-view-for-reference.html)
- [Comparison Operators](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-comparisonoperators.html)
- [Working with Polymorphic Relationships in SOQL Queries](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_polymorphic_relationships.htm)
- [SOQL SELECT Syntax](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select.html)

## About the full unit

The "Core concepts", "Exam traps" and "Worked scenario" sections of this unit, its 8 linked practice questions, and unlimited timed mock exams are included with the Platform Developer II pack (USD $19): https://www.certifyforce.com/practice-exams