SOQL Fundamentals

SOQL (Salesforce Object Query Language) reads records.

14.1 What is SOQL?

SOQL (Salesforce Object Query Language) reads records. It looks like SQL SELECT but has no SELECT *, no arbitrary joins (it uses relationships instead), and queries a single root object. In Apex, static SOQL goes inside square brackets and is checked at compile time.

To avoid an exception when a query may return no rows, assign to a list and check isEmpty().

List<Account> accounts = [
    SELECT Id, Name, Industry, AnnualRevenue
    FROM Account
    WHERE Industry = 'Technology' AND AnnualRevenue > 1000000
    ORDER BY AnnualRevenue DESC NULLS LAST
    LIMIT 50
];

Account single = [SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1];   // QueryException if 0 rows
Integer total = [SELECT COUNT() FROM Contact WHERE Email != null];

14.2 Bind variables

Use :variable to inject Apex values safely (no SOQL injection, automatic escaping):

String industry = 'Banking';
Set<Id> ownerIds = new Set<Id>{ UserInfo.getUserId() };
Date since = Date.today().addDays(-30);
List<Account> recent = [
    SELECT Id, Name FROM Account
    WHERE Industry = :industry
      AND OwnerId IN :ownerIds
      AND CreatedDate >= :since
];
Integer lim = 5;
List<Contact> few = [SELECT Id FROM Contact LIMIT :lim];

14.3 WHERE clause operators

Amount >= 1000 LIKE (%, _ wildcards, case-insensitive) Name LIKE 'Acme%' IN / NOT IN

StageName IN ('Prospecting', 'Closed Won') INCLUDES / EXCLUDES

Multi-select picklists: Interests__c INCLUDES ('Golf;Tennis') NOT Name LIKE 'Test%' = null / != null Email != null

CreatedDate = LAST_N_DAYS:30, CloseDate = THIS_QUARTER, TODAY, YESTERDAY, NEXT_MONTH Semi-join / anti-join

Id IN (SELECT AccountId FROM Contact), Id NOT IN (SELECT AccountId FROM Opportunity)

14.4 Relationship queries

Child-to-parent (dot notation, up to 5 levels):

Parent-to-child (subquery with the child relationship name, usually plural):

List<Contact> contacts = [
    SELECT Id, LastName, Account.Name, Account.Owner.Name, Account.Parent.Name
    FROM Contact WHERE Account.Industry = 'Energy'
];
// custom lookup: Invoice__c.Customer__c → traverse with __r
List<Invoice__c> invoices = [SELECT Id, Customer__r.Name FROM Invoice__c];

List<Account> withKids = [
    SELECT Id, Name,
        (SELECT Id, LastName FROM Contacts ORDER BY LastName),
        (SELECT Id, Amount FROM Opportunities WHERE IsClosed = false),
        (SELECT Id FROM Invoices__r)                 // custom child relationship
    FROM Account
    WHERE Id IN (SELECT AccountId FROM Contact)
    LIMIT 100
];
for (Account a : withKids) {
    for (Contact c : a.Contacts) {
        System.debug(a.Name + ' → ' + c.LastName);
    }
}

14.5 Aggregate queries

Aggregate functions: COUNT(), COUNT(field), COUNT_DISTINCT(), SUM(), AVG(), MIN(), MAX(). Grouping extensions: GROUP BY ROLLUP(...), GROUP BY CUBE(...), GROUPING(). Date functions: CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(), DAY_ONLY(), FISCAL_QUARTER(). Unaliased aggregates are named expr0, expr1, ….

List<AggregateResult> results = [
    SELECT StageName, COUNT(Id) dealCount, SUM(Amount) total, AVG(Amount) average, MAX(CloseDate) latest
    FROM Opportunity
    WHERE CloseDate = THIS_YEAR
    GROUP BY StageName
    HAVING SUM(Amount) > 100000
    ORDER BY SUM(Amount) DESC
];
for (AggregateResult ar : results) {
    String stage = (String) ar.get('StageName');
    Integer cnt  = (Integer) ar.get('dealCount');
    Decimal sum  = (Decimal) ar.get('total');
    System.debug(stage + ': ' + cnt + ' deals, ' + sum);
}

14.6 Other clauses

ORDER BY field ASC|DESC NULLS FIRST|LAST Skip rows (max 2,000) — prefer keyset pagination for large sets

Lock rows for the transaction (avoid race conditions) Include deleted and archived records (Apex only) FOR VIEW / FOR REFERENCE

Update recently viewed / referenced data Polymorphic fields: TYPEOF What WHEN Account THEN Name, Phone WHEN Opportunity THEN Amount END WITH SECURITY_ENFORCED

Throw if the user lacks FLS/CRUD for referenced fields (older approach) WITH USER_MODE / WITH SYSTEM_MODE

Run the query in user mode (sharing + CRUD + FLS) or system mode (§15, §29)

FORMAT(), toLabel(), convertCurrency() Localized formatting, picklist labels, multi-currency conversion

Select all standard fields (FIELDS(ALL)/FIELDS(CUSTOM) require LIMIT 200 and are mainly for APIs)