SOQL Reference (API v62.0)

Basic Syntax

SELECT fieldList | subquery | TYPEOF
FROM objectType
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filterExpression]
[GROUP BY fieldList | ROLLUP(fieldList) | CUBE(fieldList)]
[HAVING aggregateCondition]
[ORDER BY fieldList ASC|DESC NULLS FIRST|LAST]
[LIMIT numberOfRows]
[OFFSET numberOfRows]
[FOR VIEW | FOR REFERENCE | FOR UPDATE]

Governor limits: 50,000 rows per transaction (synchronous), 200 queries per transaction. Use FOR UPDATE to lock rows in a transaction.

WHERE Operators

LIKE wildcards: % matches zero or more characters, _ matches exactly one character. Case-insensitive for standard fields, depends on collation for custom.

Logical operators: AND, OR, NOT. Parentheses control precedence.

WHERE (Status = 'Active' OR Status = 'Pending') AND CreatedDate > LAST_MONTH

Relationship Queries

Child-to-Parent (Dot Notation)

Traverse up to 5 levels of parent relationships. Uses the parent relationship name with dot notation.

// Standard relationship — use parent object name directly
SELECT Contact.Account.Name, Contact.Account.Owner.Email
FROM Contact
WHERE Contact.Account.Industry = 'Technology'

// Custom relationship — replace __c with __r
SELECT Custom_Child__c.Parent_Object__r.Name
FROM Custom_Child__c

Parent-to-Child (Subquery)

Only 1 level deep allowed. Uses the child relationship name in a subquery in the SELECT clause.

// Standard relationship — use plural child name
SELECT Name, (SELECT LastName, Email FROM Contacts)
FROM Account
WHERE Industry = 'Finance'

// Custom relationship — use __r suffix of child's lookup field
SELECT Name, (SELECT Amount__c FROM Line_Items__r)
FROM Order__c

Relationship Name Rules

Key rule: Standard child relationships use the plural object name (Contacts, Cases, Opportunities). Custom child relationships use the lookup field API name with __r replacing __c.

To find the relationship name: Schema Builder, or DescribeSObjectResult.getChildRelationships() in Apex.

Aggregate Functions

Using aliases and HAVING:

SELECT StageName, COUNT(Id) cnt, SUM(Amount) total
FROM Opportunity
GROUP BY StageName
HAVING COUNT(Id) > 5 AND SUM(Amount) > 100000

Rules:

• •Aggregate results return List<AggregateResult>. Access fields via .get('alias') or .get('expr0'), .get('expr1') if no alias.
• •GROUP BY cannot include long text area, multi-select picklist, or rich text fields.
• •GROUP BY ROLLUP(field) adds subtotals. GROUP BY CUBE(f1, f2) adds cross-tabulation subtotals.
• •Max 2,000 rows from aggregate queries. Use HAVING to filter, not WHERE (which filters before aggregation).

Date Literals

SELECT Id, Name FROM Opportunity
WHERE CloseDate = LAST_N_DAYS:90 AND StageName != 'Closed Won'

Date format in WHERE (non-literal): 2024-03-15 (Date), 2024-03-15T14:30:00Z (DateTime in UTC), 2024-03-15T14:30:00-07:00 (DateTime with offset).

TYPEOF (Polymorphic Relationships)

Polymorphic fields can reference multiple object types (e.g., Task.WhatId → Account, Opportunity, etc.; Event.WhoId → Contact, Lead). TYPEOF lets you select different fields depending on the referenced object type.

Syntax:

SELECT
  TYPEOF relationshipName
    WHEN ObjectType THEN fieldList
    WHEN ObjectType THEN fieldList
    ELSE fieldList
  END
FROM objectType

Example — querying Task.What:

SELECT Id, Subject,
  TYPEOF What
    WHEN Account THEN Name, Industry, AnnualRevenue
    WHEN Opportunity THEN Name, StageName, Amount
    ELSE Name
  END
FROM Task
WHERE CreatedDate > LAST_MONTH

Rules:

• •Only works on polymorphic relationship fields (WhatId, WhoId, OwnerId on some objects).
• •Cannot use TYPEOF in WHERE, GROUP BY, or ORDER BY.
• •Use ELSE as a fallback — it fires when the referenced type doesn't match any WHEN.
• •Alternative without TYPEOF: WHERE What.Type = 'Account' filters by type, then use What.Name.

Semi-Joins and Anti-Joins

Semi-Join (IN subquery) — Records that HAVE related records

// Accounts that have at least one Opportunity
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity)

// Contacts on Accounts in the Technology industry
SELECT Id, Name FROM Contact
WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = 'Technology')

Anti-Join (NOT IN subquery) — Records that LACK related records

// Accounts with no Contacts
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Contact)

// Leads not linked to any Campaign
SELECT Id, Name FROM Lead
WHERE Id NOT IN (SELECT LeadId FROM CampaignMember)

Rules:

• •Subquery must return a single field (typically an Id or lookup field).
• •Max 1 level of subquery nesting (no subqueries inside subqueries inside subqueries).
• •You can use up to 2 semi-join or anti-join subqueries in a single WHERE clause.
• •The inner query is subject to the same governor limits.

Performance

Selective Query Thresholds

A query is selective if the leading filter (or filter combination via AND) uses an indexed field and meets the threshold. Non-selective queries on large objects (>200K records) fail at runtime.

Default Indexed Fields

NOT indexed by default: LastModifiedDate, CreatedById, formula fields, text fields (unless External ID), picklist fields. Request custom indexes via Salesforce Support.

Query Plan Tool

Use the Query Plan tool in Developer Console (Enable: Help → Settings → Enable Query Plan) or the REST API:

/services/data/v62.0/query/?explain=SELECT Id FROM Account WHERE Name = 'Acme'

Returns: leading operation type, cost estimate, index used, selectivity info.

Performance Tips

• •Filter on indexed fields first. Combine selective filters with AND.
• •Avoid !=, NOT IN, EXCLUDES on large datasets — they prevent index usage.
• •Avoid leading wildcards in LIKE ('%acme') — not indexable. Trailing wildcards are fine ('Acme%').
• •LIMIT does NOT make a query selective — selectivity is determined before LIMIT is applied.
• •Avoid OR across different fields — each branch must be independently selective.
• •Use skinny tables (request via Support) for extremely high-volume objects.

Dynamic SOQL

Build queries at runtime using Database.query().

// Basic dynamic query with bind variable
String industry = 'Technology';
List<Account> accts = Database.query(
    'SELECT Id, Name FROM Account WHERE Industry = :industry'
);

// Dynamic field list
String fields = 'Id, Name, Industry';
String query = 'SELECT ' + fields + ' FROM Account LIMIT 100';
List<Account> results = Database.query(query);

Bind Variables

Injection Prevention

Always use String.escapeSingleQuotes() when concatenating user input:

// DANGEROUS — SQL injection vulnerable
String userInput = 'Acme\' OR Name != \'';
String q = 'SELECT Id FROM Account WHERE Name = \'' + userInput + '\'';

// SAFE — escape user input
String q = 'SELECT Id FROM Account WHERE Name = \''
    + String.escapeSingleQuotes(userInput) + '\'';

// SAFEST — use bind variables (preferred)
String safeName = userInput;
List<Account> accts = Database.query(
    'SELECT Id FROM Account WHERE Name = :safeName'
);

Rule of thumb: Use bind variables whenever possible. Fall back to escapeSingleQuotes() only when bind variables aren't feasible (e.g., dynamic field names, object names).

SOQL vs SOSL

// SOQL — precise lookup
List<Account> accts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];

// SOSL — text search across objects
List<List<SObject>> results = [FIND 'Acme*' IN NAME FIELDS
    RETURNING Account(Id, Name), Contact(Id, LastName)];
List<Account> accounts = (List<Account>) results[0];
List<Contact> contacts = (List<Contact>) results[1];

Common Errors