SOQL Reference

Query Mode and Security

WITH USER_MODE

Use when you want query execution to respect user permissions and sharing.

SELECT Id, Name
FROM Contact
WITH USER_MODE
WHERE AccountId = :accountId

WITH SECURITY_ENFORCED

Use to enforce object/field access checks at query time for selected fields.

List<Contact> contacts = [
    SELECT Id, Name, Email
    FROM Contact
    WHERE AccountId = :accountId
    WITH SECURITY_ENFORCED
];

Common Query Patterns

IN Clause

Set<Id> accountIds = new Set<Id>{'001000000000001', '001000000000002'};
List<Contact> contacts = [
    SELECT Id, Name
    FROM Contact
    WHERE AccountId IN :accountIds
];

LIKE Pattern Matching

SELECT Id, Name
FROM Contact
WHERE Name LIKE 'John%'

Null Checks

SELECT Id, Name, Email
FROM Contact
WHERE Email != null

Date Filters

SELECT Id, Name, CreatedDate
FROM Contact
WHERE CreatedDate >= LAST_N_DAYS:30

Selectivity and Performance

Pagination Patterns

OFFSET Pagination (small datasets)

SELECT Id, Name
FROM Contact
ORDER BY CreatedDate
LIMIT 200
OFFSET 0

OFFSET is simple but has scaling limits. Avoid for large datasets.

Keyset/Cursor Pagination (large datasets)

List<Contact> page1 = [
    SELECT Id, Name, CreatedDate
    FROM Contact
    ORDER BY CreatedDate, Id
    LIMIT 200
];

Datetime lastCreated = page1[page1.size()-1].CreatedDate;
Id lastId = page1[page1.size()-1].Id;

List<Contact> page2 = [
    SELECT Id, Name, CreatedDate
    FROM Contact
    WHERE (CreatedDate > :lastCreated)
       OR (CreatedDate = :lastCreated AND Id > :lastId)
    ORDER BY CreatedDate, Id
    LIMIT 200
];

Aggregation Examples

Grouping and Counts

SELECT Status, COUNT(Id)
FROM Case
GROUP BY Status

Aggregate Filters

SELECT AccountId, COUNT(Id)
FROM Contact
GROUP BY AccountId
HAVING COUNT(Id) > 10

Anti-Patterns